PDA

View Full Version : [SOLVED] Lookup Value in two columns and identify opposite values



sharc316
04-03-2017, 06:25 PM
Hi,

Wondering if someone can help me accomplish the following: I would like to take a value from A1 (0089) and B1 (7191) and see if there is the reverse value anywhere in these columns ex: A2 (7191) and B2 (0089).

Then in column C would like to name these two rows with the same value; "1" in this example. Would like this check to be performed for every row and name these values with same label when true.

Thank you for your help.



A

B

C



0089
7191
1


7191
0089
1


0089
7642
2


7642
0089
2


0089
0132
3


0132
0089
3


0089
0923
4

Aussiebear
04-04-2017, 03:06 AM
Sorry but I don't understand the logic here. Given that A1 matches B2 and A2 matches B1, one of them would be 1 and the other would be two. Hides two matches as 1 seems irrelevant to me. if this was my worksheet I'd use something like this

In Cell C1 add =Sum((Len($A$1:$B$50)-Len(Substitute($A$1:$B$50, A1,"")))/Len(A1)) as an Array formula and copy down. Mind you you will need to adjust the range to suit your needs. This will give you a 1 or 0 if a match is found.

mana
04-04-2017, 03:32 AM
Option Explicit


Sub test()
Dim dic As Object
Dim c As Range
Dim s1 As String, s2 As String
Dim n As Long

Set dic = CreateObject("scripting.dictionary")

For Each c In Cells(1).CurrentRegion.Columns(1).Cells
s1 = c.Value & "_" & c.Offset(, 1).Value
s2 = c.Offset(, 1).Value & "_" & c.Value

If Not dic.exists(s1) Then
If Not dic.exists(s2) Then
dic(s1) = dic.Count + 1
n = dic(s1)
Else
n = dic(s2)
End If
Else
n = dic(s1)
End If

c.Offset(, 2).Value = n

Next

End Sub

sharc316
04-08-2017, 07:53 AM
Thank you mana, this works great!!!

The only thing is that I have headers in row 1 and it deletes the header in C1. I've played with the code but cant seem to figure out the cause.

mana
04-08-2017, 06:43 PM
For Each c In Range("a2", Range("a" & Rows.Count).End(xlUp))

sharc316
04-09-2017, 11:08 AM
Perfect! Thank you very much mana.