Using File name (ie. Sheet), Row and column position to spread Value columns in a table

[lavorarchi]

Hi I am working on a flow where I am taking data from individuals sheets of an Excel file and then trying to "re-construct" a table with a selective number of columns from each sheet based on a particular column header available within each sheet. So far I have got as far as this:

FileName	RowNum	ColumnNum	Value
C:\Users\...xlsx|||Sheet1	1	4	Sub 1
C:\Users\...xlsx|||Sheet1	2	4	GBP
C:\Users\...xlsx|||Sheet1	4	2	Sales
C:\Users\...xlsx|||Sheet1	4	4	10000

I now need to stack each sheet on top of another so it looks like this:

	Sheet1	Sub 1
	Sheet1	GBP
Sales	Sheet1	10000

The position and label for Sub 1 is likely to be different in other sheets. So although it is titled Sub 1 in this scenario, in another file it might be called Sub 2.

Thank you all in advance.

[messi007]

@lavorarchi,

Could you share a workflow with a sample data in order 🙂

It will help to provide a solution.

Regards,

[lavorarchi]

Hi @messi007 thank you. Please find attached sample data and flow as required. 

[OllieClarke · Accepted solution]

Hey @lavorarchi 

I'm not sure I fully understood your problem, but you can use a cross-tab tool to reconstruct a table using the column number and row number to place your values

If you rename these fields with a dynamic rename tool taking field names from first row of data, then you end up with this: 

If you have multiple sheets though, then it might be better to not rename the columns:

Hope that helps,

Ollie

[OllieClarke · Accepted solution]

Hi @lavorarchi 

Using your data a batch macro is actually the best approach

the batch macro looks like this:

The formula tool updating the recordID might need a bit of tweaking if there are more sheets with a different structure, but it worked well for the data you provided.

Hope that helps, Ollie

[lavorarchi]

Thank you ever so much. I am just trying to make sense of your two approaches. But looking good. Much appreciated.