How can I remove the repeated information in a same cell?
You can see the information were separated by "," but some of them are repeated
Foe example, in the last line, the size description column has "ASST" and "XS" repeated twice.
How can I remove one of them and keep the unique information.
Thank you.
STYLE | Color detail | COLOR_DESC | SIZE_DESC | Receiving Date | Receiver |
GBD2018 | BLACK-TRUE BLACK | BLACK | S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2018-01-02 | Grace |
GBD2018 | BLUE,BLUE-ENAMEL BLUE,BLUE-ETHEAREAL BLUE,BLUE-MALIBU BLUE | BLUE | S6,L10-12,M7/8,XL1416,XS4-5,ASST,S6,L10-12,M7/8,XL1416,XS4-5,ASST,S6,L10-12,M7/8,XL1416,XS4-5,ASST,S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2018-01-02,2018-01-02,2018-01-02 | Grace, Grace |
GBD2018 | GREEN-MINT LEAF | GREEN | S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2018-01-02 | Grace |
GBD2018 | NAVY-NAVY BLAZER | NAVY | S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2018-01-02 | Grace |
GBD2018 | ORANGE-SHOCKING ORG | ORANGE | S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2017-11-17 | Grace |
GBD2018 | RED-POPPY RED | RED | S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2018-01-02 | Grace |
GBD2018 | YELLOW-SAFETY YELLOW | YELLOW | S6,L10-12,M7/8,XL1416,XS4-5,ASST | 2017-11-17 | Grace |
GBD2014 | NAVY-BLUE INDIGO,NAVY-BLUE INDIGO | NAVY | S,L,M,XL,XS,ASST | 2017-12-15 | Hope |
GBD2014 | BLACK-BLACK SOOT,BLACK-BLACK SOOT | BLACK | S,L,M,XL,XS,ASST | 2017-12-15 | Hope |
GBD2014 | GRAY-LIGHT GREY MIX,GRAY-GREY MIX BC15,GRAY-GREY MIX BC15,GRAY-LIGHT GREY MIX | GRAY | S,L,M,XL,XS,S/P,L/G,M/M,XL/TG,XS,ASST,ASST | 2017-12-15 | Hope,Hope |
Solved! Go to Solution.
Hi Thank you for your quick reply .... I thought about this methods, but then realized that I can't use it because it is not just 1 column has the duplicates, there are some other cells in different columns had the duplicates.
I had more than 20 columns had this duplicated problem, I did one by one!
It works!! thank you.
@john_miller9wrote:How many fields are there in your file? 5, 10, 50? If it's just a few, you could just repeat and then join back. Or if it's many, a macro could be built out of the workflow. Here's all of the data you provided, cleaned up.
Hello john_miller9,
Thanks for sharing this workflow. It saved me lots of work today. I also had a lot of fields to clean up, so I used a slightly different method by transposing all values first and then feeding those values to the text to column tool as you did.
Can't post the workflow for some reason. Sorry!
I had a slight variation on the theme that I thought worth sharing. Same basic requirements except that I had rows which needed to be grouped before applying the dedup. Essentially my data looked like
StudyName1 Column1, Column2, Column3
StudyName1 Column1, Column2, Column3
StudyName2 Column1, Column2, Column3
StudyName2 Column1, Column2, Column3
where I want to dedup within the group of StudyName1, 2, 3, etc.
This workflow worked well for me instead of separating column by column
1. For the Multirow tool I used the formula: "IIF([Row-1:Study]!=[Study], [Row-1:ID]+1, [Row-1:ID])" and for this one I created a new field "ID" and did NOT check Group By. This created a new field "ID" which incremented every time the Study name changed.
2. Then I transposed with key fields being the new ID column and the Study column I was using as the key and *all* the fields in my data set
3. Removed IsEmpty Values because I had both blanks and nulls
4. Deduped on all fields at once
5. Transposed back with GroupBy on Study and ID and Column headers of "Name" and Values of "Value"
This approach ended up to be very minimalistic for my task.
Hope it helps.