Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Delete columns based on color

Alex
5 - Atom

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

9 REPLIES 9
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@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
5 - Atom

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
12 - Quasar

Hi @patrick_mcauliffe 

 

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

AZuc
Alteryx
Alteryx

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:

AZuc_0-1635895524815.png

 

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

AZuc_1-1635895871913.png

 

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:

AZuc_2-1635896143258.png

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.

 

 

 

 

 

 

 

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


patrick_mcauliffe
14 - Magnetar
14 - Magnetar

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
9 - Comet

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

HomesickSurfer
12 - Quasar

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
12 - Quasar

Hi @patrick_mcauliffe 

 

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

HomesickSurfer
12 - Quasar

Thank you @AZuc and @patrick_mcauliffe 

 

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

Example below and attached.

 

Capture2.PNG

Labels