General discussion

  • Creator
  • #2074019

    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.

All Comments

  • Author
    • #3777397

      Matching items in Excel Columns

      by guy ·

      In reply to Matching items in Excel Columns

      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.


      I would try and get an example to see how itworks.

    • #3777958

      Matching items in Excel Columns

      by theophotech ·

      In reply to Matching items in Excel Columns

      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

Viewing 1 reply thread