UNIQUE FILTER

C

charliechaz

New Member
Joined
May 26, 2020
Messages
13
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 a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
Thank you Peter_SSs. The formula in I7 worked beautifully. It uses advanced functions that I was unfamiliar with.
As I am eager to learn, wondering if you would be able to explain in the table below:
=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))
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))
I assume the LET function assigns the 'd' name to the IAWF!A$2:I$200 array. What is "f"? I see f at the end wrapped in an IFERROR function, "IFERROR(--f,f))". Does "f" represent "null"?I assume that this is looking for the E7 value within the IAWF!A$2:I$200 array. What does the "#" do?


Here , I'm lost. Never used LAMDA, TAKE or HSTACK before.
Can you also explain the (d,ROW(d)-ROW(INDEX(d,1,1))+1), portion as well?		As before, never used TAKE. I understand "d" but what is "b"?
Can you also explain what this portion is doing in regards to my workbook?		I assume this portion is similar to CHOOSECOLS. Is "f" null?

Why double minus signs?
<explanation><explanation><explanation><explanation><explanation>

I WANT TO LEARN! I am introducing two new worksheets and would like to write a similar formula. Similar to the IAWF (Impact Assessment Workflow) sheet, the first new sheet (IADOC) has the documents identified within the Impact Assessment records. Within the Analysis worksheet, I have written the following formula in P3:
=IFERROR(IF($E$3="","",SORT(UNIQUE(FILTER(IADOC!G2:I197,(IADOC!A2:A197=$E$3)*(IADOC!I2:I197<>""))))),"")
This works ok...for now.
The second new worksheet is a bit more daunting. The DOCWF sheet has data, related to Document workflows. I am in the process of scrubbing data for this sheet. The problem here is that unlike the IAWF, where each Impact Assessment record has a unique IA record ID, the DOCWF sheet has unique Document Numbers, but the workflows do not. I am in the process of scrubbing and may ask for help later on this.
In the meantime, I'd like to understand the details of the formula you provided to attempt to write them on my own.
Once again, thank you for the help provided thus far. You are brilliant!
Thank you,
Charlie
 
Upvote 0
charliechaz said:
The formula in I7 worked beautifully.
Click to expand...
Good news. Thanks for the confirmation.

charliechaz said:
wondering if you would be able to explain in the table below:
Click to expand...
This is about the best I can do within a reasonable time period. 😎

charliechaz.xlsm
ABCDE
1LET(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))
2I assume the LET function assigns the 'd' name to the IAWF!A$2:I$200 array. What is "f"? I see f at the end wrapped in an IFERROR function, "IFERROR(--f,f))". Does "f" represent "null"?I assume that this is looking for the E7 value within the IAWF!A$2:I$200 array. What does the "#" do?Here , I'm lost. Never used LAMDA, TAKE or HSTACK before.As before, never used TAKE. I understand "d" but what is "b"?I assume this portion is similar to CHOOSECOLS. Is "f" null?
3Can you also explain the (d,ROW(d)-ROW(INDEX(d,1,1))+1), portion as well?Can you also explain what this portion is doing in regards to my workbook?
4Why double minus signs?
5Correct regarding 'd'E7 contains a dynamic array formula that 'spills' results down the column. In my case the E7 formula produces 6 results that spill from E7 to E12 E7# refers to the whole spill range not just E7. So in my example E7# means E7:E12Too complicated to explain simply here. You probably need to look in the Microsoft Help about the functions that you are not familiar with and also search this forum (or elsewhere on the internet) for other, perhaps simpler, examples of the functions so that you can become more familiar with them. It isn't an instant process.See previous columnYes, this is like CHOOSECOLS No, f is not null. Refer to A6 at left.
6LET also assigns the 'f' name to the result(s) of the next section of the formula which is INDEX(d,SCAN("",E7#,LAMBDA(a,b,TAKE(TAKE(SORT(FILTER(HSTACK(d,ROW(d)-ROW(INDEX(d,1,1))+1) Using f saves repeating that whole calculation twice at the end of the formula. The IFERROR part at the end turns text numbers into actual numbers and leaves other text as it is.Double minus coerces text numbers into actaul numbers.
Explanation
 
Upvote 0
You must log in or register to reply here.

Similar threads

john Smith72
  • Solved
Auto filter VBA
Replies
2
Views
372
john Smith72
john Smith72
X
  • Solved
Sumifs is giving me 0 even though it clearly is >0
Replies
5
Views
592
xaikus506
X
C
  • Solved
FILTER function to return ONLY records with most recent date
Replies
6
Views
4K
Fluff
Fluff
M
  • Question Question
Running unique count of occurrence on table based on date
Replies
0
Views
357
Minx_Minxy
M
D
  • Question Question
Adapt IF AND ISBLANK formula combination in relation to UNIQUE FILTER on another sheet
2
Replies
15
Views
1K
KRice
K

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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