E
EdStevens
Excel 2003
Given two worksheets in a .xls file.
First worksheet is lookup data. First row of each column is a group name,
say GrpA, GrpB, GrpC. Under each group name (rows 2 thru n) are names of
people, say Tom, Dick, Harry. GrpA may have 5 names (rows 2 to 6) and GrpB
may have 10 names (rows 2 to 11), etc. So we have say:
A1 = GrpA
A2 = Tom
A3 = Dick
A4 = Harry
B1 = GrpB
B2 = Jane
B3 = Sally
Second worksheet is working data. First column is list of names, starting in
row 2. Columns B thru Z have a group name in row 1 -- GrpA, GrpB, etc --
matching the Group names in the lookup worksheet. A group name in row 1 may
appear in multiple columns. So we may have
A1 = "Name"
A2 = Tom
A3= Dick
A4 = Harry
B1 = "GrpA"
C1 = "GrpB"
D1 = "GrpC"
E1 = "GrpA"
Some of the names in the working data (Col A) will appear under more than
one group in the lookup data.
Some of the names in the working data will appear under exactly one group in
the lookup data.
Some of the names in the working data will appear under no groups in the
lookup data.
The task is to set a formula in the working data, at the intersection of a
name and a group, that will place an "X" in the cell if the name in that row
appears in the column of the matching group in the lookup sheet.
I've been playing around with IF, the various LOOKUP functions, and the
various IS* functions, but have not come up with something that works.
Given two worksheets in a .xls file.
First worksheet is lookup data. First row of each column is a group name,
say GrpA, GrpB, GrpC. Under each group name (rows 2 thru n) are names of
people, say Tom, Dick, Harry. GrpA may have 5 names (rows 2 to 6) and GrpB
may have 10 names (rows 2 to 11), etc. So we have say:
A1 = GrpA
A2 = Tom
A3 = Dick
A4 = Harry
B1 = GrpB
B2 = Jane
B3 = Sally
Second worksheet is working data. First column is list of names, starting in
row 2. Columns B thru Z have a group name in row 1 -- GrpA, GrpB, etc --
matching the Group names in the lookup worksheet. A group name in row 1 may
appear in multiple columns. So we may have
A1 = "Name"
A2 = Tom
A3= Dick
A4 = Harry
B1 = "GrpA"
C1 = "GrpB"
D1 = "GrpC"
E1 = "GrpA"
Some of the names in the working data (Col A) will appear under more than
one group in the lookup data.
Some of the names in the working data will appear under exactly one group in
the lookup data.
Some of the names in the working data will appear under no groups in the
lookup data.
The task is to set a formula in the working data, at the intersection of a
name and a group, that will place an "X" in the cell if the name in that row
appears in the column of the matching group in the lookup sheet.
I've been playing around with IF, the various LOOKUP functions, and the
various IS* functions, but have not come up with something that works.