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