Conditional Formatting using tables to identify multiple criteria in each row of data

[Scarbrain]

Hello,

I need an output that will identify the values as either entered by operations, entered by sales, or actuals validation.

The expected output is in the excel along with the input.

Do note, there will be over 500 lines of opportunities

Open to alternate ideas and solutions.

Cheers

[FinnCharlton]

Hey @Scarbrain , this was actually a really good challenge:

You can do this by finding the date of 'Actual Billings' for each ID, and joining it back onto the data after you get it into the table structure. You can then reference that field in column rules in the Table tool and compare it to the Field Names to get the colours correct:

Workflow attached for you to check out, hope it helps!

[Scarbrain]

Thanks @FinnCharlton  for the quick reply

Mate this is excellent, I added a few more rows and appears where the are more than 1 "Actual Billings" it brings out duplicate rows, could we get it into one row?

See updated input

[FinnCharlton · Accepted solution]

@Scarbrain Had to rejig the Table tool rules a bit - this should do it:

[Scarbrain]

The cross tab next to summary what method for aggregating values did you select? Perhaps I need the latest version as I can only select first or last...

But I can see what you have done here, brilliant.

thanks

[FinnCharlton · Accepted solution]

@Scarbrain This might be because your data types are different to mine, try using a Select tool to make the date a string before concatenating them.

[Scarbrain]

@FinnCharlton 

Would you mind looking at the complete data I have, once I use all the data some fields have no colour, and I cannot work out the formula you used in the table

Also when I change the date to MMM-YY then less fields are coloured, so I reverted back to YYYY-MM-DD

[FinnCharlton]

@Scarbrain , can you give an example of where it isn't working? When I run it, everywhere a cell has a value, it has one of the three colours:

[Scarbrain]

@FinnCharlton I see now, appears my output moves the colour one column to the left for every single opportunity...is that a bug you think, or perhaps I just need the latest version?