data comparison

P

philaboast

New Member
Joined
Dec 12, 2009
Messages
2
Hi- Would like suggestions on how to compare arrays of data for exact matches and receive outputs of cell references instead of a cumulative number of matches using =MATCH
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Welcome to the board!

Please provide some more detail about the inputs and desired output and someone will be able to help you. Is it something like this?

Excel Workbook
ABCDE
1INPUT 1INPUT 2DESIRED OUTPUT
2aaa
3acc
4ae
5bf
6cf
7dg
8dh
1
Excel 2003


...and roughly how many rows are in each input? 10? 1,000? 100,000?

When you say "arrays," I wonder if you mean to imply that you are looking for a VBA solution. Do you require either code or non-code solution, or does it matter?
 
Upvote 0
Hi- typically i am using 2 columns of data with roughly 300-400 individual cells per column. Yes, you are correct that I would like to receive the matched cells in a third column. Additionally, would it be possible to compare more than 2 columns against each other?
 
Upvote 0
Give this a try (press Alt+F11, click Insert, click Module, paste this in, then press Alt+F8 to run):

Code: 
Option Explicit

Sub GetUniquesMultipleColumns()
Dim Rng As Range, Dn As Range
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    Do
        On Error Resume Next
        Set Rng = Nothing
        Set Rng = Application.InputBox("Click the top-most item of the next column to compare.  If no more, click Cancel.", Type:=8)
        On Error GoTo 0
        If Rng Is Nothing Then Exit Do
        Set Rng = Range(Rng, Cells(Rows.Count, Rng.Column).End(xlUp))
        For Each Dn In Rng
            If Not .Exists(Dn.Value) Then .Add Dn.Value, Dn.Next.Value
        Next
    Loop
    Set Rng = Application.InputBox("Click the cell where you want the unique list to begin.", Type:=8).Resize(.Count)
    Rng = Application.Transpose(Array(.Keys))
End With
If MsgBox("Do you want to sort the unique list?", vbYesNo, "Sort List?") = vbYes Then Rng.Sort Key1:=Rng(1), Order1:=xlAscending, Header:=xlNo
End Sub

Before:
Excel Workbook
ABCDE
1INPUT 1INPUT 2OUTPUT
2aa
3b
4bc
5cd
6d1
712
823
934
10
11
1
Excel 2003

After:
Excel Workbook
ABCDE
1INPUT 1INPUT 2OUTPUT
2aa1
3b2
4bc3
5cd4
6d1
712a
823b
934c
10d
11
1
Excel 2003

...As the code ran and threw up prompts, I clicked on A2 (first column to compare), clicked OK, clicked on C2 (next column to compare), clicked OK, clicked cancel (no more columns to compare), clicked E2 (where unique list should begin), then clicked yes when asked whether to sort the result.

Hope that helps.

Tai
 
Upvote 0
If you make a slight change to the code, it can be used on multiple workbooks at the same time. You can a compare column in one workbook, then use the Window menu to switch to a different workbook and choose a compare column from it as well.

Rich (BB code): 
Option Explicit

Sub GetUniquesMultipleColumns()
Dim Rng As Range, Dn As Range
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    Do
        On Error Resume Next
        Set Rng = Nothing
        Set Rng = Application.InputBox("Click the top-most item of the next column to compare.  If no more, click Cancel.", Type:=8)
        On Error GoTo 0
        If Rng Is Nothing Then Exit Do
        Set Rng = Rng.Parent.Range(Rng, Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp))
        For Each Dn In Rng
            If Not .Exists(Dn.Value) Then .Add Dn.Value, Dn.Next.Value
        Next
    Loop
    Set Rng = Application.InputBox("Click the cell where you want the unique list to begin.", Type:=8).Resize(.Count)
    Rng = Application.Transpose(Array(.Keys))
End With
If MsgBox("Do you want to sort the unique list?", vbYesNo, "Sort List?") = vbYes Then Rng.Sort Key1:=Rng(1), Order1:=xlAscending, Header:=xlNo
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
This one is tricky. VLookup but return a different cell value
Replies
6
Views
176
Alex Blakenburg
A
C
  • Question
Index Match (find the next highest value) + multiple criteria
Replies
4
Views
61
JamesCanale
JamesCanale
D
  • Question
Trying to create a results sheet using two data ranges
Replies
0
Views
168
Doot
D
D
  • Solved
Combine 3 different index-match functions but referencing 1 specific helper cell
Replies
6
Views
76
derman0524
D
R
  • Question
Best approach (VBA) to search through a sheet for all matches to a selected range (ie. multiple cells)?
Replies
2
Views
67
radiocam
R

Forum statistics

Threads
1,214,911
Messages
6,122,196
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top