0

I have 3 columns in my excel sheet - ID, Version, Material No.

for ex -

enter image description here

and i want the pivot table to be like -

enter image description here

How can i create the third calculated column because it involves two aggregation function to create it. I am looking for count of maximum version so i need MAX function also, not count of unique values.

Prasoon Pandey
  • 99
  • 2
  • 4
  • 13
  • 1
    Could you explain the calculation logic for third column? – Ambrish Pathak May 19 '17 at 05:50
  • For one particular ID (say 1) the third column should calculate the count of material no which has maximum version. For ID 1 highest version is 2 and only material no 101 is there. So third column should be 1. Similarly for ID 2 max Version is 2 and 3 material (103,104,105) has these version so third columns should have value as 3. – Prasoon Pandey May 19 '17 at 05:54
  • Possible duplicate of [Simple Pivot Table to Count Unique Values](http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values) . If I understand your description correct. – Andreas May 19 '17 at 06:20
  • No Sir, this is not exact duplicate of that. Thanks for your suggestion. – Prasoon Pandey May 19 '17 at 07:16

1 Answers1

1

Consider a solution based on worksheet functions.

Assuming the table is in B5:D14, enter an array formula, which counts unique IDs

=SUM(N($B$5:$B$14<>$B$4:$B$13))

into G2, enter 

COUNT($B$5:$B$14)-G2

in H2.

Enter an array formula ( type Ctrl+Shift+Enter instead of just Enter), which collects unique IDs on top and fill the rest of the output lines with blanks:

=IFERROR(INDEX($B$1:$B$14,SMALL(N($B$5:$B$14<>$B$4:$B$13)*ROW($B$5:$B$14),ROW(B5)-ROW($B$4)+$H$2))*N(SMALL(N($B$5:$B$14<>$B$4:$B$13)*ROW($B$5:$B$14),ROW(B5)-ROW($B$4)+$H$2)>0),"")

in G5, another array formula

=IF(LEN(G5)>0,MAX(N($B$5:$B$14=G5)*$C$5:$C$14),"")

in H5 and one more array formula 

=IF(LEN(G5)>0,SUM(($B$5:$B$14=G5)*($C$5:$C$14=H5)),"")

in I5, select range G5:I5 and drag/copy it down.

enter image description here

g.kov
  • 333
  • 2
  • 10