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

Question

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 that are not matching the rows but it is taking lots of time to get thru like 5000.&nbsp; I am trying to see if I can get Column A and Column B to line up with one another while spacing in the row when they don't but moving the numbers to line up with one another when they match.&nbsp;I.EColumn A &nbsp; &nbsp;&nbsp; Column B123 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 123456 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 456789 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 10011001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 10021002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 10031003 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 10041004&nbsp;I would like it to look like:&nbsp;Column A &nbsp; &nbsp; &nbsp; ColumnB123 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 123456 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 4567891001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10011002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10021003 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1003&nbsp;If it is possible.&nbsp; Basically I am working with item numbers at my work, each day some items go out of stock or they add new items that I am wanting to match the item numbers quickly but point out ones that are gone or new.&nbsp;I am not an expert in Excel, so if it involves expert steps/programming (if what I am wanting to do is possible), just list the steps or what I need to do.&nbsp;&nbsp;I appreciate all your time and help!!!!!

twifoo · Answer

Why not list in Column C the items in Column A that are not found in Column B?

supermanfan75 · Answer

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

twifoo · Answer

Supermanfan75&nbsp;You could use this formula in Column C, starting in C2, copied down rows:&nbsp;=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.&nbsp;

supermanfan75 · Answer

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.&nbsp; Twifoo&nbsp;

twifoo · Answer

Supermanfan75&nbsp;The formula returns the correct results only until Row 10 because the ranges are static. Such ranges in the attached version of the file are now dynamic, such that the formula in C2, copied down rows, has been modified to:&nbsp;=IFERROR(INDEX(ListA,AGGREGATE(15,6,1/((COUNTIF(ListB,ListA)=0)*(COUNTIF(C$1:C1,ListA)=0))*(ROW(ListA)-1),1)),"")

Forum Discussion

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

6 Replies

Resources