Combine multiple sheets from one excel file to make a report

Question

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-

insendma153b562c321

Can anyone help me with this? Thanks!

Book1.xlsx

abacon · Accepted Answer

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 Answer

@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.