Help with formula consolidating Data

[Newbie73]

Hello everyone,

I need some help with a formula that I can't get my head around it. Example sheet provided:

test1.xlsx

Column A has IDs which often will be duplicated. Column B has a number from a test specific to the ID on Column A of that row and then Column C dates for those tests.

So what I would like to do is to consolidate these data as in, all the results for each ID from Column A sorted per year and If more than one result (column B) different than zero from the same year then sum the value.

There's a wanted result on the example sheet which hopefully it will help.

Thank you in advance for your help and please do tell if I didn't explain something correctly or if you need any more informations.

 

[Fluff]

How about

=LET(f,FILTER(HSTACK(A2:B100,YEAR(C2:C100)),B2:B100<>0),u,UNIQUE(HSTACK(CHOOSECOLS(f,1,3))),b,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2)),INDEX(f,,2))))),CHOOSECOLS(HSTACK(u,b),1,3,2))

 

[felixstraube]

How about?:

Book6.xlsx
	A	B	C	D	E	F	G
1					wanted result
2	PTTT706401402	0	5/6/2014		PTTT706302598	1	2020
3	PTTT706401402	0	12/17/2013		PTTT706302598	1	2019
4	PTTT706401402	0	9/30/2013		PTTT706702602	3	2023
5	PTTT706401402	0	6/10/2013
6	PTTT706401402	0	2/12/2013		PTTT706302598	1	2020
7	PTTT706401402	0	10/15/2012		PTTT706302598	1	2019
8	PTTT6241201406	0	11/28/2023		PTTT706702602	3	2023
9	PTTT6241201406	0	9/26/2023
10	PTTT6241201406	0	6/20/2023
11	PTTT6241201406	0	4/4/2023
12	PTTT706302598	1	3/17/2020
13	PTTT706302598	1	12/17/2019
14	PTTT706302598	0	9/9/2019
15	PTTT706302598	0	5/21/2019
16	PTTT706302598	0	2/25/2019
17	PTTT706302598	0	10/29/2018
18	PTTT706302598	0	7/18/2018
19	PTTT706302598	0	4/18/2018
20	PTTT706302598	0	1/9/2018
21	PTTT706702602	0	11/28/2023
22	PTTT706702602	0	9/26/2023
23	PTTT706702602	1	6/20/2023
24	PTTT706702602	0	4/4/2023
25	PTTT706702602	0	1/3/2023
26	PTTT706702602	2	6/26/2023
Sheet6

Cell Formulas
Range	Formula
E6:G8	E6	=LET(id,A2:A26,c,B2:B26,y,YEAR(C2:C26), u, UNIQUE(HSTACK(id,y)), cv, BYROW(u, LAMBDA(x, SUM(FILTER(c, (id=CHOOSECOLS(x, 1))*(y=CHOOSECOLS(x, 2)))))), r, HSTACK(CHOOSECOLS(u, 1), cv, CHOOSECOLS(u, 2)), FILTER(r, cv) )
Dynamic array formulas.

 

[felixstraube]

Oh, saw the other answer just after posting.

 

[Newbie73]

Thank you both! Tried both formulas and they both seem to work as intended!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Newbie73]

May I ask just one more addition to the formula? I've added a another column into the example sheet, usually either "CLEAR" "IR" or "R" if it matters.

Any way if in a year that the ID was "R" or "IR" to be added in as well? With priority to "R" as in, if in the same year the same ID was "IR" and "R" to simply say "R". If "CLEAR" it ca be ignored and blank.

Thanks and sorry to bother again!

 

[Newbie73]

Correcting something on my comment above, if "R" on Column D of that year then "2" on Column H and if "IR" then "1" as this will help me on another formula afterwards. If "CLEAR" leave blank please.

Updated the example sheet.

 

[Fluff]

How about

=LET(f,FILTER(HSTACK(A2:B100,YEAR(C2:C100),D2:D100),B2:B100<>0),u,UNIQUE(HSTACK(CHOOSECOLS(f,1,3))),b,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2)),INDEX(f,,2))))),c,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2))*((INDEX(f,,4)="ir")+(INDEX(f,,4)="r")),1,0)))),CHOOSECOLS(HSTACK(u,b,c),1,3,2,4))

 

[Newbie73]

Amazing! Thank you Fluff