Display unique values from Column based on Criteria of Another Column

G

gldurand

Board Regular
Joined
Jun 8, 2006
Messages
178
Office Version
  1. 2016
Platform
  1. Windows
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

Screenshot 2023-02-13 142639.png


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....

Screenshot 2023-02-13 142901.png


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

THanks alot
 

Attachments

  • Screenshot 2023-02-13 142901.png
    Screenshot 2023-02-13 142901.png
    4.4 KB · Views: 20
  • Screenshot 2023-02-13 142901.png
    Screenshot 2023-02-13 142901.png
    4.4 KB · Views: 17
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
HI, I restored the original version of SPRINTSnew2.xlsx.
 
Upvote 0
fjns said:
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
Click to expand...
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
PQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAOAP
3EPICSprintAux1Aux2Aux3Aux4Aux5Aux6Aux7Aux8Aux9Aux10Aux11Aux12Aux13Aux14Aux15Aux16Aux17Aux18Aux19Aux20Aux21Aux22Issue KeySprints
4     22    AA2AAAI-75AA2AAAI Sprint 21122201202222AA2AAAI-752-3
5     22    AA2AAAI-75AA2AAAI Sprint 31133220010332-3AA2AAAI-761-2
6     22    AA2AAAI-76AA2AAAI Sprint 11111201304411AA2AAAI-771-3
7     22    AA2AAAI-76AA2AAAI Sprint 21122220010521-2AA2AAAI-781-2-3
8     22    AA2AAAI-77AA2AAAI Sprint 11111201406611AA2AAAI-811-2-3
9     22    AA2AAAI-77AA2AAAI Sprint 31133220010731-3AA2AAAI-822-3
10     22    AA2AAAI-78AA2AAAI Sprint 11111301508811AA2AAAI-831-2-3
11     22    AA2AAAI-78AA2AAAI Sprint 21122300010921-2AA2AAAI-842-3
12     22    AA2AAAI-78AA2AAAI Sprint 311333300201031-2-3AA2AAAI-862
13     22    AA2AAAI-81AA2AAAI Sprint 1111130160111111#N/A#N/A
14AA2AAAI-83AA2AAAI Sprint 1AA2AAAI-83AA2AAAI Sprint 1122AA2AAAI-83AA2AAAI Sprint 1AA2AAAI-83#AA2AAAI Sprint 116AA2AAAI-81AA2AAAI Sprint 211223000101221-2  
15AA2AAAI-75AA2AAAI Sprint 1AA2AAAI-75AA2AAAI Sprint 1122AA2AAAI-75AA2AAAI Sprint 1AA2AAAI-75#AA2AAAI Sprint 11AA2AAAI-81AA2AAAI Sprint 311333300201331-2-3  
16AA2AAAI-76AA2AAAI Sprint 2AA2AAAI-76AA2AAAI Sprint 2122AA2AAAI-76AA2AAAI Sprint 2AA2AAAI-76#AA2AAAI Sprint 25AA2AAAI-82AA2AAAI Sprint 2112220170141422  
17AA2AAAI-81    22    AA2AAAI-82AA2AAAI Sprint 311332200101532-3  
18AA2AAAI-81    22    AA2AAAI-83AA2AAAI Sprint 1111130180161611  
19AA2AAAI-81AA2AAAI Sprint 1AA2AAAI-81AA2AAAI Sprint 1122AA2AAAI-81AA2AAAI Sprint 1AA2AAAI-81#AA2AAAI Sprint 111AA2AAAI-83AA2AAAI Sprint 211223000101721-2  
20AA2AAAI-77    22    AA2AAAI-83AA2AAAI Sprint 311333300201831-2-3  
21AA2AAAI-81AA2AAAI Sprint 2AA2AAAI-81AA2AAAI Sprint 2122AA2AAAI-81AA2AAAI Sprint 2AA2AAAI-81#AA2AAAI Sprint 212AA2AAAI-84AA2AAAI Sprint 2112220190191922  
22AA2AAAI-83    22    AA2AAAI-84AA2AAAI Sprint 311332200102032-3  
23AA2AAAI-78AA2AAAI Sprint 2AA2AAAI-78AA2AAAI Sprint 2122AA2AAAI-78AA2AAAI Sprint 2AA2AAAI-78#AA2AAAI Sprint 29AA2AAAI-86AA2AAAI Sprint 21122111100212122  
24AA2AAAI-86AA2AAAI Sprint 2AA2AAAI-86AA2AAAI Sprint 2122AA2AAAI-86AA2AAAI Sprint 2AA2AAAI-86#AA2AAAI Sprint 221#N/A#N/A11#N/A#N/A1111112223#N/A#N/A  
25AA2AAAI-77    22      11             
Export - AAAI
Cell Formulas
RangeFormula
P4:P25P4=IF(Source4[@[Feature Link]]="","",Source4[@[Feature Link]])
Q4:Q25Q4=IF(Source4[@Sprint]="","",Source4[@Sprint])
R4:R25R4=IF(OR(INDEX(P:P,ROW())="",INDEX(Q:Q,ROW())=""),"",INDEX(P:P,ROW()))
S4:S25S4=IF(OR(INDEX(P:P,ROW())="",INDEX(Q:Q,ROW())=""),"",INDEX(Q:Q,ROW()))
T4:T25T4=IF(R4="","",IF(COUNTIFS(R$4:R4,R4,S$4:S4,S4)=1,1,""))
U4:U25U4=SUM(T:T)+1
V4:V25V4=IF(R4="","",IF(T4=1,R4,""))
W4:W25W4=IF(R4="","",IF(T4=1,S4,""))
X4:X25X4=IF(R4="","",V4&"#"&W4)
Y4:Y25Y4=IF(R4="","",U$4-COUNTIFS(X:X,">"&X4))
Z4:Z25Z4=IF(ROW()-2>U$4,"",INDEX(V:V,MATCH(ROW()-2,Y:Y,0)))
AA4:AA25AA4=IF(ROW()-2>U$4,"",INDEX(W:W,MATCH(ROW()-2,Y:Y,0)))
AB4:AB25AB4=MAX(AH:AH)
AC4:AC25AC4=IF(ROW()-2>U$4,"",RIGHT(AA4,1))
AD4:AD25AD4=IF(ROW()-2>U$4,"",VALUE(AC4))
AE4:AE25AE4=IF(ROW()-2>U$4,"",COUNTIF(Z:Z,Z4))
AF4:AF25AF4=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=AE4,AE4,0))
AG4:AG25AG4=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=1,1,0))
AH4:AH25AH4=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=1,SUM(AG$4:AG4)+1,0))
AI4:AI25AI4=IF(ROW()-2>U$4,"",COUNTIFS(Z:Z,Z4,AD:AD,"<"&AD4))
AJ4:AJ25AJ4=IF(ROW()-2>U$4,"",IF(COUNTIF(Z$4:Z4,Z4)=1,SUM(AF$4:AF4)+MIN(2,AE4),0))
AK4:AK25AK4=IF(ROW()-2>U$4,"",MAX(AJ$4:AJ4)+AI4)
AL4:AL25AL4=IF(ROW()-2>U$4,"",INDEX(AC:AC,MATCH(ROW()-2,AK:AK,0)))
AM4:AM25AM4=IF(ROW()-2>U$4,"",IF(AG4=1,AL4,AM3&"-"&AL4))
AO4:AO25AO4=IF(ROW()-2>AB$4,"",INDEX(Z:Z,MATCH(ROW()-2,AH:AH,0)))
AP4:AP25AP4=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??
 
Upvote 0
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

work.png


tech1.png


tech2.png


AAAI1.png


AAAI2.png
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
146
MARZIOTULLIO
M
D
  • Solved
Summing several columns based on criteria
Replies
7
Views
179
dreid1011
dreid1011
V
  • Solved
Unique value in column A with transposed values from column B
Replies
4
Views
284
alansidman
A
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
436
BrettOlbrys1
BrettOlbrys1
T
  • Question Question
Assistance with Dividing Accommodation Costs by FTE
Replies
3
Views
82
Vogateer
V

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top