|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 22nd, 2009, 09:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotals - is there a way to include more information on the line
I am using Excel 2007. I need to sort by job no and client no. Some clients
have more than one no. Here is an unsorted list: Mgr Client No Client Job No Inv # Inv Total Carol 455 Bannon 4123 1006466 28,754.30 Bob 455 Bannon 4123 1006487 4,410.00 Bob 455 Bannon 4123 1006640 4,410.00 Carol 477 Dove 1245 1269841 13,971.80 Sandy 477 Dove 4587 1258002 3,492.95 Paul 494 Bannon 6546 1006487 6,363.17 Carol 494 Bannon 6544 1006390 931.45 Paul 494 Bannon 6545 1006466 6,520.17 Paul 839 Carolina 6789 1006713 6,985.90 And here it is sorted. . Mgr Client No Client Job No Inv # Inv Total Carol 455 Bannon 4123 1006466 28,754.30 Bob 455 Bannon 4123 1006487 4,410.00 Bob 455 Bannon 4123 1006640 4,410.00 4123 Total 37,574.30 455 Total 37,574.30 Carol 477 Dove 1245 1269841 13,971.80 1245 Total 13,971.80 Sandy 477 Dove 4587 1258002 3,492.95 4587 Total 3,492.95 477 Total 17,464.75 Paul 494 Bannon 6546 1006487 6,363.17 6546 Total 6,363.17 Carol 494 Bannon 6544 1006390 931.45 6544 Total 931.45 Paul 494 Bannon 6545 1006466 6,520.17 6545 Total 6,520.17 494 Total 13,814.80 Paul 839 Carolina 6789 1006713 6,985.90 6789 Total 6,985.90 839 Total 6,985.90 Grand Total 75,839.75 Is there a way that I can have the client name show next to the subtotal automatically? The spreadsheet is actually much larger than shown, and entering the names manually is very time-consuming. Any solutions would be appreciated. Thank you. 
|
|
#2
June 23rd, 2009, 01:46 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotals - is there a way to include more information on the line
You may want to look at building a Pivot Table.
From the view of your sorted table I think this will work for you. If your not familiar with them press F1 and enter "pivot table" in the seach box. Best regards, Dave "Acanesfan" wrote in message ... I am using Excel 2007. I need to sort by job no and client no. Some clients have more than one no. Here is an unsorted list: Mgr Client No Client Job No Inv # Inv Total Carol 455 Bannon 4123 1006466 28,754.30 Bob 455 Bannon 4123 1006487 4,410.00 Bob 455 Bannon 4123 1006640 4,410.00 Carol 477 Dove 1245 1269841 13,971.80 Sandy 477 Dove 4587 1258002 3,492.95 Paul 494 Bannon 6546 1006487 6,363.17 Carol 494 Bannon 6544 1006390 931.45 Paul 494 Bannon 6545 1006466 6,520.17 Paul 839 Carolina 6789 1006713 6,985.90 And here it is sorted. . Mgr Client No Client Job No Inv # Inv Total Carol 455 Bannon 4123 1006466 28,754.30 Bob 455 Bannon 4123 1006487 4,410.00 Bob 455 Bannon 4123 1006640 4,410.00 4123 Total 37,574.30 455 Total 37,574.30 Carol 477 Dove 1245 1269841 13,971.80 1245 Total 13,971.80 Sandy 477 Dove 4587 1258002 3,492.95 4587 Total 3,492.95 477 Total 17,464.75 Paul 494 Bannon 6546 1006487 6,363.17 6546 Total 6,363.17 Carol 494 Bannon 6544 1006390 931.45 6544 Total 931.45 Paul 494 Bannon 6545 1006466 6,520.17 6545 Total 6,520.17 494 Total 13,814.80 Paul 839 Carolina 6789 1006713 6,985.90 6789 Total 6,985.90 839 Total 6,985.90 Grand Total 75,839.75 Is there a way that I can have the client name show next to the subtotal automatically? The spreadsheet is actually much larger than shown, and entering the names manually is very time-consuming. Any solutions would be appreciated. Thank you.
|
|
#3
June 23rd, 2009, 12:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotals - is there a way to include more information on the line
Another way is to cheat.
Insert a new column and concatenate the fields you need into that new column: =b2&"--"&d2 or maybe: =text(b2,"000")&"--"&text(d2,"0000") The hyphens are to make it easier to read. The =text() portion is needed if those client numbers are plain old numbers with a format (to keep leading 0's). Acanesfan wrote: I am using Excel 2007. I need to sort by job no and client no. Some clients have more than one no. Here is an unsorted list: Mgr Client No Client Job No Inv # Inv Total Carol 455 Bannon 4123 1006466 28,754.30 Bob 455 Bannon 4123 1006487 4,410.00 Bob 455 Bannon 4123 1006640 4,410.00 Carol 477 Dove 1245 1269841 13,971.80 Sandy 477 Dove 4587 1258002 3,492.95 Paul 494 Bannon 6546 1006487 6,363.17 Carol 494 Bannon 6544 1006390 931.45 Paul 494 Bannon 6545 1006466 6,520.17 Paul 839 Carolina 6789 1006713 6,985.90 And here it is sorted. . Mgr Client No Client Job No Inv # Inv Total Carol 455 Bannon 4123 1006466 28,754.30 Bob 455 Bannon 4123 1006487 4,410.00 Bob 455 Bannon 4123 1006640 4,410.00 4123 Total 37,574.30 455 Total 37,574.30 Carol 477 Dove 1245 1269841 13,971.80 1245 Total 13,971.80 Sandy 477 Dove 4587 1258002 3,492.95 4587 Total 3,492.95 477 Total 17,464.75 Paul 494 Bannon 6546 1006487 6,363.17 6546 Total 6,363.17 Carol 494 Bannon 6544 1006390 931.45 6544 Total 931.45 Paul 494 Bannon 6545 1006466 6,520.17 6545 Total 6,520.17 494 Total 13,814.80 Paul 839 Carolina 6789 1006713 6,985.90 6789 Total 6,985.90 839 Total 6,985.90 Grand Total 75,839.75 Is there a way that I can have the client name show next to the subtotal automatically? The spreadsheet is actually much larger than shown, and entering the names manually is very time-consuming. Any solutions would be appreciated. Thank you. -- Dave Peterson
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
