conditionally copy and paste of the data by percentage

Sachin Singh
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)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

Open in new window


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

Author

Commented:
with sheet3 data we have to minus 0.75% small modification is required
Subodh Tiwari (Neeraj)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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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
Subodh Tiwari (Neeraj)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?

Author

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

Author

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

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

Author

Commented:
Plz check it once again why it is not working in this file
TwoClick.xlsb
Subodh Tiwari (Neeraj)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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial