Help With UNIQUE FILTER

C

charliechaz

New Member
Joined
May 26, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I almost got it but need the second part based on conditions. In the 'Analysis' worksheet, I7, I have =CHOOSECOLS(IFERROR(IF(E7="","",UNIQUE(FILTER(IAWF!A2:I200,(IAWF!A2:A200=E7)*(IAWF!I2:I200="")))),""),4,5,6,7,9,8). This works when in IAWF!I2:I200 there is no date (null). When there is a date, I'm getting a '#VALUE!' error. So I'd like the formula to consider if there is a date in IAWF!I2:I200, to return the record with the latest date (MAX).
I tried to add IF statement but stuck when combining it with Unique/Filter. Can someone help?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
charliechaz said:
I almost got it but need the second part based on conditions. In the 'Analysis' worksheet, I7, I have =CHOOSECOLS(IFERROR(IF(E7="","",UNIQUE(FILTER(IAWF!A2:I200,(IAWF!A2:A200=E7)*(IAWF!I2:I200="")))),""),4,5,6,7,9,8). This works when in IAWF!I2:I200 there is no date (null). When there is a date, I'm getting a '#VALUE!' error. So I'd like the formula to consider if there is a date in IAWF!I2:I200, to return the record with the latest date (MAX).
I tried to add IF statement but stuck when combining it with Unique/Filter. Can someone help?
Click to expand...
I am confused. The purple text says to return something if there is a date in col I. The red part would only (possibly) return something if col I is empty. They seem contradictory.

What about giving us say 20 rows of dummy sample data from 'IAWF' with XL2BB and tell us what is in E7 of 'Analysis' and give us (again with XL2BB) the expected results in 'Analysis' and explain again in relation to that sample data.

(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)
 
Upvote 0
Peter_SSs said:
I am confused. The purple text says to return something if there is a date in col I. The red part would only (possibly) return something if col I is empty. They seem contradictory.

What about giving us say 20 rows of dummy sample data from 'IAWF' with XL2BB and tell us what is in E7 of 'Analysis' and give us (again with XL2BB) the expected results in 'Analysis' and explain again in relation to that sample data.

