conditionally copy and paste of the data by percentage

conditionally copy and paste of the data by percentage
plz see the sample file
sample5.xlsb
Sachin SinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
Sachin SinghAuthor Commented:
with sheet3 data we have to minus 0.75% small modification is required
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Ah... I didn't notice that.
Please test the attached and let me know if it is working as desired now.
sample5.xlsb
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Sachin SinghAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
0
Sachin SinghAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, check the attached.
sample5--10-.xlsb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sachin SinghAuthor Commented:
Thnx Neeraj Sir for ur great great support
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sachin! Glad it worked as desired in the end.
0
Sachin SinghAuthor Commented:
Plz check it once again why it is not working in this file
TwoClick.xlsb
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Sachin SinghAuthor Commented:
No problem Neeraj Sir i will open a new question with 101% exact details
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.