Insert multiple entries in column based on one value

Question

Hi All,

My problem is as follows.

I have a table that looks like this:

NameAgeFavorite CitiesTom25Seattle  New York City  AtlantaMike29San Francisco  Denver  Chicago  Detroit

Based on one value (in this example that would be the name), I need to add a new column, which I have in a separate table like this:

NameFavorite FoodTomPizzaTomBurgerMikeSpaghettiMikeFish

I now want to pull those two together so that the end result looks like this:

NameAgeFavorite CitiesFavorite FoodTom25SeattlePizza  New York CityBurger  Atlanta Mike29San FranciscoSpaghetti  DenverFish  Chicago   Detroit

This is just an example, my actual data contains hundreds of cases that I need to go through, so doing that manually is hopefully avoidable 🙂

Please let me know if anyone has any ideas 🙂

Thank you!

AMansour · Accepted Answer

Hi @scollier1993

For the first table :

I'll first complete the Name field as I'll use it in the join process later.

Then I'll add rank by Favorite Cities that I'll use also in the join

for the second table :

I'll add rank by Favorite Food

finally I'll join the two table using 'Name' and 'RANK' as join key.

Hoping that's will avoid you this lazy manual process

Cheers

AbhilashR · Accepted Answer

Hi @scollier1993, attached is an implementation of the logic suggested by @AMansour.

Please let us know if you have questions.