T
thoerter
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.
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.