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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

S
  • Solved
Table comparison using formulas
Replies
2
Views
113
sharshra
S
C
  • Question Question
XLookup -or- Countif with leading numbers then Character
Replies
1
Views
72
Alex Blakenburg
A
F
  • Question Question
Looking for help or guidance on a potential Macro project
Replies
1
Views
57
Rijnsent
Rijnsent
S
  • Question Question
How to Compare Multiple Scenarios Dynamically in Excel Without Displaying All Scenarios in One Table
Replies
0
Views
77
Shahar
S
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
315
Mouse7681
M

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

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