Using DISTINCT function

Sarah_lee123

I am trying to use the distinct function. It seems like it should be relatively simple, but I am getting an error message. Am I doing something wrong?

Thanks

Paul Newcome

The DISTINCT function is meant to be used within another function almost as a filter.

=JOIN(DISTINCT({Range}), ", ")

=COUNT(DISTINCT({Range}))

Sarah_lee123

Ok, not sure if I'm using the correct formula. I was trying to replicate the "UNIQUE" function from excel/google sheets for an inventory tracking system.

An item will be received into the system, and sold from the system, multiple times, but I want to pull in one item name from a list where it may be repeated. for example, I may receive and sell a bottle of ketchup 20 times, but I want my inventory list to reflect the ketchup on one line item. From there, I will use sumifs function to total all ketchup that was received and sold, to obtain a total qty on hand (remaining inventory).

In the screenshot example above, you can see that there are two names that are repeated multiple times. I am trying to pull the two names into the new sheet so that the new sheet only has two rows. One, for GELATO 44 and one for SOUR D. If another name was in the source shet, then that name would populate as well. This is how UNIQUE function works in Excel, but I can't seem to replicate that here in Smartsheet.

If I use COUNT(DISTINCT) then I will get a number of times the item was repeated. In the screenshot example I would get the number "4" rather than the name "SOUR D". I want the name to appear once, not counted.

I don't think the "JOIN" feature is relevant here either as I am not combining info from multiple cells.

I have looked through all of the formulas and can't figure it out. Should I be using the COLLECT feature with DISTINCT?

Let me know if all that makes sense or if I should clarify more with screenshots.

Thanks!

Sarah_lee123

I was able to make some headway by copying a formula I saw in a different thread @Genevieve P. responded to.

=JOIN(DISTINCT(COLLECT({Jira Key}, {Jira Key}, <>"")), ", ")

Which I customized to my situation

=JOIN(DISTINCT(COLLECT({RECEIVED Range 2}, {RECEIVED Range 2}, <>"")), ", ")

But I end up with a list within one cell, but I really want one answer per cell rather than all answers within one cell.

The top cells are a copy/paste which is NOT how I want this sheet to populate. Whereas, the cell with a red circle around it is what the formula returned to me.

Genevieve P.

Hi @Sarah_lee123

There currently isn't a formula that can parse out all the unique references down multiple rows as individual values; the JOIN formula you've used above is what would be able to bring in distinct values, but it brings it into one combined cell, as you've found.

In this instance I would recommend using a Report instead of using formulas in Sheet.

With a Report, you can Group by the Barcode number which will automatically bring together all of your unique values. Then you can use a Summarize function to SUM a different column, giving you a total for that Grouping. Here's a webinar that goes over these two report functions: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

Paul Newcome

@Genevieve P. I have seen a few different parsing solutions here in the Community. I just can't seem to locate them right off. If I remember correctly, there is one floating around here that uses an INDEX based formula that is definitely more efficient than the one I posted some time ago.

I do agree though that grouping and summary fields in a report is going to be the easiest route to go for pulling this data.

Genevieve P.

Yes, you're right - I think it was using INDEX and then identify if you're finding the 1st instance or the 2nd instance or the 3rd... etc... but in this case you would need to know how many there are, and it would require changing the numerical value in the INDEX. (This post?)

You could have a column in the second sheet that lists the number (1, 2, 3...etc) then reference this column in the INDEX, but again, you'd need to know how many distinct values you have.

It's possible but a bit tricky to manage as the number of values changes...I think a Report could be a much smoother way to reach Sarah's end goal, but perhaps not!

@Sarah_lee123 Let me know if Reports will work for you 🙂

Paul Newcome

That one requires getting numbers lined up on the first sheet. I think it was @L_123 that had one where we actually worked from a delimiter. I could have sworn I had it saved...

Having a "Number" column and then referencing that instead of manually changing the number in the formula would work, and you can always build in room for expansion.

But yes... A report with grouping and summaries is definitely the easier way to go.

I know I have used the parsing solution before because you can't group on a multi-select dropdown or contact type column. I will dig around and see if I can find it.

Paul Newcome

Genevieve P.

https://media.giphy.com/media/xUySTu7FXFYo6nXWWQ/giphy.gif

Thanks Paul!!!

Paul Newcome

I forgot I had built my own example reference sheet just so I wouldn't have to dig through old posts. Hahaha.

L_123

Man, this is a bit of a blast from the past. I actually never used the sheet Paul is referencing after developing it, even though I had about 80 hours into it. Couldn't get it to work fast enough with 26 combined reference sheets. That was just before move workflows came out and I could convert formulas to static values

That said, I was looking not just for unique values, but values that fit about a dozen other characteristics, and from multiple sources. I think you could get away with simply indexing the distinct right?

=index(distinct(people:people),1)

=iferror(index(distinct(people:people),1+count(above$1:above1)),"")

Paul Newcome

@L_123 I never thought to index distinct directly. I have officially updated my own reference sheet.

That being said... I may still hang onto the previous formulas leveraging the delimiter for solutions where I need to grab unique entries from a multi-select dropdown/contact type column.

Sarah_lee123

I'm still lost on this one. Maybe I just need to play around with it an understand the difference between INDEX, MATCH, DISTINCT, etc, but it seems like Smartsheet might no be capable of what I'm asking. I am able to get it to pull in one and only one of the barcodes with the function

=index(distinct(people:people),1)

But I don't understand how the =iferror(index(distinct(people:people),1+count(above$1:above1)),"") equation works with my situation.

I see it can work if I'm pulling from within a sheet, but I am trying to pull from one sheet (many entries) into a second sheet (condense down to one UNIQUE cell per entry type)

It is a quick and simple equation in excel that isn't translating cleanly to Smartsheet.

Unfortunately, for my purposes, I need it to be in a sheet not a summarized in a report.

Here are some screenshots from the you tube video that I am trying to copy.

You can see the formula in the first photo. The repeated data in the second photo, and the summarized list in the third photo.

I'll keep playing around, but if you have any other ideas, let me know.

Thx

Paul Newcome

@Sarah_lee123 Take this formula as the base:

=index(distinct({Range you want to pull from}),1)

Insert a text/number type column (called "Number") on the sheet you are putting the formula on. Manually enter the numbers 1 through whatever number you need. If you anticipate 50 unique entries, 1 -75 can be entered rather quickly and gives you room for more than you expected just in case. If you expect 1,000 unique entries, maybe populate 1,050.

Finally we are going to modify the above formula and replace the 1 with a reference to the Number column.

=index(distinct({Range you want to pull from}), Number@row)

Sarah_lee123

@paulnewcome and @Genevieve P.

You are amazing and brilliant!

That worked, and I also watched a webinar on the index/match vs vlookup and that was so helpful too.

Thanks for sticking with me in this one.