I am working with Excel 2000 and am trying to solve the following
issue:

ID STRUC MW ACTT ACTUOM ACTPRE ACTVAL
12 a 123 IC50 nm
= 0.12
12 a 123 EC50 nm
= 0.18
13 b 124 IC50 nm
= 0.16
13 b 124 EC50 nm
= 0.12
14 c 123 IC50 nm
= 0.10
15 d 128 IC50 nm
= 0.15
15 d 128 EC50 nm
= 0.12
15 d 128 ED50 nm
= 0.13


I have the table above. I want to remove duplicates in rows ID, STRUC
and MW as long as the ID is the same as the row being deleted (for
instance I do not want to remove the MW of line ID 14 because it is
unique even though it is the same as ID 12). I also want to leave the
ACTT, ACTUOM, ACTPRE and ACTVAL data as it is listed.

My end goal would to be a worksheet with no duplicate ID columns, but
the data listed for ACT columns.

An even better worksheet would take away all the duplications in the
ID, STRUC and MW columns of a specific ID and then add unique ACT
(like ACTT1 ACTUOM1, etc.….) columns into new ACT columns at the end
of the row.

So the logic as I see it is:

IF ID = duplicate
Check specific columns (STRUC, MW) for duplicate values

IF duplicate values = YES
Delete second value

IF duplicate values = YES
Populate ACTT(n) column (This would be an new column at the end of the
row)with ACTT value and delete value.

Etc. for the columns requested.

IF row = blank
Delete row

Please lend me a hand if you can. I am very much a novice at this
type of thing. I attempted to do this via pivot table, but I can’t
seem to make that work for this particular problem.

Have you experimented with Advanced Filters? Simply filtering on 'unique
records only' may be helpful to you in this situation.

Hope this helps.

Tara H

"thoerter" wrote:

I am working with Excel 2000 and am trying to solve the following
issue:

ID STRUC MW ACTT ACTUOM ACTPRE ACTVAL
12 a 123 IC50 nm
= 0.12
12 a 123 EC50 nm
= 0.18
13 b 124 IC50 nm
= 0.16
13 b 124 EC50 nm
= 0.12
14 c 123 IC50 nm
= 0.10
15 d 128 IC50 nm
= 0.15
15 d 128 EC50 nm
= 0.12
15 d 128 ED50 nm
= 0.13


I have the table above. I want to remove duplicates in rows ID, STRUC
and MW as long as the ID is the same as the row being deleted (for
instance I do not want to remove the MW of line ID 14 because it is
unique even though it is the same as ID 12). I also want to leave the
ACTT, ACTUOM, ACTPRE and ACTVAL data as it is listed.

My end goal would to be a worksheet with no duplicate ID columns, but
the data listed for ACT columns.

An even better worksheet would take away all the duplications in the
ID, STRUC and MW columns of a specific ID and then add unique ACT
(like ACTT1 ACTUOM1, etc.€¦.) columns into new ACT columns at the end
of the row.

So the logic as I see it is:

IF ID = duplicate
Check specific columns (STRUC, MW) for duplicate values

IF duplicate values = YES
Delete second value

IF duplicate values = YES
Populate ACTT(n) column (This would be an new column at the end of the
row)with ACTT value and delete value.

Etc. for the columns requested.

IF row = blank
Delete row

Please lend me a hand if you can. I am very much a novice at this
type of thing. I attempted to do this via pivot table, but I cant
seem to make that work for this particular problem.

I have, but with little success in this case. Unfortunately, the
transformation on the data is slightly too complex. I may just end up
writing an external Java program to parse the CSV file, but that
starts its own set of problems.

Thanks for the response!