Consolidate data from multiple sheet to one sheet
Hello,
I have attached sample spreadsheet, In the spreadsheet you can find multiple sheets, I need to consolidate all the data in final sheet.
Eg: Sheet1 to sheet5 please do not consider, From sheet KY9374 to sheet KZ1768 we need to consolidate in final sheet.
Header details are common in all the sheets, we need to copy data from sheet KY9374 with header, then sheet KU7991 data without header, then KW8660 data without header etc.,
Here if you see in sheet KY9374 we have only one line item so we can copy that one line item and paste in final sheet and then it should go to sheet KU7991 and it should copy the details(excluding header), here we have eight line items( you can see that in x column) so it should copy the 8 line item and paste in final sheet from 3nd (A3)row.
then it should go to sheet KW8660 and it should copy the details(excluding header), here we have 22 line items, it should copy all the data and it should paste in 11th (A11) row of final sheet
Similarly it should copy all the data from all sheets and paste in final sheet,
Finally the worksheet should carry sheet1 to sheet5 and final sheet the other sheets can be deleted or it can be hidden.
For this I need a macro please help me…
| Attachment | Size |
|---|---|
| sample sheet 06-25.xls | 330.5 KB |
Non-programmatic solution
Suriya,
Here is a non-programmatic solution but it it flexible.
1°) As the content of most of your tabs were already selected, I gave them a name = "A_"&tab.
2°) Then I created a new worksheet named "Parms" containing the definition of each area + the parameters where the areas must be incorporated in the merged worksheet (Area, Source tab, Row of the 1rst line, Col of the 1rst column, Number of rows, Number of Columns, Row Start in Merged WS, Row End in Merged WS). I called this table of paremeters "T_PARMS".
3°) I created the target "merged" worksheet (called "Work") whose
- first column contains a line number
- first row contains the title of the columns used in the source worksheet, named "COL_1", "COL_2",..,"COL_93"
4°) In 'Work'!R2C2, I enter the formula: =IF(RC1>INDEX(T_PARMS;MATCH(VLOOKUP(RC1;INDEX(T_PARMS;;7);1;1);INDEX(T_PARMS;;7);0);8);"--";OFFSET(INDIRECT(INDEX(T_PARMS;MATCH(VLOOKUP(RC1;INDEX(T_PARMS;;7);1;1);INDEX(T_PARMS;;7);0);1));RC1-INDEX(T_PARMS;MATCH(VLOOKUP(RC1;INDEX(T_PARMS;;7);1;1);INDEX(T_PARMS;;7);0);7);COLUMN()-2;1;1))
5°) You need to drag the formula to the right and to the bottom.
Please see the result: https://www.box.com/s/qj4xx8axor905o9cp27q