Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A puzzling duplicate data issue...
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A puzzling duplicate data issue...
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A puzzling duplicate data issue...
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Puzzling attribute | Excel Discussion (Misc queries) | |||
duplicate rank issue | Excel Discussion (Misc queries) | |||
Duplicate value causes VLOOKUP issue... | Excel Worksheet Functions | |||
Sum help this is puzzling??????????? | Excel Worksheet Functions | |||
Puzzling Format Questions | Excel Discussion (Misc queries) |