Dynamically choosing Filter Column using another Input

[NidhiAg]

Hi Team,

I am building a workflow which read bunch of different excels. Now in each excel I have a different Column name to filter on. I am trying to make a macro (as of now just doing using single file). I have build below structure with help of this community. I have something using listbox but I dont want user interaction to choose. I am somewhat confused here. Can I get some help

[apathetichell]

where does your list of columns to filter on come from?

[NidhiAg]

I will have in excel based. Excel schema is

FileName  FilePath  FilterColumn FIlterValue

So when I read that filepath. I need to Filter "FIlterColumn" with "FIlterValue"

[apathetichell]

Doable. There are a few ways that I'm seeing it and it really depends upon the complexity of your data source. In the simplest form you'd pull in all of your excel documents and pivot with filename/filepath as key columns. You can then join it to your filter table with filename as a join code filter column joined to [name] and [filtervalue] joined the [value]... the resulting entries will be those that match the filter column/filtervalue in the filename.

It can get more complicated depending upon schema and the like... but honestly without more info it's a little hard. If your listbox is working and the column name is always in the listbox and you got the magical in ("[yyy]") to work where "yyy" is your listbox name. you can also use dynamic select and set it to [filename] or [name] in .... but note - I don't think this listbox strategy helps you if you have multiple columns with the same name and only some are being selected. In that case you'd need to do the [name]/[value]/[filename] as described above or some batch macro thingy (honestly what I'd probably do) where your control parameter 1) is your filename and your control parameter 2) is your column name and your control parameter 3) is your filter value. Can you have 3 control parameters? Sure. I do it all the time. It's fun.

[NidhiAg]

Hi Aurora,

So I have attached workflow where I am getting data from one Macro input and Column name and Value to apply filter on data from another Macro input. Can you help me fixing this . 

[apathetichell]

Hi @NidhiAg I'm sorry - I'm not getting this workflow and I'm not quite getting why you are dropping the filter value which would seem like the critical component in the select tool. With the data that you have coming in I would transpose the lower macro input with filename as key column and do a group by name vs column and value vs value. This assumes that you have no issues with bringing your files into the macro.