Discussion Locked

Matching items in Excel Columns

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

4 replies

by guy

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.

by theophotech

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

This discussion is closed to new replies.