MATCHING DATA IN COLUMNS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnS - 21 Nov 2005 14:39 GMT

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 to this Message

Max - 21 Nov 2005 15:13 GMT

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)*(Sheet2!$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
--

> I have 2 spreadsheets containing names, addresses, d.o.b. etc as column
> headings.
[quoted text clipped - 9 lines]
>
> Any help much appreciated

Reply to this Message