Hi
Can somebody help me out please.
Attached is a Spreadsheet I havew made up. In this spreadsheet I have labelled one gropd of columns as Spreadsheet 1 and the other Spreadsheet 2. What I am trying to achieve is to match Last/First Name in Spreadsheet 1 to Last/First names in Spreadsheet 2. Where a match is made then the ID field is copied to Spreadsheet 1, (I realise some of the names aren’t complete.)
Any help greatly appreciated.
Regards
Nev
spreadsheet.ods (79.7 KB)
Hi, the problem with your data is that many first names or surnames contain a space at the end. This can then be used for comparison. I have highlighted the data with a conditional marker.
You can use the TRIM() function to avoid cleaning up all the data.
I have used TRIM() with INDEX() and MATCH() to determine the ID.
=IF(COUNTBLANK(A5:B5)=2,"", IFNA(INDEX(N$4:N$1297,MATCH(TRIM(A5&B5),TRIM(P$4:P$1297)&TRIM(Q$4:Q$1297),0)),""))
You will find the result in column M of your worksheet.
I hope this has helped you.
spreadsheet_jk.ods (87,3 KB)
Thank you so much.
The Spreadsheet 2 is an extract from my database so no sure why the space but will look into, could be me inputting.
Thank you for solving two problems greatly appreciated.
regards
Nev
dscheikey
May 27Hi, the problem with your data is that many first names or surnames contain a space at the end. This can then be used for comparison. I have highlighted the data with a conditional marker.
You can use the TRIM() function to avoid cleaning up all the data.
I have used TRIM() with INDEX() and MATCH() to determine the ID.
=IF(COUNTBLANK(A5:B5)=2,"", IFNA(INDEX(N$4:N$1297,MATCH(TRIM(A5&B5),TRIM(P$4:P$1297)&TRIM(Q$4:Q$1297),0)),""))You will find the result in column M of your worksheet.
I hope this has helped you.spreadsheet_jk.ods (87,3 KB)
Visit Topic or reply to this email to respond.
To unsubscribe from these emails, click here.
