Table Sort/Filter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D Richardson - 12 Jan 2006 07:39 GMT

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.

Reply to this Message

Dave Peterson - 12 Jan 2006 14:34 GMT

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

> Table A
>
[quoted text clipped - 24 lines]
> Regards,
> David.

Signature

Dave Peterson

Reply to this Message