(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)
Click to expand...
Thanks Peter_SSs
Here is the mini sheet for the 'Analysis' worksheet:
Sample .xlsx
ABCDEFGHIJKLMNOPQ
1Active Change Control RecordsChange Record Details
2Quality Event > Record IDQuality Event > TitleSelect Change Record: Quality Event > TitleQuality Event > Lifecycle StateQuality Event > Target Implementation DateQuality Event > Due Date
3QE-226820Change 1QE-226820Change 1Plan and Assess30 Apr 2025 
4QE-227409Change 2
5 Impact Assessments
6 Impact Assessment > Record IDImpact Assessment > TitleImpact Assessment > Record OwnerImpact Assessment > Lifecycle StateTask NameTask OwnerTask Due DateTask StatusTask VerdictTask Completion Date
7 IA-047412Assessment 1SueIn Impact AssessmentTask: Impact AssessmentBob29 Oct 2024Assigned 
8 IA-047413Assessment 2JulieIn Impact AssessmentTask: Impact AssessmentSue29 Oct 2024Assigned
9 IA-047414Assessment 3BobClosed#VALUE!
10 IA-047415Assessment 4BobIn Impact AssessmentTask: Impact AssessmentJohn15 Oct 2024Assigned
11 IA-047416Assessment 5JohnIn Impact AssessmentTask: Impact AssessmentBob15 Oct 2024Assigned
12 IA-047417Assessment 6SueIn Impact AssessmentTask: Impact AssessmentBob15 Oct 2024Assigned
13    
14    
15   
16   
17   
18   
19   
20   
21   
22    
23    
24  
25  
26  
27  
Analysis
Cell Formulas
RangeFormula
A3:A4A3=SORT(UNIQUE(FILTER(QEIA!A2:A83,QEIA!A2:A83<>"")))
F3F3=IF($E$3="","",(INDEX(QEIA!$B$2:$B$200,MATCH($E$3,QEIA!$A$2:$A$200,0))))
G3G3=IF($E$3="","",(INDEX('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$C$2:$C$200,MATCH($E$3,'https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$A$2:$A$200,0))))
H3H3=IF($E$3="","",(INDEX('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$D$2:$D$200,MATCH($E$3,'https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$A$2:$A$200,0))))
I3I3=IF($E$3="","",(INDEX('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$E$2:$E$200,MATCH($E$3,'https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$A$2:$A$200,0))))
E7:E12E7=IFERROR(IF($E$3="","",SORT(UNIQUE(FILTER(QEIA!F2:F83,(QEIA!A2:A83=$E$3)*(QEIA!F2:F83<>""))))),"")
G7:G23G7=IF(E7="","",(INDEX(QEIA!$H$2:$H$83,MATCH(E7,QEIA!$F$2:$F$83,0))))
H7:H23H7=IF(E7="","",(INDEX(QEIA!$I$2:$I$83,MATCH(E7,QEIA!$F$2:$F$83,0))))
I10:N12,I9,I7:N8I7=CHOOSECOLS(IFERROR(IF(E7="","",UNIQUE(FILTER(IAWF!A2:I200,(IAWF!A2:A200=E7)*(IAWF!I2:I200="")))),""),4,5,6,7,9,8)
P7P7=IFERROR(IF(E7="","",SORT(UNIQUE(FILTER('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]IAWF'!A2:P178,('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]IAWF'!A2:A200=E7)*(IAWFNI2:R200<>""))))),"")
F7:F12F7=IF(E7="","",(INDEX(QEIA!$G$2:$G$83,MATCH(E7,QEIA!$F$2:$F$83,0))))
F13:F14F13=IF(E13="","",(INDEX('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$J$2:$J$200,MATCH(E13,'https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$I$2:$I$200,0))))
B3:B15B3=IF(A3="","",(INDEX(QEIA!$B$2:$B$83,MATCH(A3,QEIA!$A$2:$A$83,0))))
B16:B27B16=IF(A16="","",(INDEX('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$B$2:$B$200,MATCH(A16,'https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Desktop/[Impact Assessment with Change Control and Documents.xlsx]QEIA'!$A$2:$A$200,0))))
Q22:Q27Q22=IF(K22="","",(INDEX('https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Documents/Templates/[ChangeRecordsTracking.xlsx]Raw'!$J$2:$J$80,MATCH(K22,'https://gileadconnect-my.sharepoint.com/personal/ctirado_gilead_com/Documents/Documents/Templates/[ChangeRecordsTracking.xlsx]Raw'!$F$2:$F$80,0))))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:B100Expression=A3:A101<>""textNO
Cells with Data Validation
CellAllowCriteria
E3List=$A$3:$A$101


Here is the IAWF worksheet:
Sample .xlsx
ABCDEFGHI
1Record IDSource Quality EventWorkflow Completion DateTask NameTask OwnerTask Due DateTask StatusTask VerdictTask Completion Date
2IA-046890QE-22740908 Oct 2024 5:47 PMTask: Impact AssessmentJohn01 Oct 2024CompletedComplete07 Oct 2024 4:28 PM
3IA-046890QE-22740908 Oct 2024 5:47 PMTask: Complete Impact Assessment DetailsBob01 Oct 2024CompletedDetails Completed28 Sep 2024 12:04 AM
4IA-046890QE-22740908 Oct 2024 5:47 PMTask: Approve Impact AssessmentNancy04 Oct 2024CompletedApprove08 Oct 2024 5:47 PM
5IA-046890QE-22740908 Oct 2024 5:47 PMTask: Approve Impact AssessmentBob04 Oct 2024CompletedApprove08 Oct 2024 3:53 PM
6IA-046891QE-22740909 Oct 2024 10:03 PMTask: Complete Impact Assessment DetailsBob01 Oct 2024CompletedDetails Completed28 Sep 2024 12:03 AM
7IA-046891QE-22740909 Oct 2024 10:03 PMTask: Impact AssessmentBob01 Oct 2024CompletedComplete02 Oct 2024 11:21 PM
8IA-046891QE-22740909 Oct 2024 10:03 PMTask: Approve Impact AssessmentNancy04 Oct 2024CompletedApprove08 Oct 2024 9:41 PM
9IA-046891QE-22740909 Oct 2024 10:03 PMTask: Approve Impact AssessmentJohn04 Oct 2024CompletedApprove09 Oct 2024 10:03 PM
10IA-046896QE-22740909 Oct 2024 10:02 PMTask: Complete Impact Assessment DetailsBob01 Oct 2024CompletedDetails Completed28 Sep 2024 2:41 AM
11IA-046896QE-22740909 Oct 2024 10:02 PMTask: Impact AssessmentSue01 Oct 2024CompletedComplete02 Oct 2024 11:09 PM
12IA-046896QE-22740909 Oct 2024 10:02 PMTask: Approve Impact AssessmentJohn04 Oct 2024CompletedApprove09 Oct 2024 10:02 PM
13IA-046896QE-22740909 Oct 2024 10:02 PMTask: Approve Impact AssessmentNancy04 Oct 2024CompletedApprove08 Oct 2024 9:47 PM
14IA-046896QE-22740909 Oct 2024 10:02 PMTask: Approve Impact AssessmentBob04 Oct 2024CompletedApprove03 Oct 2024 9:34 PM
15IA-047412QE-226820Task: Complete Impact Assessment DetailsBob11 Oct 2024CompletedDetails Completed08 Oct 2024 6:01 PM
16IA-047412QE-226820Task: Impact AssessmentBill15 Oct 2024CompletedComplete15 Oct 2024 8:55 PM
17IA-047412QE-226820Task: Approve Impact AssessmentBob01 Nov 2024CompletedReject22 Oct 2024 4:59 AM
18IA-047412QE-226820Task: Impact AssessmentBob29 Oct 2024Assigned
19IA-047413QE-226820Task: Complete Impact Assessment DetailsBob11 Oct 2024CompletedDetails Completed08 Oct 2024 6:01 PM
20IA-047413QE-226820Task: Impact AssessmentSue15 Oct 2024CompletedComplete05 Nov 2024 9:49 PM
21IA-047413QE-226820Task: Approve Impact AssessmentNancy01 Nov 2024CompletedApprove06 Nov 2024 11:17 PM
22IA-047413QE-226820Task: Approve Impact AssessmentBob01 Nov 2024CompletedReject13 Nov 2024 6:36 PM
23IA-047413QE-226820Task: Impact AssessmentSue29 Oct 2024Assigned
24IA-047414QE-22682006 Nov 2024 11:18 PMTask: Impact AssessmentSue15 Oct 2024CompletedComplete05 Nov 2024 9:50 PM
25IA-047414QE-22682006 Nov 2024 11:18 PMTask: Complete Impact Assessment DetailsBob11 Oct 2024CompletedDetails Completed08 Oct 2024 6:02 PM
26IA-047414QE-22682006 Nov 2024 11:18 PMTask: Approve Impact AssessmentNancy01 Nov 2024CompletedApprove06 Nov 2024 11:18 PM
27IA-047415QE-226820Task: Impact AssessmentJohn15 Oct 2024Assigned
28IA-047415QE-226820Task: Complete Impact Assessment DetailsBob11 Oct 2024CompletedDetails Completed08 Oct 2024 6:02 PM
29IA-047416QE-226820Task: Complete Impact Assessment DetailsBob11 Oct 2024CompletedDetails Completed08 Oct 2024 6:02 PM
30IA-047416QE-226820Task: Impact AssessmentBob15 Oct 2024Assigned
31IA-047417QE-226820Task: Impact AssessmentBob15 Oct 2024Assigned
32IA-047417QE-226820Task: Complete Impact Assessment DetailsBob11 Oct 2024CompletedDetails Completed08 Oct 2024 6:03 PM
IAWF


In case we need it, here is the QEIA worksheet:
Sample .xlsx
ABCDEFGHI
1Record IDTitleLifecycle StateTarget Implementation DateDue DateRecord IDTitleRecord OwnerLifecycle State
2QE-226820Change 1Plan and Assess30 Apr 2025IA-047412Assessment 1SueIn Impact Assessment
3QE-226820Change 1Plan and Assess30 Apr 2025IA-047413Assessment 2JulieIn Impact Assessment
4QE-226820Change 1Plan and Assess30 Apr 2025IA-047414Assessment 3BobClosed
5QE-226820Change 1Plan and Assess30 Apr 2025IA-047415Assessment 4BobIn Impact Assessment
6QE-226820Change 1Plan and Assess30 Apr 2025IA-047416Assessment 5JohnIn Impact Assessment
7QE-226820Change 1Plan and Assess30 Apr 2025IA-047417Assessment 6SueIn Impact Assessment
8QE-227409Change 2Closed31 Jan 202528 Feb 2025IA-046896Assessment 7BobClosed
9QE-227409Change 2Closed31 Jan 202528 Feb 2025IA-046890Assessment 8BobClosed
10QE-227409Change 2Closed31 Jan 202528 Feb 2025IA-046891Assessment 9JohnClosed
11QE-227409Change 2Closed31 Jan 202528 Feb 2025
12
QEIA
 
Upvote 0
I hope I used Xl2bb correctly and you can see all three worksheets, formulas, etc.
Per my original post, I'd like the formula in Analysis I7 to return data from IAWF, columns C:I (column C was not included in the original post (I7 formula)) to the aforementioned- Analysis I7.
The Record ID IAWF record to be return is based on the 'Task Completion Date'. If the date is null, return that record. If all 'Task Completion Date' per Record ID are not null, the return the record with the latest date (MAX...I think).
If look at A3 and E7 formulas, these spill, which I like. Can the solution use these formula concepts?
 
Upvote 0
Thanks for getting XL2BB going. (y)
Peter_SSs said:
and give us (again with XL2BB) the expected results in 'Analysis'
Click to expand...
Unfortunately, instead of the above I think that you have given us again a formula that does not give the results that you expect?

Always easier for helpers if we know what we are actually aiming for. ;)

Anyway, see if this gives you what you want or at least puts you on the path to it.
If not, please fill in the expected results manually and give us that with further explanation.

charliechaz.xlsm
EIJKLMN
6Impact Assessment > Record IDTask NameTask OwnerTask Due DateTask StatusTask VerdictTask Completion Date
7IA-047412Task: Impact AssessmentBob29/10/2024Assigned
8IA-047413Task: Impact AssessmentSue29/10/2024Assigned
9IA-047414Task: Approve Impact AssessmentNancy1/11/2024CompletedApprove6/11/2024 11:18 PM
10IA-047415Task: Impact AssessmentJohn15/10/2024Assigned
11IA-047416Task: Impact AssessmentBob15/10/2024Assigned
12IA-047417Task: Impact AssessmentBob15/10/2024Assigned
Analysis
Cell Formulas
RangeFormula
E7:E12E7=IFERROR(IF($E$3="","",SORT(UNIQUE(FILTER(QEIA!F2:F83,(QEIA!A2:A83=$E$3)*(QEIA!F2:F83<>""))))),"")
I7:N12I7=LET(d,IAWF!A$2:I$200,f,INDEX(d,SCAN("",E7#,LAMBDA(a,b,TAKE(TAKE(SORT(FILTER(HSTACK(d,ROW(d)-ROW(INDEX(d,1,1))+1),(TAKE(d,,1)=b)),9),-1),,-1))),{4,5,6,7,8,9})&"",IFERROR(--f,f))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
308
ExcelToDAX
E
M
  • Solved
Filter function condition ideas?
Replies
5
Views
422
KRice
K
john Smith72
  • Solved
Auto filter VBA
Replies
2
Views
360
john Smith72
john Smith72
C
  • Solved
FILTER function to return ONLY records with most recent date
Replies
6
Views
4K
Fluff
Fluff
X
  • Solved
Sumifs is giving me 0 even though it clearly is >0
Replies
5
Views
584
xaikus506
X

Forum statistics

Threads
1,223,881
Messages
6,175,161
Members
452,615
Latest member
bogeys2birdies

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