Pivoting on horizontally stored data - breaking apart one-to-many relationship

[ken800]

I have a huge file that is updated regularly that has both past and future planned server configurations based on sales forecasts. I'm trying to get a handle on forward demand and want to pivot out the SSDs we are installing in various servers and what server slot they are put in. I have created a sample chart and sample ideal pivot above. For simplicity sake, there are only 6 in the above example but there are hundreds of possible drives I can use (just 5 here) and up to 24 possible slots the drives can go into. Essentially I want to be able to pivot out the data and sum each quarter in the pivot so I can total and sort at the end. Not every drive is used in every quarter and I need to make sure I account for every single drive used in whatever time period it is modeled (usually 1 year back and 1 year forward). What would you suggest I do to break this down into something usable?

 

[awoohaw]

I am sure this can be done more elegantly in an all in one LET statement. But, this will do what you ask:
I think my totals are wrong because I had to recreate your data, and probably have a typo (or your example was incorrect).

Book1
	A	B	C	D	E	F	G	H	I
1	Quarter	Server	Slot1	Slot2	Slot3	Slot4	Slot5	Slot6
2	2023-Q1	OZBK	SS1	SS1	SS1	SS2
3	2023-Q1	ICLV	SS1	SS2	SS2
4	2023-Q2	OFXS	SS3	SS3	SS1	SS1	SS1
5	2023-Q2	NFKY		SS4	SS4
6	2023-Q3	JPEZ	SS1	SS4
7	2023Q4	KYCA	SS1	SS1	SS1	SS1
8	2023Q4	HQOA	SS1	SS1	SS5
9	2023Q4	XQEB	SS2	SS2	SS5
10
11		2023-Q1	2023-Q2	2023-Q3	2023Q4
12	SS1	4	3	1	6	14
13	SS2	3	0	0	2	5
14	SS3	0	2	0	0	2
15	SS4	0	2	1	0	3
16	SS5	0	0	0	2	2
17		7	7	2	10	26
18
Sheet3

Cell Formulas
Range	Formula
B11:E11	B11	=LET(Q,$A$2:$A$9, UnqQ,TRANSPOSE(UNIQUE(Q,FALSE,FALSE)),UnqQ)
A12:A16	A12	=LET(slots,$C$2:$H$9,Unqslots,TRANSPOSE(UNIQUE(TEXTSPLIT(TEXTJOIN(",",,slots),",",,TRUE),TRUE,FALSE)),Unqslots)
B12:E16	B12	=SUMPRODUCT(($A12=$C$2:$H$9)*(B$11=$A$2:$A$9))
F12:F16	F12	=SUM(B12:E12)
B17:F17	B17	=SUM(B12:B16)
Dynamic array formulas.