CODEWITHSUNDEEP
excelexcel-formulapivot-tablevba
Shane
Shane

Reputation: 5151

Excel pivot table with macro to consolidate values based on field name?

I have a table with two relevant columns "src" and "on hand". In the src column I have two types of entries for each row that I want to treat in different ways when creating the pivot table.

  1. For any entry with a value in the src column with a string of length > 2, I want to create a new row in the pivot table with that value.
  2. For the "on hand" field, I want to get the "on hand" value of the last entry under that row with a src value where the length of the field name is 2;

For example, where the original table reads (Name of Product = src):

-------------------------
|Name of Product|on Hand|
-------------------------
|foo            |(1.000)|
-------------------------
|xx             |0.000  |
-------------------------
|xx             |(1.000)|
-------------------------
|xx             |7.000  |
-------------------------
|bar            |0.000  |
-------------------------
|xx             |(1.000)|
-------------------------
|xx             |2.000  |
-------------------------

The pivot table becomes:

-------------------------
|Name of Product|on Hand|
-------------------------
|foo            |7.000  |
-------------------------
|bar            |2.000  |
-------------------------

Unfortunately I have no sense of orientation on this and am unsure where to start. Is there a general approach to this sort of problem?

Upvotes: 1

Views: 98

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19299

You can do this with two helper columns. Enter the formulas detailed below in columns C and D and copy them down. You then run your pivot table on Columns C and D.

See the screenshot:

enter image description here

Category is based on this formula:

=IF(ROW()=2,A2,IF(LEN(A2)=2,C1,A2))

Which says:

  • if the row is 2 just take the product (because its the first row we can't look at the 'above' value)
  • else, if the product has length of 2 take the value above
  • else, take the product - meaning bar becomes the new value because length >3

Closing is based on this formula:

=IF(C2<>C3,B2,0)

Which says:

  • if the category below is the same then we're not at the closing balance
  • else, the category below is new therefore we've reached the closing balance

Upvotes: 1

Related Questions