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
Index and match. I’ll remind myself the formula and send it
Index(column you want to return values from, match(cell you want to look up, column you want to look up against, 0))
I’d wrap it all in an IFERROR with ‘no match’ returned aswell, just to check its working properly
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…
=IFERROR(INDEX(Sheet1!B:B, MATCH(Sheet2! A:A, 0)), “no match”)
If you’ve got DoBs etc you might want to match against more than one criterion anyway.
Edit: or countifs instead of countif if you’re using that method
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!
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?
Yes - think you’ve cracked it @froglet
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