I propose the following:

4 tabs:

1-one database (DB) that would sort the unique data from your raw data.

2-one Raw data (RD) containing the data you wish to study.

3-one pivot table (PT)

4-one table that would extract the data from the pivot table (recap)

RD may look as follows:

GL | CC | value |

1002 | cxxx1 | 10000 |

1004 | cxxx1 | 8000 |

1002 | cxxx2 | 7000 |

1003 | cxxx2 | 4000 |

1002 | cxxx3 | 6540 |

1004 | cxxx3 | 6000 |

1001 | cxxx4 | 4000 |

1003 | cxxx4 | 9000 |

1006 | cxxy1 | 1234 |

1007 | cxxy1 | 5678 |

1008 | cxxz2 | 9101 |

<tbody>

</tbody>

Four Named Ranges are created here:

ListGL would be =OFFSET(RD!$A$2,0,0,COUNT(IF(RD!$A$2:$A$1000="","",1)),1)

ListCC would be =OFFSET(RD!$B$2,0,0,COUNT(IF(RD!$B$2:$B$1000="","",1)),1)

RvCC would be =ROW(ListCC)-ROW(RD!$B$2)+1

RvGL would be =ROW(ListGL)-ROW(RD!$A$2)+1

As for the data kindly just make sure to sort them per Column B (CC).

DB will look like this:

7 | 6 |

Unique GL | Unique CC |

1001 | cxxx1 |

1002 | cxxx2 |

1003 | cxxx3 |

1004 | cxxx4 |

1006 | cxxy1 |

1007 | cxxz2 |

1008 | |

| |

| |

| |

<tbody>

</tbody>

Add one Named Range here called UniqueCC =DB!$B$3:$B$300

In A1 type: =SUM(IF(FREQUENCY(IF(ListGL<>"",MATCH("~"&ListGL,ListGL&"",0)),RvGL),1))

Ctrl + Shift + Enter, not just enter.

That would give you in this example a 7 as there are 7 different GL codes (1001,1002,1003,1004,1006,1007 and 1008)

In B1 Type:

=SUM(IF(FREQUENCY(IF(ListCC<>"",MATCH("~"&ListCC,ListCC&"",0)),RvCC),1))

Ctrl + Shift + Enter, not just enter.

That would give you in this example a 6 as there are 6 different CC codes (cxxx1,cxxx2,cxxx3,cxxx4,cxxy1 and cxxz2)

In A3 type:

=IFERROR(INDEX(ListGL,MATCH(0,IF(MAX(NOT(COUNTIF(RD!$F$1:F1,ListGL))*(COUNTIF(ListGL,">"&ListGL)+1))=(COUNTIF(ListGL,">"&ListGL)+1),0,1),0)),"")

Ctrl + Shift + Enter, not just enter.

In B3 type:

=IFERROR(INDEX(ListCC,MATCH(0,IF(MAX(NOT(COUNTIF(RD!$G$1:G1,ListCC))*(COUNTIF(ListCC,">"&ListCC)+1))=(COUNTIF(ListCC,">"&ListCC)+1),0,1),0)),"")

Ctrl + Shift + Enter, not just enter.

Copy down till needed.

PT may look as follows:

**results** | CC | | | | | |

GL | cxxx1 | cxxx2 | cxxx3 | cxxx4 | cxxy1 | cxxz2 |

1001 | | | | 4000 | | |

1002 | 10000 | 7000 | 6540 | | | |

1003 | | 4000 | | 9000 | | |

1004 | 8000 | | 6000 | | | |

1006 | | | | | 1234 | |

1007 | | | | | 5678 | |

1008 | | | | | | 9101 |

<tbody>

</tbody>

Make sure to rename the Sum of values, the Column and Rows Labels as shown above that is “results”, “CC” and “GL”. Here the pivot is in A3.

You may choose a range bigger than your actual data range in RD, such to ensure that whatever data added may be automatically added upon refreshing the PT.

Recap would be as follows:

In A3 type: =TRANSPOSE(DB!$A$3:$A$300)

Copy down till row 300.

You may opt to increase the range of this if you have thousand of GL code by modifying the range from 300 to 3000…

In B2 type: =INDEX(UniqueCC,COLUMN()-COLUMN($B$2)+1)

Ctrl + Shift + Enter, not just enter.

Copy to the right till needed.

In B3 type:

=IF(ISBLANK((GETPIVOTDATA("results",PT!$A$3,"CC",B$2,"GL",$A3))),"",IFERROR(GETPIVOTDATA("results",PT!$A$3,"CC",B$2,"GL",$A3),""))

Enter.

Copied right and down till needed.

The result would be something like this:

| cxxx1 | cxxx2 | cxxx3 | cxxx4 | cxxy1 | cxxz2 |

1001 | 0 | 0 | 0 | 4000 | 0 | 0 |

1002 | 10000 | 7000 | 6540 | 0 | 0 | 0 |

1003 | 0 | 4000 | 0 | 9000 | 0 | 0 |

1004 | 8000 | 0 | 6000 | 0 | 0 | 0 |

1006 | 0 | 0 | 0 | 0 | 1234 | 0 |

1007 | 0 | 0 | 0 | 0 | 5678 | 0 |

1008 | 0 | 0 | 0 | 0 | 0 | 9101 |

<tbody>

</tbody>

Link to your file.

The idea was to make the recap as dynamic as possible.

Is this close to what you needed?