Kerry has attended:
Excel Advanced course
Matching data on two sheets
Hi
I have a bank statement saved as a spreadsheet and a list of applicants in a separate spreadsheet.
I need to match the bank receipts with the applicants' surnames.
I am pretty sure this is reasonably straightforward but would appreciate some guidance please.
Thanks
Kerry
RE: Matching data on two sheets
Hello Kerry,
Thank you for your question. This is certainly possible in Excel. Could I first ask whether the surnames in the two sheets are in their own columns? This will simply make it easier to match them up.
If not, it would be worth getting them into separate columns. This is normally not hard.
I look forward to hearing back from you.
Kind regards
Marius Barnard
STL
RE: Matching data on two sheets
Hello Marius
Thank you for your reply.
The surname is in a separate column on the applicants list.
The bank statement has two separate columns where the surname would be found - currently named Narrative Line 1 and Narrative Line 2, obviously I can rename those.
Parents were asked to use the surname as the reference and in most cases they have. Occasionally they use something random but anomalies would stand out.
Thanks
Kerry
RE: Matching data on two sheets
Hi Kerry,
Thanks for your reply. In that case, the simplest function to use would be Vlookup. In the sheet where you want to bring in data from the other sheet, somewhere in the first record's row, type the Vlookup function. The structure is as follows:
=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, Range_Lookup)
The Lookup Value would be the cell in the current sheet's first record containing the surname.
The Table Array would be the entire data set in the other sheet. (Make sure you don't include any columns to the left of the Surname column in your selection) Also, lock the cell references of your selection with dollar symbols (e.g. $A$1:$J$1000)
Because this data set is in a separate sheet, show the two sheets side by side first, to make it easier to select the data set.
The Col Index Num is the NUMBER of the column in that data set containing the data you wish to bring across. (you need to count columns starting from the first selected column of the data set)
Range Lookup is where you tell Excel to look for exact matches between the two sets of surnames. Type FALSE here.
If the formula works for the first row, copy it down for all the records.
I hope this helps.
Kind regards
Marius
