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([email protected], IF({Sheet# 1 [Date]} = [email protected], {Sheet #1 [Desk # 1]}, [email protected])), 0)), "ERROR")

This function returns "ERROR" every time. Any thoughts/improvements? Would SS Pivot accomplish this same task?

THANK YOU!

-Clay

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/16/21 Answer ✓

    Try this instead...

    =IFERROR(INDEX(COLLECT({Sheet 1 Desk 1}, {Sheet 1 Desk 1}, @cell <> "", {Sheet 1 Date}, @cell = [email protected]), 1), "ERROR")

    thinkspi.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...

    =IFERROR(INDEX({Sheet 1 Desk 1}, MATCH([email protected], {Sheet 1 Date}, 0)), "ERROR")

    thinkspi.com

  • Clayton Richards
    Clayton Richards ✭✭✭✭✭
    edited 06/16/21

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/16/21 Answer ✓

    Try this instead...

    =IFERROR(INDEX(COLLECT({Sheet 1 Desk 1}, {Sheet 1 Desk 1}, @cell <> "", {Sheet 1 Date}, @cell = [email protected]), 1), "ERROR")

    thinkspi.com

  • Clayton Richards
    Clayton Richards ✭✭✭✭✭
    edited 06/17/21

    @Paul Newcome !!!

    I changed the "ERROR" message to "VACANT". Its so beautiful I could cry. Thank you, sir!


    -Clay

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Help Article Resources