Spreadsheet: Line-up matching cells next to each other (Excel/Liberoffice)

KingL2

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

Carrot007

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!

WaywardDriver

In E4 enter formula =IF(COUNTIF(B:B,A4)>0,A4,"")

KingL2

Great! Those both work.   Much easier than what I was thinking!
thanks

Heedtheadvice

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

af1963

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")

KingL2

af1963 said:

=IF(COUNTIF(A:A,B4)>0,"OK","ERROR")

"if something occurs in SHORTLIST that wasn't in LONGLIST, then flag it as an error".  Nice, thanks.

KingL2

Heedtheadvice said:

you could use the longlist as a data source list

Hi - no, Longlist and Shortlist already exist independently at the start.  I am trying to compare them, here

Heedtheadvice

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