# conditionally copy and paste of the data by percentage

Sachin Singh used Ask the Experts™
on
conditionally copy and paste of the data by percentage
plz see the sample file
sample5.xlsb
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Please give this a try and see if this is what you are trying to achieve.

``````Sub CopyDataFromSheet2And3ToSheet4()
Dim ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim x2, x3, x4, arrG(), arrL(), dict
Dim i As Long, lr As Long

Application.ScreenUpdating = False

Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Set ws4 = Sheets("Sheet4")
lr = ws4.Cells(Rows.Count, 1).End(xlUp).Row
x2 = ws2.Range("A1").CurrentRegion.Value
x3 = ws3.Range("A1").CurrentRegion.Value
x4 = ws4.Range("A1:T" & lr).Value
ReDim arrG(1 To UBound(x4, 1), 1 To 1)
ReDim arrL(1 To UBound(x4, 1), 1 To 1)

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x2, 1)
dict.Item(x2(i, 2)) = x2(i, 11) & "_" & Round(x2(i, 4) * 1.0075, 2) & "_" & x2(i, 4) - 0.05
Next i

For i = 2 To UBound(x3, 1)
dict.Item(x3(i, 2)) = x3(i, 11) & "_" & Round(x3(i, 4) * 1.0075, 2) & "_" & x3(i, 4) - 0.05
Next i

For i = 1 To UBound(x4, 1)
If dict.exists(x4(i, 3)) Then
If x4(i, 10) = Split(dict.Item(x4(i, 3)), "_")(0) Then
arrL(i, 1) = Split(dict.Item(x4(i, 3)), "_")(1)
Else
arrG(i, 1) = Split(dict.Item(x4(i, 3)), "_")(2)
End If
End If
Next i
ws4.Range("G1").Resize(UBound(x4, 1), 1).Value = arrG
ws4.Range("L1").Resize(UBound(x4, 1), 1).Value = arrL
Application.ScreenUpdating = True
End Sub
``````

Click the button called "Copy Data From Sheet2 & Sheet3" on Sheet4 to run the code.
sample5.xlsb

Commented:
with sheet3 data we have to minus 0.75% small modification is required
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Ah... I didn't notice that.
Please test the attached and let me know if it is working as desired now.
sample5.xlsb

Commented:
only one condition met  Neeraj Sir
plz see this file i have highlighted the accurate output  and i have highlighted the incorrect output
sample5--10-.xlsb
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You didn't explain why the numbers highlighted in red are incorrect?
e.g. on Sheet4, G10 is 871.50
whereas C10 is "AMARAJABAT" and J10 is BUY.

If you look at Sheet3, B2 matches with C10 but K2 doesn't match with J10 and here is your condition on Sheet3...
minus 0.75% of column D of sheet3 to itself and paste that data in column L of sheet4
we have to do this if column K of sheet3 matches sheet4 column J  and column B of sheet3 matches column C of sheet4
and if it not matches then
add 0.5 (this is not the percentage  it is 0.5 paise)  of column D of sheet3  and paste that data to sheet4 columnG
but here column B of sheet3  should match column C of sheet4
So as per the above condition, D2 on Sheet3 is 871 and if you add 0.5 to it, it will become 871.50 and that is what is written in G10 on Sheet4.
What's wrong then?

Commented:
Sorry Neeraj Sir Mine Mistake it is not 0.5 it is 0.05 in sheet2 also and in sheet3 also
and
L25 of sheet4 over there there is an error
0.75% of 871 is 6.53
so condition is met
then we will subtract 6.53 from 871
so the result will be 864.47
so plz look into it
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay, check the attached.
sample5--10-.xlsb

Commented:
Thnx Neeraj Sir for ur great great support
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Sachin! Glad it worked as desired in the end.

Commented:
Plz check it once again why it is not working in this file
TwoClick.xlsb
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
That's not the way Sachin. When we provide a working solution for a specific issue, make sure that you maintain the data layout in your file similar to the sample file you provided.
Just download one of file I uploaded above and see if the data layout is same in both the files for all the three sheets. If it is same, the code will work without an issue but if it's different the code will not work.
Whenever you have any such issue always open a new question, provide the existing code and explain what the code is supposed to do in your new question instead of continuing in the same question.

Commented:
No problem Neeraj Sir i will open a new question with 101% exact details

Do more with