Trying to pull unique values from another sheet's column

Faaez Kamaal

Just as the title says, I'm trying to build a solution that parses and lists the "Region Name" field in a sheet, so it can build another list based on "Regional Partner". Basically, I'm trying to build an abstract method of building a list of regions and their associated regional partners. Why? Because these 2 fields seems to change more frequently than I'd like to admit.

Anyway, through some searching, I found Paul's solution; https://app.smartsheet.com/b/publish?EQBCT=416b5082452c49029af303c13250f7da

Problem is, the data isn't in comma delimited cells. It's 20+ rows of the same name.

I'm trying to pull from "Office Directory - 20210223" into "New Chair Installs (Metrics)". I should probably break this off into it's own sheet.

2021-03-16_15-20-27.png

The above shows what I'm trying to achieve, and the below is what I've tried.

2021-03-16_15-10-00.png

2021-03-16_15-09-27.png

2021-03-16_15-08-59.png

2021-03-16_15-11-16.png

2021-03-16_15-10-46.png

2021-03-16_15-07-48.png

I'm at my wits end.

EDIT: I know I'm not the best of explainers. If I can just pull the "Region Name" dynamically, I can use INDEX/MATCH formulas to pull the rest of the info I need.

Faaez Kamaal

Paul, I realized something last night. I need the raw data in the same sheet. It seems that there is no way for me to reference a single cell from another sheet. EG: "={SomeData}1" I think that was my hinderance to this whole process.

When I tried "=IFERROR(LEFT(String@row, FIND(",", String@row) - 1), "")" with "{SomeData}", I got "#INVALID DATA TYPE". And... I think I cracked the code. I need to have all the columns on the sheet and ONLY reference {SomeData} in the "String 1" Column. I'll report back.

EDIT: Never Smartsheet with no coffee and while sleep deprived. I figured it out with that other sheet. Significantly cleaner, and I can reference my other sheet.

Faaez Kamaal

Also, I'm annoyed to have discovered the 1 edit limit. The source sheet is an excel dump from our intranet page, which is pulled a SQL DB. Long and roundabout way, and the connector is being discussed at a high level. Until that happens, I'm forced to manually upload these excel dumps and update the references per upload.

That being said, there are almost 900 rows, and roughly 30 items in the "Region Name" field, and about a dozen items in the "Regional Partner" field. I'd like to not modify the source sheet if I can help it.

Faaez Kamaal

I got what I'm looking for with this: https://community.smartsheet.com/discussion/52886/get-a-list-of-distinct-unique-values

I know there's a significantly cleaner method of doing so.

Paul Newcome

You can get your multiple rows in a comma delimited string by using a JOIN function and setting that delimiter as a comma. From there you should be able to incorporate my solution that you posted above.

For reference, you can see in the published sheet that the formula for String1 is a JOIN/COLLECT/DISTINCT.

Faaez Kamaal

Paul, I realized something last night. I need the raw data in the same sheet. It seems that there is no way for me to reference a single cell from another sheet. EG: "={SomeData}1" I think that was my hinderance to this whole process.

When I tried "=IFERROR(LEFT(String@row, FIND(",", String@row) - 1), "")" with "{SomeData}", I got "#INVALID DATA TYPE". And... I think I cracked the code. I need to have all the columns on the sheet and ONLY reference {SomeData} in the "String 1" Column. I'll report back.

EDIT: Never Smartsheet with no coffee and while sleep deprived. I figured it out with that other sheet. Significantly cleaner, and I can reference my other sheet.