Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCHING DATA IN COLUMNS
I have 2 spreadsheets containing names, addresses, d.o.b. etc as column
headings. I want to identify in the first spreadsheet those records where the data under the column headings: surname, forename,d.o.b. exactly match any records in the second spreadsheet. It would be useful if I could locate the identified rows to a third spreadsheet. Spreadsheet 1 has 450 rows, spreadsheet 2 has 14,000 rows. Any help much appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCHING DATA IN COLUMNS
One play to try ..
Assume the tables in Sheet1 (450 rows) and Sheet2 (14000 rows) are identically structured in cols A to C, with headers in A1:C1, data from row2 down, viz.: SurName ForeName DOB SName1 FName1 Date1 SName2 FName2 Date2 SName3 FName3 Date3 etc In Sheet1 ----------- Use an empty col to the right, say, col F Put in the formula bar for F2, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(ISNUMBER(MATCH(1,(Sheet2!$A$2:$A$14001=A2)*(Sh eet2!$B$2:$B$14001=B2)*(Sh eet2!$C$2:$C$14001=C2),0)),ROW(),"") Copy F2 down until the last row of data (to F451) (Leave F1 empty) In a new Sheet3 ---------------- With the same headers placed in A1:C1 Put in A2: =IF(ISERROR(SMALL(Sheet1!$F:$F,ROW(A1))),"",INDEX( Sheet1!A:A,MATCH(SMALL(She et1!$F:$F,ROW(A1)),Sheet1!$F:$F,0))) Copy A2 across to C2, fill down to C451 (cover the same range size as in Sheet1) Sheet3 will return the desired results, all neatly bunched at the top Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "JohnS" wrote in message ... I have 2 spreadsheets containing names, addresses, d.o.b. etc as column headings. I want to identify in the first spreadsheet those records where the data under the column headings: surname, forename,d.o.b. exactly match any records in the second spreadsheet. It would be useful if I could locate the identified rows to a third spreadsheet. Spreadsheet 1 has 450 rows, spreadsheet 2 has 14,000 rows. Any help much appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data in narrow columns truncated when saving as DBF | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Convert 1 row of data into Multiple columns | Excel Discussion (Misc queries) | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |