how to pick columns based on a sample file and picking it from different excel files ?

[Binu456m]

i have multiple excel files which has many sheets inside it. So i need to get an output of certain fields based on the sample file attached. The excel formats are same for all input files (the input files can be two files or hundreds of files). i have attached the input file format and sample Output data i need to extract from the files i upload. if anyone of you can help me, would be appreciated 

[caltang]

You'll need to group and cleanse the values to get those values into a proper data frame like your output set.

This is a complete build and will take some effort. Who are you building this for?

[Binu456m]

This is building for my project. Since i have 100+ files as input file, it is impossible to cleanse the data and run right? all 100 files has same format data. so is there anyway to get the output?

[caltang]

Can you provide three sample files? Just to see if the dataframe is consistent amongst all the files.

In theory it is doable, does take some time.

[Binu456m]

attached two input files for your reference. the expecting output file format, i have attached earlier. any help would be appreciated

[caltang]

@Binu456m your two attached files have problems. I can't open them. Can you check?

[caltang]

Also, from your sample file:

You hid some rows - and they're still referencing your SharePoint man.... I can't use these files. 

[Binu456m]

I just need data from the highlighted columns and other can be ignored... i need column name and column value based on each file (all file has same template)

[Binu456m]

see attached for your reference  

[cjaneczko]

Start by using a directory tool to grab all the files. Then you can use a union tool and have it "Auto Config by Name" and "Output Common Subset of Fields". Use the Template file as the first join, then join the directory tool to it.