how to combine many spreadsheets into one big one?

[tigerpie]

Hello,
I have about 150 Spreadsheet with 2 or 3 work books inside each one.
1 work book is has our client information, and the other workbook has computer hardware information in it.
The "headers" go down the left hand side of the spread sheet (A1, A2, A3 etc) and the data is in B1, B2, B3 etc.
Some of the "headers" are slightly different.
I was wondering if it is possible to do something so that the "headers" from the columns A1 > A25 in each work book get copied from all of the spreadsheets (excel files) and put at the top of the page, so the headers then become A1 > c1, and the data that was there also gets moved to the new spreadsheet matching up withe the proper header????

Any ideas?

[AndyPS]

If I have understood correctly, this is about automation of copy/paste transpose so that you end up with a list, going down the page, extracted from the source spreadsheets.

I have about 150 Spreadsheet with 2 or 3 work books inside each one.

(Whilst you mention work books, I think the more common description is a workbook (a spreadsheet) having several worksheets (or tabs).)

You say some spreadsheets have 3 sheets but only describe 2. What is on the third? Also, are the headings in identical order in all source spreadsheets?
It would help enormously if you could post a couple of examples (turning any sensitive data to other things but keeping the structure).

[tigerpie]

Sorry for getting the workbook and spreadsheets terms wrong, I haven't ever had much call for doing anything fancy in excel before..

I have attached (or tried to attach) 2 example spreadsheets.
The 3rd worksheet in some of the work books, is not needed.

[tigerpie]

I have also attached a workook with how i would like it to look.
I would have 2 work sheets, one called Customer and One called Hardware.
And then a 3rd worksheet that would show what customer has what system.
I was hoping that I could have some sort of dropdown menu for the 3rd sheet.
I would select the Clients Name, and then the associated client information would populate the relevant fields, and then I would select a HASP Key and the associated hardware info would auto populate.

[AndyPS]

Don't worry about the usage of workbook etc. - I understood - though once you get into VBA there is a need to talk Excel-speak!
I will have a look at these over the next day or so and respond.
Regards.

[AndyPS]

A couple of quick questions - from your examples 1 & 2, it looks like there are 2 layouts to the source workbooks. Is this correct? Are the row headings ALWAYS the same or could there be spelling/spacing differences?
Turning to your Example3, your desired output, I am worried about having the customer and hardware data on different sheets with no common linkage (key) between them other than the row number. If someone made a mistake and deleted a row in one sheet, the records would not match and putting it right would be difficult. Coudn't the data just be in a single row on one sheet? We can always hide columns.

[tigerpie]

Hi Andy,
I was thinking last night that, the headers of the hardware information are slightly different unfortunately.
However the data is generally the same (For instance, the Motherboard information would only ever be 1 of 4 types, the CPU might be 1 of 5 types the graphics cards might be 1 of 3 types)
With the customer information, the row headers would always be the same spacing, spelling etc, but the data is always different.
SO maybe we just need to grab the customer information from all of the works sheets, and when it comes to the hardware I could have some sort of drop down list to select the data from, to save me having tore-type everything.
As for the customer information being on a separate sheet from the hardware, the reasoning for doing this was that the hardware (computer) is built before the customer data is entered, or vice versa and sometimes a computer will come back from one customer and then be sent out to another customer. So i was trying to make it so the information could be swapped around when needed.

Would it be possible to have it so when you are combing the hardware information and the customer information on the 3rd worksheet, you can "lock" the row (with a tick box or something) and then if it needs to be changed you "un-tick" the tick box to allow that row to be deleted or edited?

[AndyPS]

I am sorry but I do not have time to build you a complete IT system! The objective of this forum is to educate and hopefully you can learn and improve you skills with the help of other.
Your question has suffered an awful lot of 'scope creep'. What you are now describing for the computer / customer is a classic in database design - an entity "customer" and an entity "computer" linked by a relator that has time in it. Many of the fields in the "computer" entity are linked to other tables (e.g. motherboard type). Something like MS Access is designed to handle this type of requirement.
Going back to your original question, I will produce a bit of VBA to help you extract the customer data from a lot of workbooks. Once you are happy with that you can extend your workbook.

[JBeaucaire]

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

[AndyPS]

I have now cobbled together a bit of VBA to do the compilation. It is on the attached workbook but run by a couple of buttons.
As the data is in variable positions (one or two sheets, different rows, etc.) the source workbook is opened and seached to identify the data element in Column A. If there is a match, the data in Column B for that row is extracted and written to the output list.
The code first seaches sheet 1 of the book but tries sheet 2 (assuming it exists) if the data element is not found.
This is demonstration code only and does not include error handling. The source workbooks must not be open - if they are, an error will occur. (It is quite easy to allow for the book being open but outside scope!)

You can add search terms and relate these to the desired columns in the output.

Once you have extracted the data, you could extract the sheet and manipulate it further.

Hope this has helped.