LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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.



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
is it possible for excel to take several cells in random order and confused in Iowa Excel Discussion (Misc queries) 1 January 7th 08 03:17 AM
How can I sort a selection of cells into random order? IanS Excel Discussion (Misc queries) 2 June 14th 07 12:31 PM
Tough Concatenate Problem BCBC Excel Worksheet Functions 3 February 16th 06 10:13 AM
A tough problem Patrick Simonds Excel Discussion (Misc queries) 3 July 30th 05 07:26 PM
Tough problem with rotas Scott Cheesman Excel Discussion (Misc queries) 0 April 2nd 05 10:51 AM


All times are GMT +1. The time now is 12:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"