Positioning Subtotals to the next column on right

[1cyril1]

Hi, I've searched through 4 pages of "Subtotal" posts in this forum but didn't see one that covers what I need. I just want to have Subtotal values appear to the right of the column being sub-totalled rather than directly below each group of numbers. The col to the right would be created just to hold the subtotal values and facilitate filtering on the subtotal lines only. Is this easy to do?

(I've seen somewhat convoluted suggestions elsewhere that involve showing visible cells but I gave up - hoping there's a simpler approach!)

All suggestions most welcome - thanks!

Cyril

 

[JoeMo]

Using VBA, what you want could be done with a button click. If you are willing to use VBA, post a small sample of your starting data/layout, and indicate which columns you want to subtotal and what subtotal function you want to use. The link below provides several options for producing a layout in your post.
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[1cyril1]

Many thanks JoeMo, I'm open to all suggestions! The link you posted doesn't seem to be the correct one?

I basically want to subtotal by value (Col-R below) whenever there's a change in PO No. (Col-F) and have it appear in a new Col-S instead of under the sub-total in Col-R. I'm using Win10 and Office 2016. Unfortunately, our security masters in the workplace won't allow me to install any software so this is the best I can do to provide an idea of what my data looks like (some cols hidden or excluded from this extract - it has many cols!)

[TABLE="width: 624"]
<tbody>[TR]
[TD]COL-F
[/TD]
[TD]COL-G
[/TD]
[TD]COL-J
[/TD]
[TD]COL-K
[/TD]
[TD]COL-M
[/TD]
[TD]COL-P
[/TD]
[TD]COL-R
[/TD]
[/TR]
[TR]
[TD]PO Number
[/TD]
[TD]Created by
[/TD]
[TD]Material group
[/TD]
[TD]PO Description
[/TD]
[TD]Contract Number
[/TD]
[TD]PO Local Currency
[/TD]
[TD]PO Value (Euro)
[/TD]
[/TR]
[TR]
[TD]3500120173
[/TD]
[TD]xx
[/TD]
[TD]IT_T-OPS
[/TD]
[TD]Description-1
[/TD]
[TD]4600012081
[/TD]
[TD]EUR
[/TD]
[TD]4,447
[/TD]
[/TR]
[TR]
[TD]3500120194
[/TD]
[TD]xx1
[/TD]
[TD]VEM_EPART
[/TD]
[TD]Description-2
[/TD]
[TD]4600015833
[/TD]
[TD]EUR
[/TD]
[TD]3,383
[/TD]
[/TR]
[TR]
[TD]3500120198
[/TD]
[TD]xx2
[/TD]
[TD]CCE_SS-BL
[/TD]
[TD]Description-3A
[/TD]
[TD]4600018480
[/TD]
[TD]EUR
[/TD]
[TD]7,030
[/TD]
[/TR]
[TR]
[TD]3500120199
[/TD]
[TD]xx3
[/TD]
[TD]CCE_SS-BL
[/TD]
[TD]Description-3B
[/TD]
[TD]4600018480
[/TD]
[TD]EUR
[/TD]
[TD]4,500
[/TD]
[/TR]
[TR]
[TD]3500120202
[/TD]
[TD]xx4
[/TD]
[TD]FAC_SCAPE
[/TD]
[TD]Description-4
[/TD]
[TD]4600012534
[/TD]
[TD]EUR
[/TD]
[TD]3,960
[/TD]
[/TR]
[TR]
[TD]3500120210
[/TD]
[TD]xx5
[/TD]
[TD]IT_T-OPS
[/TD]
[TD]Description-5
[/TD]
[TD]4600018652
[/TD]
[TD]EUR
[/TD]
[TD]6,230
[/TD]
[/TR]
</tbody>[/TABLE]

Hope that helps - thanks again for taking the time to understand and provide a solution!

 

[JoeMo]

Can you clarify for me?
1. Is the data always sorted by PO Number?
2. For the data you posted, there's a change in PO at every line. Do you want a subtotal in col S for each line?

 

[1cyril1]

Yes, I meant to say the Sort is by PO No. (ascending).

