Tough problem adding cells from different sheets, in random order

[Darcy]

Sheet 1 Sheet 2
Sheet 3

a b a b
a b
12 190 10 220
34 340
34 180 23 190
10 310
10 90 34 160
12 280
4 60 12 90
23 190
11 80
11 80

4 60

What I'm looking for is the result in sheet 3, I need column b to be the sum
of column b in sheets 1 and 2 based on the number in column a being the same,
then sorting based on column b, and obviously if the number in column a does
not occur twice then it is still added in sheet 3 with its original column b
value.
Tough to explain, but if I look at the number 12 in sheet 1 column a, I look
for another number 12 in sheet 2 column a, if there is one there, then I add
the two corresponding values in column b, and put the result in sheet 3, and
sort.

Anyone have some ideas, Please help.

Thanks

Darcy

 

[Darcy]

Sorry looks like my columns got screwed up, Hopefully these are easier to
understand.

Sheet 1 Sheet 2

a b a b
12 190 10 220
34 180 23 190
10 90 34 160
4 60 12 90
11 80
Sheet 3

a b
34 340
10 310
12 280
23 190
11 80
4 60

 

[WilD]

Hi. Try this.
1. Put Sheet1 and Sheet2 together and ad a column displaying sheet origin.
Then ad Title Columns to each column. For example:
ColumnA ColumnB Origin
12 190 Sheet1
34 180 Sheet1
10 90 Sheet1
4 60 Sheet1
11 80 Sheet1
10 220 Sheet2
23 190 Sheet2
34 160 Sheet2
12 90 Sheet2

2. Select this table including Titles (A1 to C10). Then press [Alt+D+P] to
enter PivotTable Wizard.
Then Click [Next]. (Step 1-3)
Then Click [Next] again. (Step 2-3)
Then Click [Finish]. (Step 3-3)
This will create a new Sheet and will show you the "PivotTable Field List"
Window.

3. In "PivotTable Field List"
Right-Click ColumnA and Select "Add to Row Labels".
Then Right-Click ColumnB and Select "Add To Values".
You should now see the report you were looking for.

4. To order ColumnA descending, just Click on any value in column a and
Choose from menu Data, "Sort Largest to Smallest" Button (ZA!).

There are other possibilities to solve this task but I think this is the
easiest one.

 

[Darcy]

Thanks Wild, I will try that and let you know, but why do I need to add a
column for origin?

Darcy

Hi. Try this.
1. Put Sheet1 and Sheet2 together and ad a column displaying sheet origin.
Then ad Title Columns to each column. For example:
ColumnA ColumnB Origin
12 190 Sheet1
34 180 Sheet1
10 90 Sheet1
4 60 Sheet1
11 80 Sheet1
10 220 Sheet2
23 190 Sheet2
34 160 Sheet2
12 90 Sheet2

2. Select this table including Titles (A1 to C10). Then press [Alt+D+P] to
enter PivotTable Wizard.
Then Click [Next]. (Step 1-3)
Then Click [Next] again. (Step 2-3)
Then Click [Finish]. (Step 3-3)
This will create a new Sheet and will show you the "PivotTable Field List"
Window.

3. In "PivotTable Field List"
Right-Click ColumnA and Select "Add to Row Labels".
Then Right-Click ColumnB and Select "Add To Values".
You should now see the report you were looking for.

4. To order ColumnA descending, just Click on any value in column a and
Choose from menu Data, "Sort Largest to Smallest" Button (ZA!).

There are other possibilities to solve this task but I think this is the
easiest one.

Sorry looks like my columns got screwed up, Hopefully these are easier to
understand.

Sheet 1 Sheet 2

a b
34 340
10 310
12 280
23 190
11 80
4 60

 

[WilD]

Well, this is to keep Sheet1 and Sheet2 data separated, identified, so you
could do further analysis on data knowing where each value came from.

Another way to do this is using "SubTotal" function in Data menu.

1. Put Sheet1 and Sheet2 data together and assign Column Title to each
column (this is important).
ColumnA ColumnB
12 190
34 180
10 90
4 60
11 80
10 220
23 190
34 160
12 90

2. Sort this range based on ColumnA. This is important because Excel will
calculate SubTotal based on every change detected in ColumnA.
ColumnA ColumnB
34 180
34 160 (Here, Excel will insert a row with
sum of "34" value)
23 190 (Here, Excel will insert a row with
sum of "23" value)
12 190
12 90 (sum of "12" value)
11 80 (sum of "11" value)
10 90
10 220 etc
4 60

3. Select the whole data, including Column Titles and go to Data menu then
select SubTotal button. In the "Subtotal window", select as follow:
At each change in: ColumnA
Use function: Sum (See other options available)
Add subtotal to: ColumnB
Then click [OK]

4. Excel will group rows in 3 levels as shown at left of the screen. To
see just the SubTotals, click on the little rectangle named "2" at the top of
this new bar.
For more information about how to manage this group of rows, look up for
"Outline a list of data in a worksheet" in Excel help.