Match opposites

[rdev]

Please let me know how we can match as per below

Meet Google Drive – One place for all your files

I am looking for a macro that will see E2 in in column N , test column D to read the opposite of E2
I have the following but not coming good

code

Sub relatedpartyelimination()

lastrow = Sheets("Comm").Cells(Rows.Count, 1).End(xlUp).Row
v = 3
MsgBox lastrow

For *** = 2 To lastrow
x = Cells(***, 1).Value
u = Cells(***, 4).Value
fr = Cells(***, 5).Value
Z = Cells(***, 6).Value

If u = "Purchase of Goods" Then
u1 = "Sales of Goods"
If u = "Purchase of Services" Then
u1 = "Sales of Services"
End If
If u = "Amount Receivables" Then
u1 = "Amount Payables"
End If
If u = "Balances Payables with FSCM" Then
u1 = "Balances Payables with FSCM"
End If
End If

ot = 3
Do While ot < lastrow

If Cells(ot, 5).Value = x And Cells(ot, 4) = u1 Then
Cells(***, 7).Value = Cells(ot, 6).Value
End If
ot = ot + 1
Loop


Next ***



End Sub

 

[Scott Huish]

That link doesn't actually link to a shared file. Do you want to try again or post a sample and what you mean by "read the opposite" exactly?

 

[rdev]

That link doesn't actually link to a shared file. Do you want to try again or post a sample and what you mean by "read the opposite" exactly?

Hello
Please see the link
dummy data.xlsx - Google Drive

 

[Scott Huish]

With your new dataset:

Excel 2010
	A	B	C	D	E	F	G
1	Company	Transaction	Transaction Party	Amount	MatchCompany	Counter Amount
2	Alpa	Purchase of goods	Silicon	340,000	Silicon	Purchase of goods	F2 should equal to cell D5 after the following is satified[silicon is found in column A, we have 'Sales of goods' , i.e the opposite of 'Purchase of goods', cells E5= Alpa.
3	Alpa	Sales of goods	Naturo	275,000	Naturo	Sales of goods
4	Alpa	Sales of services	Cinbata	78,000	Cinbata	-
5	Silicon	Sales of goods	Alpa	340,000	Alpha	340,000
6	Tetra	Purchase of services	Alpa	122,000	Alpha	-
7	Naturo	Purchase of goods	Alpa	275,000	Alpha	275,000
Sheet1

Sub test()
Dim c As Range, d As Range
For Each c In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
    Set d = Range("A:A").Find(c)
    If Not d Is Nothing Then
        If Left(d.Offset(, 1), 8) = "Purchase" Then c.Offset(, 1) = "Sales" & Mid(d.Offset(, 1), 9)
        If Left(d.Offset(, 1), 5) = "Sales" Then c.Offset(, 1) = "Purchase" & Mid(d.Offset(, 1), 6)
    End If
Next
End Sub

 

[rdev]

Just an example of the result I am looking for :
I have to look for the value in cell E2 value in column A,
If found then subject to conditions E9=A2 and D9 is the opposite of D2 , then put the values in cell A9 and F9 in cells G2 and H2 respectively and cells G9 and H9 should have values A2 and F2
The macro should the take row A3 and do same procedure as above, if not found then 'no result to be inserted'

 

[Scott Huish]

So in the above example the result should not go into Column H, but Column G (the opposite matching value) and in Column H the value from Column F. Is this correct?

Sub test()
Dim c As Range, d As Range
For Each c In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
    Set d = Range("A:A").Find(c)
    If Not d Is Nothing Then
        If Left(d.Offset(, 1), 8) = "Purchase" Then c.Offset(, 2) = "Sales" & Mid(d.Offset(, 1), 9)
        If Left(d.Offset(, 1), 5) = "Sales" Then c.Offset(, 2) = "Purchase" & Mid(d.Offset(, 1), 6)
        c.Offset(, 3) = c.Offset(, 1)
    Else
        c.Offset(, 2) = "No Result"
    End If
Next
End Sub

 

[rdev]

No the data in Column E and F are results after running the routine. The macro will look for Silicon in column A and if found , a second condition from Column B is to be meant, the opposite value of cell B2 and a third condition in column C in row 5 equal to cell A2

 

[rdev]

This is what the final result should look to after running the macro, I have tried to amend the macro without success

Dataresults.xlsm - Google Drive

 

[Scott Huish]

I have tried but I just don't seem to quite understand what you are trying to accomplish.

 

[rdev]

Please see if this makes sense what I want to achieve. The data remains what i posted in before


Sub usingFind()
Dim ms As Worksheet
Dim lastrow As Long, i As Long
Dim myCell As Range
Set ms = Sheets("mathew")
lastrow = Range("A" & Rows.Count).End(xlUp).Row
x = 2
y = 3
With ms.Range("A1:A" & lastrow)
c = ms.Cells(x, y).Value
u = ms.Cells(x, y).Offset(0, -1).Value
If u = "Purchase of goods" Then u1 = "Sales of goods"
If u = "Sales of goods" Then u1 = "Purchase of goods"
Set myCell = .Find(c)
If Not myCell Is Nothing And myCell.Offset(0, 1).Value = u1 And _
myCell.Offset(0, 2).Value = c Then
Cells(x, 5).Value = c
Cells(x, 5).Value = myCell.Offset(0, 3).Value
myCell.Offset(0, 4).Value = Cells(x, 1).Value
myCell.Offset(0, 4).Value = Cells(x, 1).Value
End If
End With

Exit Sub
End Sub