Tough problem adding cells from different sheets, in random or
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.
|