How to Expand Components of an Assembly while preserving Dates

[Vog2654]

Hello,

I have a list of various assemblies sold at various points in time. I want to convert the list of assemblies sold into a list of each assembly's respective components sold while preserving the date in which these components were sold. Is there a formula I can use to get my two sets of data into the "DESIRED END PRODUCT" format?


ASSEMBLY RAW SALES DATA

Date	Assembly ID	Assembly QTY Sold	Component ID	Component Qty Per Assembly	Total QTY Component Sold
2/16/2024	323915	2			0
5/17/2024	292229	4			0
2/20/2024	324272	1			0
2/5/2024	322219	4			0
1/29/2024	321230	6			0
10/10/2024	341048	1			0
7/31/2024	348430	1			0

ASSEMBLY + COMPONENT RAW DATA

Assembly ID	Component ID	Component Qty Per Assembly
323915	63640	1
323915	52149	1
323915	64365	1
292229	63917	4
292229	63639	8
324272	63639	8
324272	63917	4
322219	63640	4
322219	63918	1
321230	63640	2
321230	63918	1
341048	64040	1
341048	64082	1
348430	63640	8
348430	63972	4

DESIRED END PRODUCT

Date	Assembly ID	Assembly QTY Sold	Component ID	Component Qty Per Assembly	Total QTY Component Sold
2/16/2024	323915	2	#N/A
2/16/2024			63640	1	2
2/16/2024			52149	1	2
2/16/2024			64365	1	2
5/17/2024	292229	4	#N/A
5/17/2024			63917	4	16
5/17/2024			63639	8	32
2/20/2024	324272	1	#N/A
2/20/2024			63639	8	8
2/20/2024			63917	4	4
2/5/2024	322219	4	#N/A
2/5/2024			63640	4	16
2/5/2024			63918	1	4
1/29/2024	321230	6	#N/A
1/29/2024			63640	2	12
1/29/2024			63918	1	6
10/10/2024	341048	1	#N/A
10/10/2024			64040	1	1
10/10/2024			64082	1	1
7/31/2024	348430	1	#N/A
7/31/2024			63640	8	8
7/31/2024			63972	4	4

The rows with the #N/A in them are not critical to be in the solution. I just need to know how much of each component was sold on each date.

 

[hagia_sofia]

Hello, would it not be better like this (please adjust ranges):

=LET(
a,HSTACK(XLOOKUP(A15:A29,B3:B9,A3:A9),A15:C29,XLOOKUP(A15:A29,B3:B9,C3:C9),XLOOKUP(A15:A29,B3:B9,C3:C9)*C15:C29),
CHOOSECOLS(a,1,2,5,3,4,6))

 

[Vog2654]

For anyone who happens to want to do this, If you insert both data sets into Power Query, you can combine the two and then use the editor to "explode" the data.