Filter by color option in Alteryx

[Rahul_Analyst]

Hi,

I have an excel file where some columns are colored and I have to filter it out by filter by color option. I want to do it in Alteryx. Is there any trick for doing this in Alteryx?

 I came across two posts on the community by searching its solution. However, I am unable to do it. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Recognizing-cell-colors-in-excel/td-p/...

https://community.alteryx.com/t5/Base-de-Connaissance-Francais/Extraire-la-couleur-des-cellules-d-un...

[gabrielvilella]

You should follow the article that explains the Python method. 

[Ben_H · Accepted solution]

Hi @Rahul_Analyst,

So this can be done, but it's quite a faff to get it to work.

I've made a very crude example below that may well break if you try it with another sheet but it should give you some ideas if you want to persue an Alteryx based method.

If you read an excel file as a .zip you can access all of the different xml that makes up your document. One of these contains style information including which cells are coloured.

Here is my hideous test workbook I made -

As you can see there are various colours across rows, columns or just individual cells.

At first I read in the file as normal, but including the filepath.

The bottom strand just takes the filepath and repurposes it for use in the macro where it will read xml.

The macro takes the xml and identifies from it the cells that are coloured. I've done this in a very quick and dirty fashion so it doesn't tell you what the colour actually is, just that it has a colour and what number excel is using internally to keep track of it. You could get the actual colour but that would have taken some more messing around with the xml which i didn't want to bother with!

The macro output table is given the headers of the input data, then joined on. The additional columns basically tell you that the cells are coloured.

0 is no fill, numbers are given depending on the order in which they are first applied in the workbook (i think) - either way they are consistent.

You end up with this -

So if I wanted the yellow cells in column A I would filter "A_Colour" to 2.

It's a bit of a mess but it might give you a jumping off point to develop something better.

Regards,

Ben

[Rahul_Analyst]

@Ben_H ,

Brilliant, thanks a lot!

[ajanayak]

Hello There,

Many thanks for prompt response, I am new for this tool, Need simple solution,

In the first step itself when I am using input tool for excel data load, all the yellow colored row is coming as normal excel in Alteryx, Not sure how to preserve the colored row while loading the data, Then after we will apply logic for filtering based on colored rows, Find the attachment which I am looking for using it as input data.

Please let me know if you still need further clarity on my requirement.