Comparing 2 cells with 2 different cells within Excel...

[Waxaholic]

What i am needing to do is this:

I have the following data to compare...

Column 1 Column 2
____________________

1 4
1 7x
1 2
4 1
4 8
4 5

Column 1, #1 is associated with Column 2, #4. Column 1, #1 is associated with Column 2, #7x...and so on. This is true throughout the table. I am needing to verify that if the association is 1 and 4, is there an inverse association for 4 and 1. Everyone following me here. For every 1 - 4, there must be a corresponding 4 - 1. In the above, the 1 -4, and 4 -1 association would be true, yet, the association for 1 -7x would be false as there is no corresponding 7x - 1. Still with me? I hope, ha ha.

Anyone have any ideas as to what combination of formulas could help me here?

Thanks,

Wax

 

[Guest_imported]

If you want a complicated way to do it you can use this code. If you want to use this code but don't know how to please let me know.


Dim ColumnOne As String
Dim ColumnTwo As String
Dim ColumnThree As String
Dim Delimiter As String
Dim Header As Integer
Dim NumberofDataOne As Integer
Dim NumberofDataTwo As Integer
Dim MaxData As Integer
Dim MyArray() As String
Dim MyArrayOpp() As String

Private Sub CompareColumns()
ColumnOne = "A"
ColumnTwo = "B"
ColumnThree = "C"
Delimiter = "|"
Header = 1

NumberofDataOne = WorksheetFunction.CountA(ActiveSheet.Range(ColumnOne & ":" & ColumnOne)) - Header
NumberofDataTwo = WorksheetFunction.CountA(ActiveSheet.Range(ColumnOne & ":" & ColumnOne)) - Header

If NumberofDataOne > NumberofDataTwo Then MaxData = NumberofDataOne Else MaxData = NumberofDataTwo

ReDim Preserve MyArray(MaxData + 1)
ReDim Preserve MyArrayOpp(MaxData + 1)

For i = 1 To MaxData + 1
MyArray(i) = ActiveSheet.Range(ColumnOne & i).Value & Delimiter & ActiveSheet.Range(ColumnTwo & i).Value
MyArrayOpp(i) = ActiveSheet.Range(ColumnTwo & i).Value & Delimiter & ActiveSheet.Range(ColumnOne & i).Value
Next i

For i = 1 To MaxData + 1
For x = 1 To MaxData + 1
If MyArray(i) = MyArrayOpp(x) Then
ActiveSheet.Range(ColumnThree & i).Value = "Matching Values at Row" & x
ActiveSheet.Range(ColumnThree & x).Value = "Matching Values at Row" & i
MyArray(i) = "Matched1"
MyArrayOpp(x) = "Matched2"
End If
Next x
Next i
End Sub

 

[Waxaholic]

I have no clue as to how to use this code. I imagine i input it into the vb script editor from within Excel? I am willing to try this if you are willing to explain it to me.

Thanks,

Wax

 

[bkpchs237]

Waxaholic,
I offer you a combination of formulas to accomplish the task. I have taken the liberty to expand on your list a bit to explain the formulas and present you with an example.
My values start in cells E2 and end in cell F15. In cell G2 enter the following formula:
=f2&" "e2 Copy this formula down the entire column as needed. This will concatenate the values with a space in between them in the reverse order in this column.
Now in cell H2 enter the following formula:
=IF(ISNA(VLOOKUP(E2&" "&F2,$G$2:$G$15,1,FALSE)),"","match found&quot Copy this formula down the entire column as needed. This will perform the comparison for you and leave a blank if the condition is not satisfied, and the words "match found" if the condition is satisfied.

E F
1 4
1 7x
1 2
4 1
4 8
4 5
2 3
3 2
4 4
5 7
3 6
7 4
6 3
9 2


E F G H
1 4 4 1 match found
1 7x 7x 1
1 2 2 1
4 1 1 4 match found
4 8 8 4
4 5 5 4
2 3 3 2 match found
3 2 2 3 match found
4 4 4 4 match found
5 7 7 5
3 6 6 3 match found
7 4 4 7
6 3 3 6 match found
9 2 2 9

You can copy column, edit, paste special it in place to get rid of the formulas, and/or hide column G if needed.
Hope this helps.

 

[Guest_imported]

Waxaholic,
1.Open the VB Editor in Excel by pressing Alt+F11.

2.Open the Project Explorer by pressing Ctrl+R

3.Double click on the sheet that has the data in from the Project Explorer window.

4.Copy the code into the window on the right. (Sheet Code window)

5.Click inside the sub then click on the play button.

This shouold run the code and produce something like this on the sheet where the data is:

ColumnA ColumnB ColumnC
4 1 Matching Values at Row5
1 7x Matching Values at Row9
1 2
1 4 Matching Values at Row2
4 1 Matching Values at Row8
4 5 Matching Values at Row10
1 4 Matching Values at Row6
7x 1 Matching Values at Row3
5 4 Matching Values at Row7
f g Matching Values at Row13
h j
g f Matching Values at Row11


One thing to know is the first 5 lines of code in the sub define where the data is,where to put the answer,what character wont appear in the data (this is used to seperate the data i.e in thread above a space was used) and is there a header.

ColumnOne = "A" 'data column one
ColumnTwo = "B" 'data column two
ColumnThree = "C" 'where to put the answer
Delimiter = "|" 'delimiter character
Header = 1 'is there a header 1=yes 0=no

I hope this helps, let me know if it works.

 

[Guest_imported]

Don't know if you are that interested but I have made some changes to the sub. It no longer finds a match with itself.
Example:
4 4 doesn't match itself


Private Sub CompareColumns()
ColumnOne = "A"
ColumnTwo = "B"
ColumnThree = "C"
Delimiter = "|"
Header = 1

NumberofDataOne = WorksheetFunction.CountA(ActiveSheet.Range(ColumnOne & ":" & ColumnOne)) - Header
NumberofDataTwo = WorksheetFunction.CountA(ActiveSheet.Range(ColumnOne & ":" & ColumnOne)) - Header

If NumberofDataOne > NumberofDataTwo Then MaxData = NumberofDataOne Else MaxData = NumberofDataTwo

ReDim Preserve MyArray(MaxData + Header)
ReDim Preserve MyArrayOpp(MaxData + Header)

For i = 1 To MaxData + Header
MyArray(i) = ActiveSheet.Range(ColumnOne & i).Value & Delimiter & ActiveSheet.Range(ColumnTwo & i).Value
MyArrayOpp(i) = ActiveSheet.Range(ColumnTwo & i).Value & Delimiter & ActiveSheet.Range(ColumnOne & i).Value
Next i

For i = 1 To MaxData + Header
For x = 1 To MaxData + Header
If MyArray(i) = MyArrayOpp(x) Then
If i <> x Then
ActiveSheet.Range(ColumnThree & i).Value = &quot;Matching Values at Row&quot; & x
ActiveSheet.Range(ColumnThree & x).Value = &quot;Matching Values at Row&quot; & i
MyArray(i) = &quot;Matched1&quot;
MyArrayOpp(x) = &quot;Matched2&quot;
End If
End If
Next x
Next i
End Sub

 

[Waxaholic]

Woo Hoo, I want to send thanx to both Dark_Sun and bkpchs237 for their great lessons in obtaining my goal here. Both were a success. This is an awesome place to come to for expert help with problem solving such as this. I cannot thankyou enough as this will greatly help me in my future endeavors.

Thanks again for all your help,

Wax