I have several columns which I'm trying to extract unique values from if row meets a criteria.
Providers by name are in columns A, C, E, G, I and K.
Services they provide are in columns B, D, F, H, J, L, N, O, P, Q, R, S, T, U, V and W.
When a provider name is selected in "A12" and it matches agencies in provider name columns, I want to filter unique services from all service columns. My formula in "Y3" shows an error but I don't know why......
Please mock up the results you are expecting. I cannot work out from the formula what you are expecting it to return.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
try
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Another optionFormula:Please Login or Register to view this content.
This did not filter the services in columns N thru W
This also did not filter the services in columns N thru W
See attached AliG. I changed the filtered Agency to "GEORGIA MOUNTAINSCSB".
My desired output is in column "AC".....
Try this,
Please Login or Register to view this content.
I'm going to simplify my ask:
If "A3" occurs in ProviderName1 to ProviderName3, values in same row of columns Service Group Name 1 thru Nursing Services should be filtered/sorted/unique.
Have you tried the formula windknife suggested? It appears to do what you asked for.
Another formula.
Formula:Please Login or Register to view this content.
Excel 365 (Windows) 64 bit
Community Access Community Access - Group Community Access - Individual CRA CRA - Category 2 - Host Home Nursing Services Nursing Services - RN Occupational Therapy Occupational Therapy - Evaluations Specialized Medical Supplies
Sheet: Sheet1
I am not too familiar with variable assignments. Can the responses use actual columns rather than references so I'm able to recreate as needed?
I uploaded a new file and need a response on this file only.
If State matches "A3", then I want just all colors in that row to be sorted/unique/filtered into Colors if State selected column. See Desired Outcome column. If criteria not met, then Colors (ALL) list shows.......thanks
It's AliGW. Please don't address me as Sacha Baron Cohen's alter ego!See attached AliG.
Try this:
=UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=A3),1)))
If you want to know the cells referenced, then convert the table to an array. I have done this underneath your table and used this formula:
=UNIQUE(SORT(TOCOL(FILTER(D11:G17,C11:C17=A12),1)))
That worked! Thanks AliGW.
How to modify so to show Colors (ALL) if criteria NOT met?
Maybe:
=IFERROR(UNIQUE(SORT(TOCOL(FILTER(D11:G17,C11:C17=A12),1))),UNIQUE(SORT(TOCOL(D11:G17,1))))
Sorry AliGW but that didn't work......
I'm trying to get a modification to the following formula:
=UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1)))
Formula currently sort/filters all unique values in columns Color1 thru Color4 if State column matches criteria from "A3"....pretty straight forward.
What I need: If criteria is not an option in State column then Colors if State selected should revert to Colors (ALL).
Maybe this:
=IF($A$3="",UNIQUE(SORT(TOCOL(Table2[[Color 1]:[Color 4]],1))),UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1))))
Your other thread has been closed, as you are merely requesting an amendment.
Last edited by AliGW; 03-25-2024 at 09:44 AM. Reason: Typo fixed.
Or maybe this:
=IFERROR(IF($A$3="",UNIQUE(SORT(TOCOL(Table2[[Color 1]:[Color 4]],1))),UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1)))),UNIQUE(SORT(TOCOL(Table2[[Color 1]:[Color 4]],1))))
Thanks AliGW for fixing my duplicate thread error.
That formula only works if $A$3 is empty but looking for any situation that does not match $A$3.
Like this one but with TOCOL to filter several columns:
=SORT(UNIQUE(FILTER(Table2[Color 1], Table2[State]=$A$3, SORT(UNIQUE(Full_Service_List[Colors (ALL)])))))
You obviously didn't look at the file attached to post #20. Look at that first, please - it allows for a mismatch OR the cell being empty.
If that does what you need, then it can be condensed a touch:
=LET(u,UNIQUE(SORT(TOCOL(Table2[[Color 1]:[Color 4]],1))),uf,UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1))),IFERROR(IF($A$3="",u,uf),u))
For future reference, this:
was of no help at all. If you are going to state that something doesn't work, you need to say in what way: wrong result and what it should be, error message, misinterpreted requirement. "Didn't work" tells your helper nothing useful. And it was the middle of my night when you posted that, in case you were expecting an immediate response ...Sorry AliGW but that didn't work......
Error output should be Full_Service_List table and not Color1 thru Color4, which is what I got from that formula.
I tried to modify it with:
=IFERROR(IF($A$3="",UNIQUE(SORT(TOCOL(Table2[[Color 1]:[Color 4]],1))),UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1)))),UNIQUE(SORT(Full_Service_List[Colors (ALL)])))
but now output does not work when criteria is met........
OK - this, then:
=LET(u,UNIQUE(SORT(Full_Service_List[Colors (ALL)])),uf,UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1))),IFERROR(IF($A$3="",u,uf),u))
Just caught an error - try this instead:
=LET(u,UNIQUE(SORT(TOCOL(Full_Service_List[Colors (ALL)],1))),uf,UNIQUE(SORT(TOCOL(FILTER(Table2[[Color 1]:[Color 4]],Table2[State]=$A$3),1))),IF(ISNUMBER(MATCH(A3,Table2[State])),uf,u))
Still no. Output does not work if criteria IS met......
Try post #26.
Yep that's the winner...thanks
Great - got there in the end.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks