Data DiSers assemble: MS Excel assistance required...

Team.

I have two tabs in the same doc with separate lists of customer details - names, DoB, postcodes etc., one record per line. One tab is for sales, the other is for cancellations, and there may be (actually will be) matching records on both tabs i.e. customers who have bought and then subsequently cancelled.

What I need to do is find those matching records. The best way would be to find records on both tabs that have the same postcode. How do I do this with a command rather than having to scroll through the whole list and check against the other one…?

T.I.A. n all that

You could add a column to one of the tabs with a countif formula counting how many times the postcode is in the relevant column on the other tab. Then you can apply a filter and filter out all the zeros, leaving you with a list of all the postcodes that are on both tabs

2 Likes

Index and match. I’ll remind myself the formula and send it

3 Likes

Index(column you want to return values from, match(cell you want to look up, column you want to look up against, 0))

2 Likes

I’d wrap it all in an IFERROR with ‘no match’ returned aswell, just to check its working properly

1 Like

image

6 Likes

I think this is the one - cheers.

Just checked though and I will have to use customer surname instead due to lack of postcodes on some records. The only issue with that is that it won’t be an identical match because the name field on one tab has first name and surname whereas the other just has surname…

Sheet 1

Sheet 2

Formula is

=IFERROR(INDEX(Sheet1!B:B, MATCH(Sheet2! A:A, 0)), “no match”)

1 Like

If you’ve got DoBs etc you might want to match against more than one criterion anyway.

https://exceljet.net/formulas/index-and-match-with-multiple-criteria

Edit: or countifs instead of countif if you’re using that method

1 Like

Is there something like a DOB field you could concatenate with surname to make it more unique

I know marckee, ‘more unique’ isn’t possible, everyone knows what I mean!

4 Likes

Yeah what froglet said

1 Like

Just realised how shit the data is on the sales tab:

Column A: inv no.
Column B: Sale date
Column C: 1st and 2nd name

On the canc tab I have detailed data and column H is the surname. So I need to create a new column on the canc tab and ask it to find examples of the surname (column H) in the sales tab…

Index match and countif/countifs will look for the full cell contents, so if you’re looking for “jane smith” but it’s on the other tab as “smith” it won’t find it. Probably easiest i think to first split the full name column into first name and surname and then use your new surname column. That can be messy if you’ve got any multiple word first or surnames, because you’ll probably need to use a space as the delimiter and then you end up with several columns.

If it’s a small data set, you might have no or few names that spill over into 3+ columns and you can correct those few manually. If it’s a massive data set, at this point I’d be asking myself if I can get better data, because even if you go through it manually you’re going to have some names where it’s not clear if the middle bit belongs with the first name or the surname. Do the customers have a customer ID or do the sales have an order number that might be recorded against both sales and cancellations?

1 Like

Yes - think you’ve cracked it @froglet :heart:

If you wanted to do it in the other way and look up say surname and DOB from the cancellations tab against the other tab that has full names instead of surnames, this might help

1 Like

Anyone know why my first row has this line underneath it? It’s not a border. Whatever it is, it’s preventing my import from working. Think it’s treating the first row as a header or something. Any help appreciated before I have a nervous breakdown, thanks.

Maybe freeze rows is enabled on the first row? Does row 1 stay at the top of the screen even as you scroll down?

I just discovered this seconds before you replied, it was indeed frozen. Turns out that’s not what’s causing my import to fail. Thanks anyway. :sob:

1 Like

Copied and pasted all of the cells into a new sheet and now it’s working :man_shrugging:

I hate Excel.

1 Like