• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamically combining rows from different sheets into 1 sheet

amollogs7

New Member
I have data of diff regions: east, west, north & south

The data is in 4 diff sheets named: east, west, north & south.


The column heads are identical & formatting as well.


I want to consolidate the data from these 4 sheets into a fifth sheet of the workbook named:" Overall"


The "Overall" sheet should have the consolidated data in the sequence:

1. West

2. East

3. North

4. South


But all this should happen dynamically,

i.e. as & when i add a row of data to East sheet the consolidated data should automatically show added entry in a new row inserted below last row of east data.

Also the serial no. should adjust automatically sequentially


E.g.:


Sheet West:

1. CompanyA Rose 12cartons 6pcs

2. CompanyB Lotus 15cartons 9pcs

3. CompanyC Sunflower 18cartons 5pcs


Sheet East:

1. CompanyP Orange 72cartons 3pcs

2. CompanyQ Mango 55cartons 4pcs

3. CompanyR Apple 48cartons 1pcs

4. CompanyS Orange 89cartons 9pcs


Sheet North:

1. CompanyJ Shirt 20cartons 4pcs

2. Companyk trouser 95cartons 3pcs


Sheet South:

1. Company M Toy 56cartons 5pcs


Sheet consolidated:

1. CompanyA Rose 12cartons 6pcs

2. CompanyB Lotus 15cartons 9pcs

3. CompanyC Sunflower 18cartons 5pcs

4. CompanyP Orange 72cartons 3pcs

5. CompanyQ Mango 55cartons 4pcs

6. CompanyR Apple 48cartons 1pcs

7. CompanyS Orange 89cartons 9pcs

8. CompanyJ Shirt 20cartons 4pcs

9. Companyk trouser 95cartons 3pcs

10. Company M Toy 56cartons 5pcs


Observe that the serial no. is 1 to 10 & not 1,2,3,1,2,3,4,1,2,1


Kindly help.
 

Hui

Excel Ninja
Staff member
amollogs7

This is going to be interesting to explain:


On a new sheet "Total", Put a heading Row:

Code:
Serial	Company	Flower	No	Size


Make the following named ranges:

[pre][code]East	=OFFSET(East!$A$1,1,0,COUNTA(East!$A:$A)-1,COUNTA(East!$1:$1))
NOC	=COUNTA(OFFSET(Total!$A$1,0,0,1,COUNTA(Total!$1:$1)))
North	=OFFSET(North!$A$1,1,0,COUNTA(North!$A:$A)-1,COUNTA(North!$1:$1))
South	=OFFSET(South!$A$1,1,0,COUNTA(South!$A:$A)-1,COUNTA(South!$1:$1))
Total	=COUNTA(East,West,North,South)/NOC
West	=OFFSET(West!$A$1,1,0,COUNTA(West!$A:$A)-1,COUNTA(West!$1:$1))
Now on the Total Page

A2: =IF(ROW()>COUNTA(East,West,North,South)/NOC+1,"",ROW()-1)
B2: =IF(ROW()>Total+1,"",IF(ROW()-ROW($A$1)<=(COUNTA(West)/NOC),INDEX(West,ROW()-1,COLUMN()),IF(ROW()-ROW($A$1)-(COUNTA(West)/NOC)<=(COUNTA(East)/NOC),INDEX(East,ROW()-(COUNTA(West)/NOC)-1,COLUMN()),IF(ROW()-ROW($A$1)-(COUNTA(East,West)/NOC)<=(COUNTA(North)/NOC),INDEX(North,ROW()-(COUNTA(East,West)/NOC)-1,COLUMN()),INDEX(South,ROW()-COUNTA(East,West,North)/NOC-1,COLUMN())))))[/code][/pre]
Copy A2 down

Copy B2 Across and Down


Voila


Be careful that the " are correct, retype them if you get errors


Have a look at the above in action at:

http://rapidshare.com/files/428766855/Consolidate.xlsx
 

jbcheshire

New Member
I looked at your template and the "total" sheet does not have the companies consolidated in the sequence he gave with West being first as he asked. They are actually sorted strictly by the worksheet order for the companies...
 

Hui

Excel Ninja
Staff member
@JB

Thanx for that pick up


I have adjusted the post above and the uploaded a revised link to a new file


Never heard from Amollogs and so don't know if it was any use or not. I hope it is of some use to you
 
Top