Combine multiple sheets from one excel file to make a report

[skmarsh]

Hi! I have an excel file with 3 sheets, having different no. of columns and different column names except 'e/n' , 'dm' and 'ins'. I want to combine these sheets and get only these three columns after combining. I want to then make a report, grouped on ins, with counts for e, n and dm

The output should look like this-

ins	e	n	dm
a	1	5	3
b	5	6	2
c	3	2	1

Can anyone help me with this? Thanks!

[abacon · Accepted solution]

Hey @skmarsh 

Yes that is possible and not too difficult. I have attached a link to a macro I built that gets all data out of excel files and sheets from a given directory. Use this macro, then simply remove the columns you don’t need with a select tool and use the summarize tool to group on the desired column and count the necessary fields.

Bacon

https://community.alteryx.com/t5/Community-Gallery/Dynamic-XLSX-Input-Tool/ta-p/1393832

[Qiu · Accepted solution]

@abacon 
Nice one.
i offen refer to this macro for importing multiple sheets in from one Excel file, but yours can do that for a folder.

@skmarsh 
Quick sample with the macro above.

[skmarsh]

It's working! Thank you so much @abacon and @Qiu !!

[Qiu]

Thanks to the macro of @abacon 😁