Conditionally copy and paste of the data with add/subtract method

If column C of sheet4 matches with column  B of sheet2 and  column J of sheet4  is not matching with column M of sheet2
then in sheet4
in L  column of sheet4 put the value of column D of sheet2
in G column of sheet4  -0.05 in  the value of L and  put the remaining value  in column G



If column C of sheet4 matches with column  B of sheet3 and  column J of sheet4  is not matching with column M of sheet3
then in sheet4
in L  column of sheet4 put the value of column D of sheet3
in G column of sheet4  + 0.05 in   the value of L and  put the total value  in column G

Plz see the sample file
TwoClick.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:
Are these the same conditions you mentioned in your previous question except the column K is changed to column M on Sheet2 and Sheet3?
0
Sachin SinghAuthor Commented:
I have also made some changes in this but this  details are 101% perfect Neeraj Sir   and the file that i sent is the actual file
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why have you placed NA in some of the cells in column L on Sheet4?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Sachin SinghAuthor Commented:
if we are placing order through limit order then trigger price is not applicable  so that's why it is putted NA in L column
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
So what to do with those NAs in column L? Do you need to retain them or overwrite with the calculated values from Sheet2 and 3?
0
Sachin SinghAuthor Commented:
Neeraj Sir don't do anything with that  and paste the  calculated data
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay.
You also need to redefine your requirement. You said this...

If column C of sheet4 matches with column  B of sheet2 and  column J of sheet4  is not matching with column M of sheet2
then in sheet4
in L  column of sheet4 put the value of column D of sheet2
in G column of sheet4  -0.05 in  the value of L and  put the remaining value  in column G

What if column J of Sheet4 matches with Column M of Sheet2?

Or it is something like this...

1) If column C of sheet4 matches with column  B of sheet2 and  column J of sheet4  is not matching with column M of sheet2, put the value of column D from Sheet2 in column L on Sheet4.

2)  If column C of sheet4 matches with column  B of sheet2 and  also column J of sheet4  matches with column M of sheet2, put the value of column D from Sheet2  minus 0.05 in column L on Sheet4.

And same for Sheet3?
0
Sachin SinghAuthor Commented:
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's separate thread.
What to do with this?
Should it ignore when also column J of sheet4  matches with column M of sheet2 and only place the calculated values in column G and L when they don't match?
0
Sachin SinghAuthor Commented:
ignore it sir
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay.
0
Sachin SinghAuthor Commented:
Thnx for the Support
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Wait a minute Sachin!
You said...
If column C of sheet4 matches with column  B of sheet2
But the column C on Sheet4 is blank and the symbols are in column B. Is that correct? Or you put symbols in wrong column on Sheet4?
0
Sachin SinghAuthor Commented:
Sorry Neeraj Sir  plz consider this file
TwoClick.xlsb
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, try this...

Sub STEP8()
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, 13) & "_" & x2(i, 4) & "_" & x2(i, 4) - 0.05
Next i

For i = 2 To UBound(x3, 1)
    dict.Item(x3(i, 2)) = x3(i, 13) & "_" & x3(i, 4) & "_" & 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)
            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 "Populate Column G & L" on Sheet4 to run the code.
TwoClick-v2.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 support
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sachin!
0
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.