Return unique values while ignoring blanks.
Hello!
I'm trying to find a function similar to vlookup or index that will return a unique value where there are duplicate search values. In this example, individuals book a desk on a given date. Since form submissions create a new row, the bookings flow in like this:
Sheet # 1
My goal is now to collect these bookings on a separate sheet, this time organizing bookings by date. For example:
Sheet # 2
Since there are multiple bookings on the same date, traditional vlookup functions, data mesh, and others I've tried do not seem to work. I last attempted this function that I borrowed from a google search to extract only unique values. Working only within the "Desk 1" column, for now. I replaced named ranges w/ column names here to help things make sense.
=IFERROR(INDEX({Sheet # 1 [Desk 1]}, MATCH(0, COUNTIF(Date@row, IF({Sheet# 1 [Date]} = Date@row, {Sheet #1 [Desk # 1]}, Date@row)), 0)), "ERROR")
This function returns "ERROR" every time. Any thoughts/improvements? Would SS Pivot accomplish this same task?
THANK YOU!
-Clay
Best Answer
-
Try this instead...
=IFERROR(INDEX(COLLECT({Sheet 1 Desk 1}, {Sheet 1 Desk 1}, @cell <> "", {Sheet 1 Date}, @cell = Date@row), 1), "ERROR")
Answers
-
Try this...
=IFERROR(INDEX({Sheet 1 Desk 1}, MATCH(Date@row, {Sheet 1 Date}, 0)), "ERROR")
-
Thanks so much, @Paul Newcome !
This is closer to the solution, but doesn't capture everyone. I suspect that the duplicate search values on sheet #1 are still throwing it off. Is there any way around that?
Results from testing your function on sheet #2:
Thanks again! Hoping there's a way to only pull out the unique values among all of the blanks for each date on sheet #1.
-Clay
-
Try this instead...
=IFERROR(INDEX(COLLECT({Sheet 1 Desk 1}, {Sheet 1 Desk 1}, @cell <> "", {Sheet 1 Date}, @cell = Date@row), 1), "ERROR")
-
@Paul Newcome !!!
I changed the "ERROR" message to "VACANT". Its so beautiful I could cry. Thank you, sir!
-Clay
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!