Table Sort/Filter

D

D Richardson

Table A

User ParameterA ParameterB ParameterC ParameterD ParameterE
Jim x x x
John x x x
Dave x
Henry x
Jim x
John x
Dave x
Henry x

Table B
User ParameterA ParameterB ParameterC ParameterD ParameterE
Dave x x
Henry x x
Jim x x x x
John x x x x



How can I transform table A into Table B, see my earlier post for
attachment, (there are no viruses or macros attached to it). I basically
want to remove duplicate user names, but preserve and combine the parameter
fields.

Any help would be appreciated.

Regards,
David.
 
D

Dave Peterson

Assuming the data is on Sheet1.

Create a new sheet (named sheet2).
Select A1 of sheet2
data|filter|advanced filter
check copy to another location
list range: sheet1!a1:A##
criteria range: (leave blank)
copy to: A1 (of sheet2)
check unique records only.

You'll end up with a list of unique names. Sort this column if you want.

Copy the headers from sheet1 (B1:F1) to B1 of sheet2.

Put this in B2 of sheet2.
=IF(SUMPRODUCT(--(Sheet1!$A$1:$A$99=$A2),--(Sheet1!B$1:B$99="X"))>0,"X","")
Adjust the range, but don't use the whole column--and watch the $ signs.

Drag it down the column.
Select that range/column of formulas and drag to the right
 
Top