Transforming a joined list of keys into a joined list of values

I’m having a mental block with this one.

Imagine I have a table containing “things”. Something like this:

Screen Shot 2021-04-19 at 8.25.38 PM

In a second table, I have a column that contains a joined list of “keys”. Like so:


Important point to note is that my joined list contains duplicate keys.
What I want to do is transform that joined list of keys into a joined list of values, whilst maintaining the duplicates. So the end result should look like:


I can create a Spilt Text column, then use that to create a multi-relation, and then use another joined list to get the values through that relation, BUT… I lose the duplicates.

I feel like this should be trivial. What am I missing?

Won’t scale well, but using a template column is the most direct option in my opinion.

A template column, how?

Speaking of scale, my real joined list is dynamic, and could contain thousands of keys…

If it’s thousands then this won’t work haha. I was thinking if you have a small number of items then just replace the numbers directly by the text, which is already cumbersome (as we can’t replace by a “custom text”.

1 Like

mmm, where is @Jeff_Hager when you need him? :rofl:


Got it, I’ve already tried to do it, but no success yet.

Like =UNIQUE formula in GSheets, right?

No, no - unique is easy. I want the opposite of unique :stuck_out_tongue_closed_eyes:

1 Like

hola @Darren_Murphy,

Do you need anything like this:

to have this?

let me know if this works for you although I think i can narrow the final list to be based on customer only…


yes… no… maybe, I’m not sure. It’s a bit difficult to tell.

Maybe if I state my challenge a little differently.

Imagine that you have a column that contains a big long list of Row IDs from your User Profiles sheet. Maybe 100, or maybe 500… whatever. And the same Row ID can be there multiple times.

Now how would you transform that joined list of Row ID’s into a joined list of User Names, with every User Name replacing the corresponding Row ID, INCLUDING the duplicates? So if the original joined list has Row ID “xyz” 25 times, then the User Name that corresponds to Row ID “xyz” should be in the new joined list 25 times.



let me reread your problem , I was looking for another way using another point of view.

Oh yeah… the opposite :upside_down_face: maybe I read upside down

1 Like


and now?

This would be your list where to search

and here you have what you want … I think :upside_down_face:

Does it work for you?


I still can’t tell if that’s a solution or not. Where is the joined list of IDs in your example (ie. my starting point)?

It needs to start with this:

And end with this:

And that should be in a single row

Well, if you want to have your list based on ID, here it’s

but at the end: do you want to have in your final list (my jl_Final List column) the same sequence/order showed in your 2nd table? It’s quite difficult!

If you are going to create a chart based on fruit ID’s qty, I don’t see the problem with my jl_Final List column? The order in the list doesn’t matter. :innocent:

Saludos Darren

The order is not important. Maintaining the same count of each element is important.
But I think you’re still not understanding the challenge.

  • You have ONE row in your table
  • In that row is a single column, containing a joined list: 1,1,1,3,2,1,2
  • The challenge is to change that Joined List of numbers (keys) into a Joined List of names (values)

The keys->values mapping is available in another table, ie:

Screen Shot 2021-04-19 at 8.25.38 PM

My backup plan is to do this with a script, but it’s a bit frustrating that I can’t find a Glide solution, because it seems such a simple thing to do. :persevere:

I’ve been trying to think about this today, but nothing is coming to mind yet. How is your original joined list of keys created? I wonder if it would just be easier to create the joined list with names instead of keys to begin with.


Thanks Jeff.
Yes, it would be much easier to do that.
The catch is that these need to be translated into multiple languages on the fly…

The example I gave was deliberately simple so as to demonstrate the crux of the challenge. The real life scenario is quite a bit more complicated :slightly_smiling_face:

1 Like