General discussion

Locked

Matching items in Excel Columns

By lgsellers ·
If I have two Columns in an Excel spreadsheet of numbers and there are NOT the same number of items in each column, is there an easy way to match up like items. I've sorted them, but it doesn't help much because there are miscellaneous items in between due to the difference in list size. I would like for the like items to be matched up side by side or highlighted. Is this possible? Any help would be appreciated.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Matching items in Excel Columns

by guy In reply to Matching items in Excel C ...

There is a Function in Excel by the name of MATCH (Try Help Function Match) It works a bit like Lookup, however the Syntax is a bit fiddly. This is what it does:

Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

Syntax

MATCH(lookup_value,lookup_array,match_type)
I would try and get an example to see how itworks.

Collapse -

Matching items in Excel Columns

by lgsellers In reply to Matching items in Excel C ...
Collapse -

Matching items in Excel Columns

by TheoPhoTech In reply to Matching items in Excel C ...

Your dilemma has also been mine for awhile, so when I saw your question I decided to write a macro to handle this. After running the macro you will be left with 3 columns of data, 2 showing the differences and the 3rd with the similarities combined.Paint the differences with different colors and recombine them with the 3rd list and your 2 list will be side-by-side and the differences will be easy to see.
===copy from next line down to end and paste into Excel VBA editor
'=============
Option Explicit
Sub CompareList()
Dim FirstListRow As String
Dim SameListRow As String
Dim CompareValue As String
Dim Message As String
Dim Response As Integer
'Set default values
FirstListRow = 1
SameListRow = 1

'Reminder of proper set up
Message = "Both lists must start in row 1, with the first list in column A " & _
"and the second list in column B. Only the differences will be " & _
"left in columns A & B, and the similarities combined and placed " & _ "in column C. Both

Collapse -

Matching items in Excel Columns

by lgsellers In reply to Matching items in Excel C ...

Thank you.

Back to Windows Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums