Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-651059%22%20slang%3D%22en-US%22%3EHow%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651059%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20of%20data%20numbers%20I%20want%20to%20match%2Fline%20with%20one%20another%20but%20want%20to%20see%20if%20they%20can%20be%20spaced%20if%20not%20matching%20between%20the%20columns%3F%20I%20have%20found%20conditional%20formatting%20to%20mark%20the%20two%20that%20are%20not%20matching%20the%20rows%20but%20it%20is%20taking%20lots%20of%20time%20to%20get%20thru%20like%205000.%26nbsp%3B%20I%20am%20trying%20to%20see%20if%20I%20can%20get%20Column%20A%20and%20Column%20B%20to%20line%20up%20with%20one%20another%20while%20spacing%20in%20the%20row%20when%20they%20don't%20but%20moving%20the%20numbers%20to%20line%20up%20with%20one%20another%20when%20they%20match.%26nbsp%3B%3C%2FP%3E%3CP%3EI.E%3C%2FP%3E%3CP%3EColumn%20A%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Column%20B%3C%2FP%3E%3CP%3E123%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20123%3C%2FP%3E%3CP%3E456%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20456%3C%2FP%3E%3CP%3E789%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%201001%3C%2FP%3E%3CP%3E1001%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%201002%3C%2FP%3E%3CP%3E1002%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%201003%3C%2FP%3E%3CP%3E1003%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%201004%3C%2FP%3E%3CP%3E1004%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20it%20to%20look%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20ColumnB%3C%2FP%3E%3CP%3E123%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20123%3C%2FP%3E%3CP%3E456%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20456%3C%2FP%3E%3CP%3E789%3C%2FP%3E%3CP%3E1001%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201001%3C%2FP%3E%3CP%3E1002%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201002%3C%2FP%3E%3CP%3E1003%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201003%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20is%20possible.%26nbsp%3B%20Basically%20I%20am%20working%20with%20item%20numbers%20at%20my%20work%2C%20each%20day%20some%20items%20go%20out%20of%20stock%20or%20they%20add%20new%20items%20that%20I%20am%20wanting%20to%20match%20the%20item%20numbers%20quickly%20but%20point%20out%20ones%20that%20are%20gone%20or%20new.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20an%20expert%20in%20Excel%2C%20so%20if%20it%20involves%20expert%20steps%2Fprogramming%20(if%20what%20I%20am%20wanting%20to%20do%20is%20possible)%2C%20just%20list%20the%20steps%20or%20what%20I%20need%20to%20do.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20all%20your%20time%20and%20help!!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-651059%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651074%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651074%22%20slang%3D%22en-US%22%3EWhy%20not%20list%20in%20Column%20C%20the%20items%20in%20Column%20A%20that%20are%20not%20found%20in%20Column%20B%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651075%22%20slang%3D%22en-US%22%3ERE%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651075%22%20slang%3D%22en-US%22%3EThat%20could%20help%20too.%20I%20am%20looking%20for%20any%20way%20to%20make%20it%20easier%20or%20show.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651083%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651083%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20could%20help%20too.%20I%20am%20looking%20for%20any%20way%20to%20make%20it%20easier%20or%20show.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651152%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349757%22%20target%3D%22_blank%22%3E%40Supermanfan75%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20use%20this%20formula%20in%20Column%20C%2C%20starting%20in%20C2%2C%20copied%20down%20rows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(INDEX(A%242%3AA%2410%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EAGGREGATE(15%2C6%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F((COUNTIF(B%242%3AB%2410%2CA%242%3AA%2410)%3D0)*(COUNTIF(C%241%3AC1%2CA%242%3AA%2410)%3D0))*(ROW(A%242%3AA%2410)-1)%2C1))%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20the%20sample%20in%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654089%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654089%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20the%20formula%20and%20it%20does%20not%20go%20pass%2010%20rows.%20I%20have%20copied%20and%20moved%20column%20A%20down%20to%20highlight%20all%20the%20cells%20and%20did%20the%20same%20thing%20in%20column%20B%20and%20still%20comes%20up%20as%200%20in%20Column%20C.%20I%20see%20in%20your%20download%2Fsample%20file%20it%20displayed%20the%20difference%20but%20if%20change%20the%20numbers%20(copy%2Fpaste)%20out%2C%20nothing%20occurs.%20The%20first%2010%20rows%20I%20know%20match%20but%20going%20farther%20does%20nothing.%20I%20must%20be%20not%20doing%20something%20right.%20Even%20if%20I%20change%20all%20the%20A's%20to%20the%20ending%20row%20number%20and%20the%20B%20number%20row%2C%20still%20nothing%20occurs%20any%20different.%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654223%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349757%22%20target%3D%22_blank%22%3E%40Supermanfan75%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20returns%20the%20correct%20results%20only%20until%20Row%2010%20because%20the%20ranges%20are%20static.%20Such%20ranges%20in%20the%20attached%20version%20of%20the%20file%20are%20now%20dynamic%2C%20such%20that%20the%20formula%20in%20C2%2C%20copied%20down%20rows%2C%20has%20been%20modified%20to%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(INDEX(ListA%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EAGGREGATE(15%2C6%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F((COUNTIF(ListB%2CListA)%3D0)*(COUNTIF(C%241%3AC1%2CListA)%3D0))*(ROW(ListA)-1)%2C1))%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662410%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%202%20columns%20match%2Fline%20up%20with%20one%20another%20with%20matching%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662410%22%20slang%3D%22en-US%22%3E%3CP%3EI%20must%20be%20not%20doing%20something%20right%20or%20missing%20something.%20I%20took%20your%20second%20file%20and%20it%20just%20shows%200%20each%20time.%20I%20have%20copy%20paste%2C%20highlighted%20the%20rows%20and%20it%20is%20not%20picking%20up%20differences.%26nbsp%3B%20Maybe%20it%20is%20easier%20to%20show%20the%20worksheet%20I%20have%20I%20am%20trying%20to%20work%20with.%26nbsp%3B%20I%20do%20want%20to%20tell%20you%2C%20Thank%20you%20for%20all%20your%20help%20and%20time%20with%20this!!!%20%26nbsp%3B%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Supermanfan75
New Contributor

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.  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. 

I.E

Column A      Column B

123                    123

456                     456

789                    1001

1001                  1002

1002                  1003

1003                  1004

1004

 

I would like it to look like:

 

Column A       ColumnB

123                    123

456                    456

789

1001                 1001

1002                 1002

1003                 1003

 

If it is possible.  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. 

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. 

 

I appreciate all your time and help!!!!!

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

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

@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. 

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 

@Supermanfan75 

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: 

=IFERROR(INDEX(ListA,
AGGREGATE(15,6,
1/((COUNTIF(ListB,ListA)=0)*(COUNTIF(C$1:C1,ListA)=0))*(ROW(ListA)-1),1)),"")

I must be not doing something right or missing something. I took your second file and it just shows 0 each time. I have copy paste, highlighted the rows and it is not picking up differences.  Maybe it is easier to show the worksheet I have I am trying to work with.  I do want to tell you, Thank you for all your help and time with this!!!    @Twifoo 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies