You might record part of this into a macro to make it "Auto".
Pivot Table Multiple Consolidation Ranges
Range: Select the data on sheet2; include top blank headers.
Layout: Drag Column button from Column field and
drag in Value button instead.
Data field: Count of Value
Options: No grand totals
Part of he Pivot Table will then look like this:
1 2 3 6
A 1 1 1
B 1 1
C 1

Name the Count of Value matrix <array1
Name the Column field <vect1
Create another matrix with this array formula:
=IF(array10,vect1,"")
Copy Paste Special Value
The matrix will then look like this:
1 2 3
1 2
6
Insert this matrix in the proper place on sheet1.
Remove the blank spaces and left justify with
Goto Special Constants Text
Delete Shift Left