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

Comments

  • Carrot007
    Carrot007 Posts: 4,534 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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!

  • In E4 enter formula =IF(COUNTIF(B:B,A4)>0,A4,"")
  • KingL2
    KingL2 Posts: 88 Forumite
    Fifth Anniversary 10 Posts
    Great! Those both work.   Much easier than what I was thinking!
    thanks
  • Heedtheadvice
    Heedtheadvice Posts: 2,762 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    edited 25 June 2021 at 10:14AM
    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
    af1963 Posts: 389 Forumite
    Fourth Anniversary 100 Posts Name Dropper
    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
    KingL2 Posts: 88 Forumite
    Fifth Anniversary 10 Posts
    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
    KingL2 Posts: 88 Forumite
    Fifth Anniversary 10 Posts
    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 :smile:
  • Heedtheadvice
    Heedtheadvice Posts: 2,762 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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.....
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.