Delete columns based on color

[Alex]

Hi there,

It's possible to delete a column if a particular color (RGB(153, 204, 255) or 16764057) isn't present in any of its row?

Thank you

[patrick_mcauliffe]

@Alex  I'm assuming you're talking about an Excel data input? 

If so, the answer is yes.  But its pretty complex, so let me know if I'm on the right track before I continue.

[Alex]

Hi @Patrick

Yes, indeed.

The data comes with this blue color in some cells, and if a column doesn't have any row with blue it should be deleted.

Thank you

[HomesickSurfer]

Hi @patrick_mcauliffe 

Subscribed to this post to see any probable solutions as I too am interested in filtering by color.

[AZuc]

Challenge accepted!

Supposing we are talking about xlsx (not xls), it's actually a zip file full of xml's we can extract some data.

You can see some information about xlsx structure here: https://professor-excel.com/xml-zip-excel-file-structure/

Particularly speaking about colors (background color, to be specific), I've done some findings:

Opening xlsx as zip, we'll find sheet data on xl/worksheets/sheet1.xml (supposing your sheet name is sheet1). Inside this xml, we'll find a list of rows and the detail of each one (cells) in a structure like this:

In red we'll see row and cell reference and in yellow a reference to the color (s refers to shade) which is in other xml.

(although the subject is very interesting and full of details, I'll stay focused in the ones needed to address the question)

The xml that contains details about each color is xl/styles.xml

There are 2 structures we need to read from that.

The first one is this

This is an array (starting in 0) where each position is related to the number referred by shade in the sheet xml. Ex: s="1" means the second row of this array, the one with fillId="2".

Now, the structure that defines the "fillId", and the color, is this one:

In yellow we'll see the RGB for each color.

Based on this concepts, I've built a sample workflow that shows which color is in each cell of a simple Excel Spreadsheet. Surely it can be evolved to a more flexible macro, but I believe the information so far is good enough to help in the problem.

[patrick_mcauliffe]

Hey @HomesickSurfer , yeah, sorry I've been busy with work lately and was hoping to get a full write up posted.  

Basically, you'd be unzipping the Excel file and looking in the style sheets for the color code, much like this:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Changing-File-Name-without-changing-fo...

[Pang_Hee_Choy]

if you can identify the rules, you can redo it in Alteryx

[HomesickSurfer]

Hi @AZuc 

Thank you.  This is impressive!  I've reviewed your attached workflow - works wonderfully!

I appreciate you for taking time to provide the sample flow and detailed explanation.

[HomesickSurfer]

Hi @patrick_mcauliffe 

Thank you.  I will review the link you've provided.  I appreciate your response.

[HomesickSurfer]

Thank you @AZuc and @patrick_mcauliffe 

I have successfully been able to sum values based on color.

Example below and attached.