0

I have 1000 excel workbooks and I have to summarize data in one excel workbook. Each workbook consists of data of one property (id of property, region, market value etc.) In the summary workbook I want to insert in a column the id of property and automatically search across the file of workbooks and insert the value for its region, market value etc.

Thanks

Tsorts
  • 31
  • 3

1 Answers1

0

enter image description hereI am going to first assume that there is some structure to the workbooks you are discussing, for example all market value workbooks are the same but only the data is different. it would also be great if they were in different folders and if they were named in a consistent way.

This is only an example with links to different content that will help solve your problem. Your question is too vague and does not provide me with specific enough information to provide exact solution.

Combining records. https://www.youtube.com/watch?v=rSQwZ1d3b1g&list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK&index=5&t=0s

Merge Records. https://www.youtube.com/watch?v=8F7v6YvnsiY&list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK&index=6

Pivot Table Slicer for summary reports. https://www.youtube.com/watch?v=zgt7SdrYJqg

Your best bet would be to use the power query feature to combine data from workbooks inside a folder From DATA TAB -> Select GET DATA drop down from Get & Transform ribbon -> From File -> From Folder. You should be able to combine all the workbooks in folder If your problem is simple.

If each workbook has multiple pages of info and the pages differ then you will need to create a custom function in power query to handle this issue.

If workbooks are all in one folder then you will need to run power query multiple times per condition and use a filter with power query assuming that the files have some kind of organized naming convention.

Once you have combined files and cleaned up the data you should be able to combine the records.

You will do this using the power query feature merge feature. From DATA TAB -> Select GET DATA drop down from Get & Transform ribbon -> Combine Queries -> Merge.

Once records are all merged you should be able to have your final table and use a pivot table to give you the option to select ID with filters or pivot table slicers so that you can have the summary report by ID going across all the workbooks like you wanted.

If anything is unclear please provide more info so i can help you specifically

moises mera
  • 161
  • 6
  • For example, In one cell the value is “property ID” In the next column the value is 12345 Somewhere else the value is “location” In the next column the value is Berlin. Somewhere else “market value” and in the next column 10000. Workbooks are not exactly the same but always next to the “market value” is the right value. So i would like to search for the string market value and take the value next to it. Workbooks name is PID 12345 or GPID 12345. Each workbook takes its name from the id of property. – Tsorts Jun 29 '20 at 05:46
  • This is great news! So there is some structure related to the workbooks at least. Follow the steps i previously mentioned Selecting the workbooks from folder. Once you have done this click the button -> Transform Data. After this you should see all the workbooks. filter out files you dont want using drop downs and text filters. you can combine records using the Content Column that has two down arrows on it instead of the usual single drop down. You may need to repeat this process multiple times once for PID and once for GPID depending on what files are in what folder. I added pic to answer. – moises mera Jun 29 '20 at 12:18