We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Spreadsheet: Line-up matching cells next to each other (Excel/Liberoffice)
Options

KingL2
Posts: 88 Forumite

in Techie Stuff
This comes up a lot for me, and I'm getting tired of doing it manually, so I wonder if there's a systematic way...
I have a long list of data in alphabetical order (column A). Each cell is unique. I have a second list (Column
which is roughly the same dataset as the first, but has some items missing.
I want to (recursively) insert a blank cell in column B in all cases where there is no entry in Column B that matches with column A (moving the rest of column B DOWN). So that I end up with two columns where all the entries in Column B sit next to the corresponding entry in Column A.
Like this:
image
Later on, I will do some more processing to the right. I will usually make a simple (IF-statement based) matching test on A&B, then sort on that and process all the Matches in one way and the Mismatches in another way.
The data in column A is alphanumeric, and contains spaces. No special characters.
I don't want to rely on conditional formatting.
Occasionally, there might be typo in one of the cells that causes a Mismatch when it should be a Match, so I want to be able to line-up the data next to each other and eyeball any mismatches, so I can correct them manually, (rather than rely on entirely systematic methods that require a perfect match, e.g. Filters).
I guess that this would need a macro/VB script, but if it's possible to do it without, then even better.
Actually, I'm using Libreoffice Calc. But Excel 2013 is OK, too.
thanks very much
I have a long list of data in alphabetical order (column A). Each cell is unique. I have a second list (Column

I want to (recursively) insert a blank cell in column B in all cases where there is no entry in Column B that matches with column A (moving the rest of column B DOWN). So that I end up with two columns where all the entries in Column B sit next to the corresponding entry in Column A.
Like this:

Later on, I will do some more processing to the right. I will usually make a simple (IF-statement based) matching test on A&B, then sort on that and process all the Matches in one way and the Mismatches in another way.
The data in column A is alphanumeric, and contains spaces. No special characters.
I don't want to rely on conditional formatting.
Occasionally, there might be typo in one of the cells that causes a Mismatch when it should be a Match, so I want to be able to line-up the data next to each other and eyeball any mismatches, so I can correct them manually, (rather than rely on entirely systematic methods that require a perfect match, e.g. Filters).
I guess that this would need a macro/VB script, but if it's possible to do it without, then even better.
Actually, I'm using Libreoffice Calc. But Excel 2013 is OK, too.
thanks very much
0
Comments
-
Off the top of my head using a non macro method....I would insert a colum in the middle put in the formula.=IF(ISNA(VLOOKUP(A4,D:D,1,FALSE)),"",VLOOKUP(A4,D:D,1,FALSE))For the first one (in B4)Copy it down.Thats it.If you want it cleaned then copy/paste text the formula column over itself to make it text rather than formulas and delete the unordered row.But I'm sure there are many other ways!
1 -
In E4 enter formula =IF(COUNTIF(B:B,A4)>0,A4,"")1
-
Great! Those both work. Much easier than what I was thinking!
thanks
0 -
As a more basic suggestion - if it is possible for you.....If all the entries in column Shortlist must be contained in Longlist then you could use the longlist as a data source list and shortlist gets populated as a dropdown list with Longlist as it's source.?That helps prevent the typos you want to check manually plus some conditional formatting can highlight duplications and entries that may have been typed in that are not on the longlist.Plus use the above already posted methods if you still need to align such as to do an eyeball check....(you may want to align for other purposes though...)...........but I prefer to let the program do the work!!0
-
The suggested solutions won't allow you to visually check for "near-miss" typos by seeing them next to the correct item. If an item is mistyped in any way in column B, for example entering "Text C" with an extra space; it won't appear beside "Text C" in the new columns D and E. The system can't 'guess' which item it is meant to match - it will only 'know' that there is nothing that exactly matches "Text C".
Assuming everything in column B MUST match an entry in A, you can do another ( very similar) check to make sure the entries in column B do actually match items in column A.
I'd enter this in cell C4 so the results appear adjacent to the column B entries.
=IF(COUNTIF(A:A,B4)>0,"OK","ERROR")
1 -
Heedtheadvice said:you could use the longlist as a data source list
0 -
Ok, my suggestion is irrelevent then.Out of curiosity (not important so ignore this if you wish) why do you not want to use conditional formatting? You can use the formula similar to that latterly given to make the differences really stand out that automatically dissapear if you were to do any corrections, plus highlight other aspects such as duplications.....0
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.7K Banking & Borrowing
- 253K Reduce Debt & Boost Income
- 453.4K Spending & Discounts
- 243.7K Work, Benefits & Business
- 598.5K Mortgages, Homes & Bills
- 176.8K Life & Family
- 256.9K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards