Display unique values from Column based on Criteria of Another Column

[gldurand]

Hi There,

I am using Excel 2016

I have a huge dataset 30 col/1000 rows but 2 specific ones that i need to use for this task i think.

This is the PIVOT showing the data, but there are a number of duplicates in SPRINT

I want to be able to put into the cells for each issue key the unique Sprints associated to them

1) I want to remove the prefix and only put the numerical value)
2) The source data changes daily
3) I would prefer a formula (Even if i need to use helper columns) but VBA is Fine

Here is my staging area where i want my result J7.. J8 etc....

Any help would certainly save me a huge amount of manual work.

THanks alot

 

[gldurand]

Hi, wow, saves other steps with the enhancement. now i can run it right from the data source without the in-between pivot table.
Can you reload SPRINTSnew2.xlsx... i somehow messed it up.. I owe you big time thanks

 

[fjns]

HI, I restored the original version of SPRINTSnew2.xlsx.

 

[gldurand]

I2: =IF(COUNTIF(A$2:A2,A2)=1,SUM(H$2:H2)+1,0) - cell of OH Key in column P
J2: =COUNTIFS(A:A,A2,E:E,"<"&E2) - index of Sprint per OH Key
K2: =IF(COUNTIF(A$2:A2,A2)=1,SUM(G$2:G2)+2,0) - first cell of OH Key in column A
L2: =MAX(K$2:K2)+J2 - cell of Sprint in column B
M2: =INDEX(D:D,MATCH(ROW(),L:L,0)) - Sprint occurences

Hi FJNS

Thanks for this again, but i ran into an issue when we have a unique key that has no occurrences. It does not list the value and returns a #N/A

CX360 Dashboard 2023.xlsx
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM		AO	AP
3	EPIC	Sprint	Aux1	Aux2	Aux3	Aux4	Aux5	Aux6	Aux7	Aux8	Aux9	Aux10	Aux11	Aux12	Aux13	Aux14	Aux15	Aux16	Aux17	Aux18	Aux19	Aux20	Aux21	Aux22		Issue Key	Sprints
4						22					AA2AAAI-75	AA2AAAI Sprint 2	11	2	2	2	0	1	2	0	2	2	2	2		AA2AAAI-75	2-3
5						22					AA2AAAI-75	AA2AAAI Sprint 3	11	3	3	2	2	0	0	1	0	3	3	2-3		AA2AAAI-76	1-2
6						22					AA2AAAI-76	AA2AAAI Sprint 1	11	1	1	2	0	1	3	0	4	4	1	1		AA2AAAI-77	1-3
7						22					AA2AAAI-76	AA2AAAI Sprint 2	11	2	2	2	2	0	0	1	0	5	2	1-2		AA2AAAI-78	1-2-3
8						22					AA2AAAI-77	AA2AAAI Sprint 1	11	1	1	2	0	1	4	0	6	6	1	1		AA2AAAI-81	1-2-3
9						22					AA2AAAI-77	AA2AAAI Sprint 3	11	3	3	2	2	0	0	1	0	7	3	1-3		AA2AAAI-82	2-3
10						22					AA2AAAI-78	AA2AAAI Sprint 1	11	1	1	3	0	1	5	0	8	8	1	1		AA2AAAI-83	1-2-3
11						22					AA2AAAI-78	AA2AAAI Sprint 2	11	2	2	3	0	0	0	1	0	9	2	1-2		AA2AAAI-84	2-3
12						22					AA2AAAI-78	AA2AAAI Sprint 3	11	3	3	3	3	0	0	2	0	10	3	1-2-3		AA2AAAI-86	2
13						22					AA2AAAI-81	AA2AAAI Sprint 1	11	1	1	3	0	1	6	0	11	11	1	1		#N/A	#N/A
14	AA2AAAI-83	AA2AAAI Sprint 1	AA2AAAI-83	AA2AAAI Sprint 1	1	22	AA2AAAI-83	AA2AAAI Sprint 1	AA2AAAI-83#AA2AAAI Sprint 1	16	AA2AAAI-81	AA2AAAI Sprint 2	11	2	2	3	0	0	0	1	0	12	2	1-2
15	AA2AAAI-75	AA2AAAI Sprint 1	AA2AAAI-75	AA2AAAI Sprint 1	1	22	AA2AAAI-75	AA2AAAI Sprint 1	AA2AAAI-75#AA2AAAI Sprint 1	1	AA2AAAI-81	AA2AAAI Sprint 3	11	3	3	3	3	0	0	2	0	13	3	1-2-3
16	AA2AAAI-76	AA2AAAI Sprint 2	AA2AAAI-76	AA2AAAI Sprint 2	1	22	AA2AAAI-76	AA2AAAI Sprint 2	AA2AAAI-76#AA2AAAI Sprint 2	5	AA2AAAI-82	AA2AAAI Sprint 2	11	2	2	2	0	1	7	0	14	14	2	2
17	AA2AAAI-81					22					AA2AAAI-82	AA2AAAI Sprint 3	11	3	3	2	2	0	0	1	0	15	3	2-3
18	AA2AAAI-81					22					AA2AAAI-83	AA2AAAI Sprint 1	11	1	1	3	0	1	8	0	16	16	1	1
19	AA2AAAI-81	AA2AAAI Sprint 1	AA2AAAI-81	AA2AAAI Sprint 1	1	22	AA2AAAI-81	AA2AAAI Sprint 1	AA2AAAI-81#AA2AAAI Sprint 1	11	AA2AAAI-83	AA2AAAI Sprint 2	11	2	2	3	0	0	0	1	0	17	2	1-2
20	AA2AAAI-77					22					AA2AAAI-83	AA2AAAI Sprint 3	11	3	3	3	3	0	0	2	0	18	3	1-2-3
21	AA2AAAI-81	AA2AAAI Sprint 2	AA2AAAI-81	AA2AAAI Sprint 2	1	22	AA2AAAI-81	AA2AAAI Sprint 2	AA2AAAI-81#AA2AAAI Sprint 2	12	AA2AAAI-84	AA2AAAI Sprint 2	11	2	2	2	0	1	9	0	19	19	2	2
22	AA2AAAI-83					22					AA2AAAI-84	AA2AAAI Sprint 3	11	3	3	2	2	0	0	1	0	20	3	2-3
23	AA2AAAI-78	AA2AAAI Sprint 2	AA2AAAI-78	AA2AAAI Sprint 2	1	22	AA2AAAI-78	AA2AAAI Sprint 2	AA2AAAI-78#AA2AAAI Sprint 2	9	AA2AAAI-86	AA2AAAI Sprint 2	11	2	2	1	1	1	10	0	21	21	2	2
24	AA2AAAI-86	AA2AAAI Sprint 2	AA2AAAI-86	AA2AAAI Sprint 2	1	22	AA2AAAI-86	AA2AAAI Sprint 2	AA2AAAI-86#AA2AAAI Sprint 2	21	#N/A	#N/A	11	#N/A	#N/A	1	1	1	11	1	22	23	#N/A	#N/A
25	AA2AAAI-77					22							11
Export - AAAI

Cell Formulas
Range	Formula
P4:P25	P4	=IF(Source4[@[Feature Link]]="","",Source4[@[Feature Link]])
Q4:Q25	Q4	=IF(Source4[@Sprint]="","",Source4[@Sprint])
R4:R25	R4	=IF(OR(INDEX(P:P,ROW())="",INDEX(Q:Q,ROW())=""),"",INDEX(P:P,ROW()))
S4:S25	S4	=IF(OR(INDEX(P:P,ROW())="",INDEX(Q:Q,ROW())=""),"",INDEX(Q:Q,ROW()))
T4:T25	T4	=IF(R4="","",IF(COUNTIFS(R$4:R4,R4,S$4:S4,S4)=1,1,""))
U4:U25	U4	=SUM(T:T)+1
V4:V25	V4	=IF(R4="","",IF(T4=1,R4,""))
W4:W25	W4	=IF(R4="","",IF(T4=1,S4,""))
X4:X25	X4	=IF(R4="","",V4&"#"&W4)
Y4:Y25	Y4	=IF(R4="","",U$4-COUNTIFS(X:X,">"&X4))
Z4:Z25	Z4	=IF(ROW()-2>U$4,"",INDEX(V:V,MATCH(ROW()-2,Y:Y,0)))
AA4:AA25	AA4	=IF(ROW()-2>U$4,"",INDEX(W:W,MATCH(ROW()-2,Y:Y,0)))
AB4:AB25	AB4	=MAX(AH:AH)
AC4:AC25	AC4	=IF(ROW()-2>U$4,"",RIGHT(AA4,1))
AD4:AD25	AD4	=IF(ROW()-2>U$4,"",VALUE(AC4))
AE4:AE25	AE4	=IF(ROW()-2>U$4,"",COUNTIF(Z:Z,Z4))
AF4:AF25	AF4	=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=AE4,AE4,0))
AG4:AG25	AG4	=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=1,1,0))
AH4:AH25	AH4	=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=1,SUM(AG$4:AG4)+1,0))
AI4:AI25	AI4	=IF(ROW()-2>U$4,"",COUNTIFS(Z:Z,Z4,AD:AD,"<"&AD4))
AJ4:AJ25	AJ4	=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=1,SUM(AF$4:AF4)+MIN(2,AE4),0))
AK4:AK25	AK4	=IF(ROW()-2>U$4,"",MAX(AJ$4:AJ4)+AI4)
AL4:AL25	AL4	=IF(ROW()-2>U$4,"",INDEX(AC:AC,MATCH(ROW()-2,AK:AK,0)))
AM4:AM25	AM4	=IF(ROW()-2>U$4,"",IF(AG4=1,AL4,AM3&"-"&AL4))
AO4:AO25	AO4	=IF(ROW()-2>AB$4,"",INDEX(Z:Z,MATCH(ROW()-2,AH:AH,0)))
AP4:AP25	AP4	=IF(ROW()-2>AB$4,"",INDEX(AM:AM,MATCH(ROW()-2,AH:AH,0)+INDEX(AE:AE,MATCH(ROW()-2,AH:AH,0))-1))

Can you help??

 

[fjns]

Hi, I created two new tables for correction of the issue.

The formulas used in the new tables:

SPRINTSnew3.xlsx
tech!A4: =IF(OR(INDEX(work!A:A,ROW())="",INDEX(work!B:B,ROW())=""),"",INDEX(work!A:A,ROW())) (Range: A4:A100 ...)
tech!B4: =IF(OR(INDEX(work!A:A,ROW())="",INDEX(work!B:B,ROW())=""),"",INDEX(work!B:B,ROW())) (Range: B4:B100 ...)
tech!C4: =IF(A4="","",IF(COUNTIFS(A$4:A4,A4,B$4:B4,B4)=1,1,"")) (Range: C4:C100 ...)
tech!D4: =MATCH("_aux",LEFT(LOWER(Z:Z),4),0)
tech!E4: =MATCH("_aux",LEFT(LOWER(Z:Z),4),0)+SUM(C:C)
tech!F4: =IF(A4="","",IF(C4=1,A4,"")) (Range: F4:F100 ...)
tech!G4: =IF(A4="","",IF(C4=1,B4,"")) (Range: G4:G100 ...)
tech!H4: =IF(A4="","",F4&"#"&G4) (Range: H4:H100 ...)
tech!I4: =IF(A4="","",E$4-COUNTIFS(H:H,">"&H4)) (Range: I4:I100 ...)
tech!J4: =IF(ROW()>E$4,"",INDEX(F:F,MATCH(ROW(),I:I,0))) (Range: J4:J100 ...)
tech!K4: =IF(ROW()>E$4,"",INDEX(G:G,MATCH(ROW(),I:I,0))) (Range: K4:K100 ...)
tech!L4: =MATCH("_aux",LEFT(LOWER(Z:Z),4),0)+MAX(R:R)-1
tech!M4: =IF(ROW()>E$4,"",RIGHT(K4,LEN(K4)-SEARCH(work!B$1,K4)-LEN(work!B$1))) (Range: M4:M100 ...)
tech!N4: =IF(ROW()>E$4,"",VALUE(M4)) (Range: N4:N100 ...)
tech!O4: =IF(ROW()>E$4,"",COUNTIF(J:J,J4)) (Range: O4:O100 ...)
tech!P4: =IF(ROW()>E$4,"",IF(COUNTIF(J$4:J4,J4)=O4,O4,0)) (Range: P4:P100 ...)
tech!Q4: =IF(ROW()>E$4,"",IF(COUNTIF(J$4:J4,J4)=1,1,0)) (Range: Q4:Q100 ...)
tech!R4: =IF(ROW()>E$4,"",IF(COUNTIF(J$4:J4,J4)=1,SUM(Q$4:Q4)+1,0)) (Range: R4:R100 ...)
tech!S4: =IF(ROW()>E$4,"",COUNTIFS(J:J,J4,N:N,"<"&N4)) (Range: S4:S100 ...)
tech!T4: =IF(ROW()>E$4,"",MATCH("_aux",LEFT(LOWER(Z:Z),4),0)+IF(COUNTIF(J$4:J4,J4)=1,SUM(P$4:P4)+MIN(2,O4)-1,0)) (Range: T4:T100 ...)
tech!U4: =IF(ROW()>E$4,"",MAX(T$4:T4)+S4) (Range: U4:U100 ...)
tech!V4: =IF(ROW()>E$4,"",INDEX(M:M,MATCH(ROW(),U:U,0))) (Range: V4:V100 ...)
tech!W4: =IF(ROW()>E$4,"",IF(Q4=1,V4,W3&"-"&V4)) (Range: W4:W100 ...)
tech!Y4: =IF(ROW()>L$4,"",INDEX(J:J,MATCH(ROW()-MATCH("_aux",LEFT(LOWER(Z:Z),4),0)+1,R:R,0))) (Range: Y4:Y50 ...)
tech!Z4: =IF(ROW()>L$4,"",INDEX(W:W,MATCH(ROW()-MATCH("_aux",LEFT(LOWER(Z:Z),4),0)+1,R:R,0)+INDEX(O:O,MATCH(ROW()-MATCH("_aux",LEFT(LOWER(Z:Z),4),0)+1,R:R,0))-1)) (Range: Z4:Z100 ...)

AAAI.xlsx
R4: =IF(OR(INDEX(P:P,ROW())="",INDEX(Q:Q,ROW())=""),"",INDEX(P:P,ROW())) (Range: R4:R100 ...)
S4: =IF(OR(INDEX(P:P,ROW())="",INDEX(Q:Q,ROW())=""),"",INDEX(Q:Q,ROW())) (Range: S4:S100 ...)
T4: =IF(P4="","",IF(COUNTIFS(P$4:P4,P4,Q$4:Q4,Q4)=1,1,"")) (Range: T4:T100 ...)
U4: =MATCH("_aux",LEFT(LOWER(Y:Y),4),0)
V4: =MATCH("_aux",LEFT(LOWER(Y:Y),4),0)+SUM(T:T)
W4: =IF(R4="","",IF(T4=1,R4,"")) (Range: W4:W100 ...)
X4: =IF(R4="","",IF(T4=1,S4,"")) (Range: X4:X100 ...)
Y4: =IF(R4="","",W4&"#"&X4) (Range: Y4:Y100 ...)
Z4: =IF(R4="","",V$4-COUNTIFS(Y:Y,">"&Y4)) (Range: Z4:Z100 ...)
AA4: =IF(ROW()>V$4,"",INDEX(W:W,MATCH(ROW(),Z:Z,0))) (Range: AA4:AA100 ...)
AB4: =IF(ROW()>V$4,"",INDEX(X:X,MATCH(ROW(),Z:Z,0))) (Range: AB4:AB100 ...)
AC4: =MATCH("_aux",LEFT(LOWER(Y:Y),4),0)+MAX(AI:AI)-1
AD4: =IF(ROW()>V$4,"",RIGHT(AB4,LEN(AB4)-SEARCH(Q$3,AB4)-LEN(Q$3))) (Range: AD4:AD100 ...)
AE4: =IF(ROW()>V$4,"",VALUE(AD4)) (Range: AE4:AE100 ...)
AF4: =IF(ROW()>V$4,"",COUNTIF(AA:AA,AA4)) (Range: AF4:AF100 ...)
AG4: =IF(ROW()>V$4,"",IF(COUNTIF(AA$4:AA4,AA4)=AF4,AF4,0)) (Range: AG4:AG100 ...)
AH4: =IF(ROW()>V$4,"",IF(COUNTIF(AA$4:AA4,AA4)=1,1,0)) (Range: AH4:AH100 ...)
AI4: =IF(ROW()>V$4,"",IF(COUNTIF(AA$4:AA4,AA4)=1,SUM(AH$4:AH4)+1,0)) (Range: AI4:AI100 ...)
AJ4: =IF(ROW()>V$4,"",COUNTIFS(AA:AA,AA4,AE:AE,"<"&AE4)) (Range: AJ4:AJ100 ...)
AK4: =IF(ROW()>V$4,"",MATCH("_aux",LEFT(LOWER(Y:Y),4),0)+IF(COUNTIF(AA$4:AA4,AA4)=1,SUM(AG$4:AG4)+MIN(2,AF4)-1,0)) (Range: AK4:AK100 ...)
AL4: =IF(ROW()>V$4,"",MAX(AK$4:AK4)+AJ4) (Range: AL4:AL100 ...)
AM4: =IF(ROW()>V$4,"",INDEX(AD:AD,MATCH(ROW(),AL:AL,0))) (Range: AM4:AM100 ...)
AN4: =IF(ROW()>V$4,"",IF(AH4=1,AM4,AN3&"-"&AM4)) (Range: AN4:AN100 ...)
AP4: =IF(ROW()>AC$4,"",INDEX(AA:AA,MATCH(ROW()-MATCH("_aux",LEFT(LOWER(Y:Y),4),0)+1,AI:AI,0))) (Range: AP4:AP100 ...)
AQ4: =IF(ROW()>AC$4,"",INDEX(AN:AN,MATCH(ROW()-MATCH("_aux",LEFT(LOWER(Y:Y),4),0)+1,AI:AI,0)+INDEX(AF:AF,MATCH(ROW()-MATCH("_aux",LEFT(LOWER(Y:Y),4),0)+1,AI:AI,0))-1)) (Range: AQ4:AQ100 ...)

The required properties of the tables:

SPRINTSnew3.xlsx
name of column tech!H: _AuxC8 (for the formulas of cells tech!D4, tech!E4, tech!I4, tech!L4, tech!T4, tech!Y4 and tech!Z4)
name of column work!B: Sprint and name of column tech!Z: SPRINTS (for the formula of cell tech!M4)

AAAI.xlsx
name of column Y: _Aux8 (for the formulas of cells U4, V4, Z4, AC4, AK4, AP4 and AQ4)
name of column Q: Sprint and name of column AQ: Sprints (for the formula of cell AD4)

SPRINTSnew3.xlsx
AAAI.xlsx

 

[gldurand]

Hi FJNS

Question, in Google Sheets, the ARRAY_CONSTRAIN(ARRAYFORMULA is not required when using the formula in Excel 2016. I can just remove it correct??

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(OR(INDEX(work!A:A;ROW())="";INDEX(work!B:B;ROW())="");"";INDEX(work!A:A;ROW()))); 1; 1)

 

[fjns]

Hi, there have been times when Google Drive broke a table and it malfunctioned after downloading.
I now uploaded the tables also packed. Fortunately, after downloading the ZIP file, the unpacked tables will work correct with your Office 2016.

SPRINTSnew3.zip
AAAI.zip