Forum Discussion

Supermanfan75's avatar
Supermanfan75
Copper Contributor
May 28, 2019

How do I make 2 columns match/line up with one another with matching data?

I have two columns of data numbers I want to match/line with one another but want to see if they can be spaced if not matching between the columns? I have found conditional formatting to mark the two...
excel
Twifoo's avatar
Twifoo
Silver Contributor
May 28, 2019
Why not list in Column C the items in Column A that are not found in Column B?
  • Supermanfan75's avatar
    Supermanfan75
    Copper Contributor
    May 28, 2019

    That could help too. I am looking for any way to make it easier or show. Twifoo 

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 28, 2019

      Supermanfan75 

      You could use this formula in Column C, starting in C2, copied down rows: 

      =IFERROR(INDEX(A$2:A$10,
      AGGREGATE(15,6,
      1/((COUNTIF(B$2:B$10,A$2:A$10)=0)*(COUNTIF(C$1:C1,A$2:A$10)=0))*(ROW(A$2:A$10)-1),1)),"")

      See the sample in the attached file. 

      • Supermanfan75's avatar
        Supermanfan75
        Copper Contributor
        May 29, 2019

        I have tried the formula and it does not go pass 10 rows. I have copied and moved column A down to highlight all the cells and did the same thing in column B and still comes up as 0 in Column C. I see in your download/sample file it displayed the difference but if change the numbers (copy/paste) out, nothing occurs. The first 10 rows I know match but going farther does nothing. I must be not doing something right. Even if I change all the A's to the ending row number and the B number row, still nothing occurs any different.  Twifoo 

Resources