Combining Worksheets

[John]

I need to combine the results of two worksheets that are in one
workbook for an execption report. I use the filter comand to filter

0 for the exception. Now I need to show two weeks in the report.

Copying and pasting is an option, but if the sort order changes, there
is a problem. There are about 110 items or rows and 6 days in the
report.

I am looking for suggestions on how to create this report.

Sample:

sheet1
item mon tue total
101 10 5 15
102 1 1
103 0
104 0
105 2 2

sheet2
item mon tue total
101 1 2 3
102 1 1
103 0
104 3 3
105 2 2

totalsheet
item mon tue mon tue total
101 10 5 1 2 18
102 1 1 2
103 0
104 3 3
105 2 2 4

 

[Pete_UK]

Have you got Monday-Saturday in both sheets? Do you have any items in
Sheet1 that are not in Sheet2, and vice-versa?

Pete

 

[John]

Have you got Monday-Saturday in both sheets? Do you have any items in
Sheet1 that are not in Sheet2, and vice-versa?

Pete







- Show quoted text -

All the sheets will be the same.

Thanks

 

[Pete_UK]

First of all, then, make a copy of Sheet1 in the same workbook - just
do CTRL-drag of the sheet tab, and rename it totalsheet. I think
column H is where you want the second week's figures to begin if you
have 6 days in each sheet, so copy the headings from B1:H1 into H1
onwards (thus copying the heading for "total" as well).

In H2 of totalsheet you can put this formula:

=IF(VLOOKUP($A2,Sheet2!$A$2:$G$110,COLUMN(B$1),
0)="","",VLOOKUP($A2,Sheet2!$A$2:$G$110,COLUMN(B$1),0))

This will give you a blank cell if you have a blank cell in the
corresponding position in Sheet2. Then you can copy this formula
across into I2:M2. In N2 you need to add the formula:

=SUM(B2:M2)

Now highlight the cells H2:N2 and copy these down to row 110.

Hope this helps.

Pete

 

[John]

First of all, then, make a copy of Sheet1 in the same workbook - just
do CTRL-drag of the sheet tab, and rename it totalsheet. I think
column H is where you want the second week's figures to begin if you
have 6 days in each sheet, so copy the headings from B1:H1 into H1
onwards (thus copying the heading for "total" as well).

In H2 of totalsheet you can put this formula:

=IF(VLOOKUP($A2,Sheet2!$A$2:$G$110,COLUMN(B$1),
0)="","",VLOOKUP($A2,Sheet2!$A$2:$G$110,COLUMN(B$1),0))

This will give you a blank cell if you have a blank cell in the
corresponding position in Sheet2. Then you can copy this formula
across into I2:M2. In N2 you need to add the formula:

=SUM(B2:M2)

Now highlight the cells H2:N2 and copy these down to row 110.

Hope this helps.

Pete






- Show quoted text -

It works Pete,

Thanks

 

[Pete_UK]

You're welcome, John - thanks for feeding back.

Pete