lookup value based on multiple criteria

[Brent Blackburn]

Banging my head on this one... can anyone help??

I've got several thousand rows of data in three columns,
structured similar to example below. Each set of ID
numbers represents a separate contact entry (person) in
an address book. FIELDNAME could include one or more of
about 200 fields, and VALUE may be blank.

ID FIELDNAME VALUE
1 FirstName Bob
1 LastName Smith
1 Company Tech Smith, Inc.
2 LastName Johnson
2 Company <blank>
2 FirstName Jim

I've got a second sheet set up with all of the 200
possible FIELDNAMEs placed horizontally one per cell
across the first row. Essentially I'd like to normalize
this table so that for each unique ID there is only one
row of data per contact, having matched up the FIELDNAME
with the proper VALUE for each contact. This is what I
need:

ID FirstName LastName Company
1 Bob Smith TechSmith, Inc.
2 Jim Johnson <blank>

Keep in mind that not every contact has the same number
of fields, so my lookup array range is not a predictable
number of rows. How should I approach this? Thanks!

Brent

 

[Frank Kabel]

Hi
on your second sheet enter the following array formula in B2 (entered
with CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,('sheet1'!$A$1:$A$100=$A2)*('sheet1'!$B$1:$B$100=B$1),
0)),"",INDEX('sheet1'!$C$1:$C$100,MATCH(1,('sheet1'!$A$1:$A$100=$A2)*('
sheet1'!$B$1:$B$100=B$1),0)))
and copy down / to the right

 

[Brent Blackburn]

Thanks very much for the help. Looks like it'll work
great once I tailor it to the real data!