Validating and filtering excel data based on color

[ajanayak]

Hello Team,

Need help from you for one of my requirement where in I have one excel sheet and I need to get the few items from that based on rows colored in Yellow, It can be red, green as well.

So over all two filter criteria, Based on Inovoice# and color I need to filter those records from sheet and need to move that to output.

NOTE --> Please find the attachment for details.

There can be multiple invoice with same number but I need to filter out the Invoice based on color.

Please let me know if you need any further information. Any help would be appreciated.

Thanks

AJAY

[Hammad_Rashid]

Check this out : https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Filter-by-color-option-in-Alte...

[BrandonB · Accepted solution]

Hi @ajanayak 

I took some inspiration from the post here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Alteryx-Identifying-Highlighte... 

Alteryx can't natively read cell colors from a regular input tool, but what you can do is read data from the underlying XML that exists within the file, identify what rows are highlighted using it, and then join it back to your dataset to see which rows are highlighted in the main data set. Then you can perform whatever analysis you need using that information. 

 

Workflow is attached

Thank you @danilang and @TheOC for doing the heavy lifting

 

[ajanayak]

Many thanks for all your help and support Brandon, It made my day.

[geordie986]

Thanks @BrandonB  : do you know how your solution can be adjusted to detect background color on single cells? I'm trying to understand the xml structure but is not easy to identify the part in which cell-specific colors are defined.

Also, with minimal changes to your approach I was also able to parse and extract comments from Excel, simply changing file type to xml, path to xl/threadedComments/threadedComment1.xml and threadedComment as child node name.

[BrandonB]

@geordie986 I'm not an expert in formatting that exists in XML, but the pattern I recognized was specifically in the c_s column. In particular, when there was a value present I was able to look at the spreadsheet and confirm that the row was in fact highlighted. It was a bit of reverse engineering to figure this out, but that's how I built the formula that identified which rows were highlighted. 

I believe that in my example the c_s value in row 5 in the xml indicated that the first column of the data in the 5th row had a highlighted cell. So if you wanted to do this dynamically you could use a dynamic select tool to pull c_s through c_17 columns, identify which had values based on row/column combo if you needed to identify specific cells that were highlighted. 

[geordie986]

Thank you, will check and update.

Also did some further research in the meantime, apparently the value stored in c_s column is linked to the style.xml, that can be used to derived the actual color.

In my case, I need to the determine the coloring of each individual cell to biforcate the data processing workflow - we are talking 20 rows x 350 columns with multiple colors so will be fun :)