I have three files with data about a set of individuals where each
file has different fields. The largest contains last name, first
name, address and phone with data on about 8,000 names. The second
is a smaller file with last name, first name, address in a different
format, birthday, and region code. Most but not all of the names in
the second file are also in the first file. The third is the smallest
file containing names, address, and two activity code fields. I want
to combine these files to create a single record for each name
containing the fields from all three.

Here is an example of the file when it is simply combined:

Last First Addr1 Addr2 Phone Birth Region Act1 Act2
BB AA 1111 2222
BB BB 1234 3333
BB AA 1111 1988 2
BB AA 1111 2 3
4

I want to get this data to look like this:

Last First Addr1 Addr2 Phone Birth Region Act1 Act2
BB AA 1111 1111 2222 1988 2 3 4
BB BB 1234 3333

Do I have to use VBA? I tried using Access but don't have the skills
to make it work - and I'd rather leave it in excel anyway.

Here's one formulas play ..

Illustrated in this sample:
http://www.savefile.com/files/1314960
Extract unique names n combine fields.xls

Assume source data is in a sheet: x,
in cols A to I, data from row2 down

Use 2 empty cols to the right, eg cols J and K
In J2: =IF(COUNTA(A2:B2)<2,"",A2&"_"&B2)
In K2: =IF(J2="","",IF(COUNTIF(J$2:J2,J2)1,"",ROW()))
Leave K1 empty. Select J2:K2, copy down to cover the max expected extent of
source data

Then in another sheet: y,
with the same col headers pasted into A1:I1

In A2:
=IF(ROWS($1:1)COUNT(x!$K:$K),"",INDEX(x!A:A,SMALL (x!$K:$K,ROWS($1:1))))
Copy A2 to B2

In C2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(ISNA(MATCH(1,(x!$A$2:$A$100=$A2)*(x!$B$2:$B$10 0=$B2)*(x!C$2:C$100<""),0)),"",
IF(INDEX(x!C$2:C$100,MATCH(1,(x!$A$2:$A$100=$A2)*( x!$B$2:$B$100=$B2)*(x!C$2:C$100<""),0))=0,"",
INDEX(x!C$2:C$100,MATCH(1,(x!$A$2:$A$100=$A2)*(x!$ B$2:$B$100=$B2)*(x!C$2:C$100<""),0))))
Copy C2 across to I2. Select A2:I2, copy down to the same extent as done in
x's cols J & K, to extract the required results. Cols A and B will return
the unique names while cols C to I will populate the other cols accordingly.
Remember to adapt the ranges in C2's array formula to suit the extent done
in x's cols J & K before copying across.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pavil" wrote in message
...
I have three files with data about a set of individuals where each
file has different fields. The largest contains last name, first
name, address and phone with data on about 8,000 names. The second
is a smaller file with last name, first name, address in a different
format, birthday, and region code. Most but not all of the names in
the second file are also in the first file. The third is the smallest
file containing names, address, and two activity code fields. I want
to combine these files to create a single record for each name
containing the fields from all three.

Here is an example of the file when it is simply combined:

Last First Addr1 Addr2 Phone Birth Region Act1 Act2
BB AA 1111 2222
BB BB 1234 3333
BB AA 1111 1988 2
BB AA 1111 2 3
4

I want to get this data to look like this:

Last First Addr1 Addr2 Phone Birth Region Act1 Act2
BB AA 1111 1111 2222 1988 2 3 4
BB BB 1234 3333

Do I have to use VBA? I tried using Access but don't have the skills
to make it work - and I'd rather leave it in excel anyway.