Table Tool - Background Color per Existing/New Email Address

[Mert]

Hi. I have a piece of data which updates weekly. One of the fields holds email data which does not have to be unique.

I am reporting the result using Table Tool. Let's say I start with 3 rows on the 1st week:

ID	Email	Week
1	a@b.com	1
2	c@d.com	1
3	a@b.com	1

I would like Table Tool (or another tool) to assign background colour per row per email address: Yellow, Red, Yellow

On the 2nd week, my data get enriched:

ID	Email	Week
1	a@b.com	1
2	c@d.com	1
3	a@b.com	1
4	c@d.com	2
5	e@f.com	2

I would like Table Tool (or another tool) to keep assigning background colour per row per email address: Yellow, Red, Yellow, Red, Blue

There is no pre determined colour for any email address. We don't know how many different email address we will end up with as weeks go by. However we are sure that total number of different email addresses will not be more than 30 or 35. 

Is this any possible? TIA!

[Clifford_Coon]

Hi @Mert ,

A thought and a Question.

You could extract a unique list of names after each enrichment process and assign the same color to those that already had a color, then assign a new color to each new email. (You would need to have a "bank" of colors to be assigned)

Q: do the colors need to remain the same once they have been used - the above assumes that is the case

[Mert]

Thank you @Clifford_Coon for sharing your thoughts. It is worth it to give it a try.

A: I think the colors need to remain the same once they are assigned to certain email addresses on week 1 onwards.

[Clifford_Coon · Accepted solution]

Hi @Mert ,

Here is a working prototype.  The Container could/should be a macro.

 

[danilang · Accepted solution]

Hi @Mert 

Here's a way you can do it

Start with a blank colour mapping file populated with 35 colours.  I'm using named colours here, but you can use RGB values if you modify the column rule in the Table tool

This gets joined to the unique email addresses in the input data.  The unmatched ones get joined by record position to the remaining colours to assign a new colour to each new email.  The existing and new ones are joined to the input data to add a colour field for each record.  These are also unioned with the unmatched colours and written back out to the colour mapping file to use next week.

The table tool is configured to use the Colour field in a formula to set the text colour for each row 

Here are the results of sequentially adding new rows each week.  

Week 1

Week 2

Week 3

Good luck coming up with 35 easily distinguishable colours

Dan