Apply macro to unique seqence of cells colored cells

[capson]

My data looks like this
Capture1.PNG

I need for each unique color sequence to get the unique string from the cells of column E which is made up of the unique segments separated by the pipe "|"

Please Login or Register  to view this content.

The unique string from the cells of column E for HH is

Please Login or Register  to view this content.

I need the data it to look like this
Capture2.PNG

I can get the Unique string from the code below but I can not get how to apply it per uniquely colored sequence

I have uploaded a workbook

Please Login or Register  to view this content.

[jindon]

Try

Please Login or Register  to view this content.

[gbeats101]

looking at the layout, it seems that each color sequence has a specific label (All HH are blue, All AA are red etc) if this is the case then once you have got the strings in the dictionary just loop through the range again and find the first item of that specific label, and place the new string in the column, then continue looping finding all the other labels with the same label and delete them all.this would leave you with a single item of label "AA" for example with the new string...... you may also want to find the phone number too and store that so you can add this with the new string.

[capson]

jindon your code worked beautifully

In my effort to solve my problem I whittled down the data set as a place to start when I posted my question I forgot I was still using this shortened data set

My apologies for this over site

I have two other columns F and G both of which need the same process run on them as column E the "real data" looks like this
Capture4.PNG

I tried tweaking your code but can not get it
I uploaded a new work book

Thanks

Please Login or Register  to view this content.

[jindon]

Not sure about how you want it in col.G though.

Please Login or Register  to view this content.

[capson]

jindon thanks again, I have uploaded a new workbook with what I am looking for (should have done this last time)

[jindon]

Still not sure.
Highlite the difference, iuf this is not how you want it,

Please Login or Register  to view this content.

[capson]

jindon almost there,
the segments that make up the strings for column F & G like the in column "E" need to be unique no segment repeated just the largest string of unique segments, the correct data is there it is just not unique

You have the process perfect for column E

[jindon]

I'll have a look at the file tomorrow, it is late here.

[capson]

Thanks

The data in the columns F & G is correct the segments just need to be made unique

[jindon]

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[capson]

jindon, thank you your code executes beautifully it will save me many hours of tedious labor over the months to come

[jindon]

No problem.