Sorry - in reality there will be loads of duplicate POs but the rushed sample I posted was from a filtered view where duplicates are hidden - there can be between 2 and 50 duplicates (or none!) and the subtotal would sum up all corresponding values to give the total for each PO 'family'. Ideally, I'd like the subtotal value to appear in Col S ..... on same row as each 'bottom' PO with no subtotal row at all but if that complicates things unduly please don't worry about it.

Many thanks once more

 

[JoeMo]

Looks like this doesn't require VBA. Here's the sample data you posted. Copy the array formula down to cover all your data, and don't forget that array formulas must be confirmed by ctrl+shift+enter not just enter.

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	PO Number	Created by			Material group	PO Description		Contract Number			PO Local Currency		PO Value (Euro)	Subtot by PO#
2	3500120173	xx			IT_T-OPS	Description-1		4600012081			EUR		4,447	4,447
3	3500120194	xx1			VEM_EPART	Description-2		4600015833			EUR		3,383	3,383
4	3500120198	xx2			CCE_SS-BL	Description-3A		4600018480			EUR		7,030	7,030
5	3500120199	xx3			CCE_SS-BL	Description-3B		4600018480			EUR		4,500	4,500
6	3500120202	xx4			FAC_SCAPE	Description-4		4600012534			EUR		3,960	3,960
7	3500120210	xx5			IT_T-OPS	Description-5		4600018652			EUR		6,230	6,230
Sheet10

 

[1cyril1]

Excellent Joe - I'd never heard of array formulas before so the learning goes on! Because of the need for care I'd appreciate your expert guidance on the following, before I test it over the next day or two:

1. unlike my sample, the data in my real-life spreadsheet starts in row#7 so 1st formula needed in S7 rather than S2. I could attempt to adjust but not sure I'd get right
2. each time I need to use the formula (monthly reporting task) the number of rows with data will vary (i.e. do I need to reference the full column to catch all?)

What will the formula need to look like to take account of these 2 factors?

Hopefully, that's all the questions from me - can't wait to try out!

 

[JoeMo]

Excellent Joe - I'd never heard of array formulas before so the learning goes on! Because of the need for care I'd appreciate your expert guidance on the following, before I test it over the next day or two:

1. unlike my sample, the data in my real-life spreadsheet starts in row#7 so 1st formula needed in S7 rather than S2. I could attempt to adjust but not sure I'd get right
2. each time I need to use the formula (monthly reporting task) the number of rows with data will vary (i.e. do I need to reference the full column to catch all?)

What will the formula need to look like to take account of these 2 factors?

Hopefully, that's all the questions from me - can't wait to try out!

Below is the formula for S7. Copy it from your browser then enter it in S7, with S7 selected click in the formula bar and press ctrl+shift+enter to convert the formula to an array formula. If you are successful with these steps you will notice that Excel adds opening and closing braces: {=your formula} around the formula signifying it's an array formula.

Array formulas generally take more time to calculate then non-array formulas so it's best not to reference entire columns. The formula will return "" (a zero-length string, which appears like a blank cell to the eye) for any row that has no PO# in col F. This allows you to copy the formula down as far as you think your data will go in the future, while not cluttering the, as yet, unused rows with error values. In the formula below I have assumed that the data will never go beyond row 1000 (see addresses in red font)- change that to whatever you wish.

=IF($F7="","",IF($F7<>$F8,SUM(IF($F$7:$F$1000=$F7,$R$7:$R$1000,0)),""))

 

[Peter_SSs]

Here are two non-array alternatives. They would simply need to be copied down to the end of the data or as far as you think you might ever need without having to consider what the last row of the range might be within the formula itself.

=IF(F7="","",IF(F7=F8,"",SUM(R$7:R7)-SUM(S$6:S6)))
or
=IF(F7="","",IF(F7=F8,"",SUMIF(F$7:F7,F7,R$7:R7)))

 

[1cyril1]

Sincere thanks Joe and Peter; no excuses now. I'll revert asap and confirm the outcome. I expect this will be of benefit to many Excel users - such a great forum