Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

**RobertOHare** · 07-08-2013, 11:27 AM

Marco for pivot table like fuctions. Subtotal column C, based on column B criteria. Column A is the product.
Because the product code doesn’t copy down I can’t use pivot tables. In this example would need to merge code 82548
COLUMN A B C
206967711 82548 20%
32338 20%
82548 20%
82661 20%
103737 20%

Need results as follows:
COLUMN A B C
206967711 82548 40%
32338 20%
82661 20%
103737 20%

This excel spreadsheet has many more rows which I deleted for security reasons. Most of my daily spreadsheets have thousands of lines which are tedious doing manually. Tried doing sumproduct, array and lookup value which didn’t work. Example spreadsheet is enclosed.
It also would be OK to bring the new totals to column D and E. Thank you in advance.

**AlphaFrog** · 07-08-2013, 09:22 PM

One way you could do this without a macro is to put this formula in an empty column to fill in the product codes for each Agent Code.
=IF(TRIM(B2)="","",IF(TRIM(A2)="",D1,TEXT(A2,"@")))

For this example, put it in cell D2 and fill down column D for all your data. Put a column header (Pcodes) in D1.

Then create a pivot table where the Row Area has Pcodes and Agent CODE. The Data area has Sum of Agent PCT. Turn off row and column Grand Totals on the pivot table's option dialog.

**RobertOHare** · 07-09-2013, 01:27 PM

This really helps a lot. One of the problems I have is deleting cells before I use your recomendation. Let me know if you now how to solve.

If Column C = 0
Delete cell B,C,D on the same line then move up cells B,C,D below with Loop

**AlphaFrog** · 07-09-2013, 01:38 PM

Originally Posted by RobertOHare

This really helps a lot. One of the problems I have is deleting cells before I use your recomendation. Let me know if you now how to solve.

If Column C = 0
Delete cell B,C,D on the same line then move up cells B,C,D below with Loop

Select column C
Select from the menu Data\Filter\Auto Filter
Select 0 from the C1 dropdown list
Select all the visible "zero" rows and delete

**RobertOHare** · 07-09-2013, 02:34 PM

Thank you AlphaFrog. This has been a great help. Hope I can help others with my wisdom like you in the future. Bob O'Hare

**AlphaFrog** · 07-09-2013, 02:57 PM

You're welcome.

Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

LinkBack

Thread Tools

Rate This Thread

Display

Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

Re: Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

Re: Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

Re: Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

Re: Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

Re: Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions