Join Lookup and Show Unique Results Only

So for a Bit of Context;
I added a delivery tab in My app to show all upcoming ‘Container’ Deliveries.
Each ‘Container’ Contains Multiple ‘Pallets’, Each Pallet belongs to a different “Project”.

So I Created a lookup to Show the “Project” for all Related Results, and then a Join, for it to show up as a single line within the ‘Container’.
However, Now it shows up as “7871, 7871, 7369, 7871” Instead of my desired result; “7871, 7369”

Ya—you might need to do a split of those values, then another multiple relation back to the source container, then another join.

Can you please elaborate -
Tried it, but admittedly don’t understand it… How would that result in showing unique results only?

Actually…maybe I misunderstood your request. Does this help:

1 Like

You can do it like this;

Add a rowID column if you haven’t done so.

Create a relation using the “Project” column, relate that to itself, let it be a multiple match.

Have a single value column, let’s name it “First ID”, return the first rowID from the Project relation above.

Have an If Then Else column, if rowID is “First ID”, then return the project number associated with the row.

Finally, using a joined list, you can return all the “First ID” from the multiple relation in step 2.

5 Likes

so I ended up implementing a solution I know best.

  1. I did a Unique formula in a separate sheet =UNIQUE( {Container# , ProjectName} ) which basically lists the Unique Containers and Projects,

  2. then a relation to the Container # in that sheet

  3. Lookup to the Project Name

  4. Joined the Lookup

I did if for 2 reasons, 1. It’s more familiar territory, 2. Row ID might be an issue since the Containers sheet is an IMPORTRANGE.

as for performance, the Unique formula shouldn’t be too heavy and only updates when new containers are added, so it’s not too bad.
I’m wondering what your thoughts are on that.

@ThinhDinh @Robert_Petitto Thank You so much for your help with this!

Ya, that works just fine.

1 Like

I think that’s fine.
I’ve used a similar approach in the past - although I wouldn’t now as I’m comfortable with the option that @ThinhDinh described.

It wouldn’t be, as long as you leave it at the end of the sheet as the last column.

1 Like

It would, though, if the data coming in inserts/removes/rearranges rows

1 Like

I guess it depends what you’re using the RowID’s for.
In this case, the only important thing is that they remain unique - which they would, even if data is added or removed.

2 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.