How to aggregate data based on multiple criteria?

Hi All

I've a table for inventory tracking where parts coming from oversease needs to be tracked based on estimated monthly arrivals. Parts are spread across multiple order numbers and what I am trying to do is develop a report in which collate the part numbers such a way that for each individual part number, its aggregate total in transit is shown under a calendar month. The delivery schedule is well into 2024. But a few problems I am not able to figure out are that:

(i) Same part number under same order number is being shipped out over multiple shipments, so how do I make an aggregate for these arrivals and put them under separate calendar months according to their ETA?

(ii) The file contains historical data as well i.e parts which have recently been delivered. I am not able to come up with a logic to exclude these delivered parts from the count of incoming inventory.

The source file gets updated maybe couple of times a month and because most of the deliveries are well into the future, projection would not change that much in my report.

I've been trying various combination of vlookup, count, countif, IF statements but unable to come up with any formulae that could achieve the results. Of course a crude way would be to just filter the date based on required parameters, get the figures and put them manually in a calendar based table but I really want to see if some bit of automation can be constructed for this task. Would highly appreciate any assistance.

Originally Posted by Glenn Kennedy

Your description of what you WANT to see is vague, and you have not mocked it up. So this is a guess of what you want..

Formula:

=LET(

A,A2:F59,

B,UNIQUE(FILTER(INDEX(A,,4),INDEX(A,,5)="Yes")),

C,TRANSPOSE(UNIQUE(TEXT(SORT(FILTER(INDEX(A,,6),INDEX(A,,3)>0)),"mmm yyyy"))),

D,DROP(REDUCE("",C,LAMBDA(y,z,HSTACK(y,BYROW(B,LAMBDA(x,SUMPRODUCT((INDEX(A,,4)=x)*(TEXT(INDEX(A,,6),"mmm yyyy")=z)*INDEX(A,,3))))))),,1),

VSTACK(HSTACK("Code",C),HSTACK(B,D)))

Hi Glenn

Thank you so much for your support, it is what I was looking for. I tried but failed to understand your solution as I wanted to see if I can use it to further manipulate the data in some more creative ways. While I understand some stuff like unique, index etc but rest flew way above my head. Is there a way you can dumb it down for a freshie like myself? As an additional exercise oI was trying to sum and put the total for each part number under respective calendar month regardless of transit status. But those formulas like Drop, Reduce, Hstack totally threw me off.

My sincere apologies to both of you, yes I did not take time to read the forum rules and it's on me. Been in a tough spot after having spent quite a bit of time trying to solve this problem on my own and kept running into a wall, but no excuse, please accept my apologies.

Originally Posted by alansidman

If this works for you since you did not provide a sample solution

Excel 2016 (Windows) 64 bit

	H	I	J
1	PCB Model	Month Year	Quantity
2	11T40S4000-3D.007	June 2023	3
3	12T40S5000-5D.003	August 2023	10
4	11T40S4000-3D.007	October 2023	9
5	11T40S4000-3D.007	August 2023	2
6	11T40S4000-3D.007	July 2023	2
7	11T40S4000-3D.007	September 2023	2
8	12T40S5000-5D.003	October 2023	9
9	11T40S4000-3D.007	November 2023	6
10	11T40S4000-3D.007	December 2023	2
11	13T40S7000-7D.005	January 2024	3
12	12T40S5000-5D.003	March 2024	7
13	14T40S8000-8D.006	April 2024	8
14	13T40S7000-7D.005	April 2024	3
15	13T40S7000-7D.005	May 2024	4
16	12T40S5000-5D.003	May 2024	23
17	15T40S9000-9D.007	May 2024	4
18	12T40S5000-5D.003	June 2024	1
19	16T40S4000-4D.001	June 2024	4
20	13T40S7000-7D.005	June 2024	3
21	16T40S4000-4D.001	July 2024	4
22	12T40S5000-5D.003	July 2024	6
23	11T40S4000-3D.007	July 2024	3
24	16T40S4000-4D.001	August 2024	2
25	17T40S3000-4D.002	March 2024	7
26	11T40S4000-3D.007	October 2024	3
27	13T40S7000-7D.005	November 2024	4
28	17T40S3000-4D.002	September 2024	8

Sheet: Sheet1

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Required ETA", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Required ETA]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Required ETA]), Int64.Type),
    #"Inserted Merged Column" = Table.AddColumn(#"Inserted Year", "Month Year", each Text.Combine({[Month Name], Text.From([Year], "en-US")}, " "), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"PCB Model", "Month Year"}, {{"Quantity", each List.Sum([Qty]), type number}})
in
    #"Grouped Rows"

Wow! That was my next step for which I posted question to Kevin as well. You folks here all geniuses!

How do I load this data? I started Power BI in my Excel and loaded the source file that I am working with. For next step, do I put that above Power Query in the formula bar?