Counting Unique Values in a Column of another sheet

Hi there,

I'm looking to count the number of unique IDs in another sheet I'm referencing (that I cannot edit), and put the count by each ID in another sheet I'm using. I'd appreciate any tips that could help me especially keeping in mind I cannot edit the reference sheet (There are examples similar to this but they require editing that sheet). Thanks!

Find more posts tagged with

Sheets

Formulas

Comments

Paul Newcome

How many unique ID's do you have? Will this number change? Are you able to provide some screenshots? Even though you cannot edit, would someone that can edit be able to add helper columns to the other sheet for you?

gsmith21

Thousands of IDs that won't change (some are the same), but more will be added and I need what I'm working on to auto-update with new entries. Unable to provide screenshots, but it's possible I could get someone to add a helper column and hide it, but I would rather not. Are there even possible work-arounds?

Paul Newcome

I can only think of one workaround at the moment, but...

It is extremely cumbersome to build and doesn't easily auto-update for added entries. I will keep thinking on it though.

Paul Newcome

Are all of the ID's in the same column on the original sheet?

gsmith21

Yes

Paul Newcome

EDIT:

I hade to adjust the formulas in the Unique column to include an IFERROR. Unused rows throw an Invalid Value error which was pulling through into the COUNT1 formula.

Response:

Excellent.

Ok. Here is a solution. It will need some tweaking to accommodate how many rows you have, but I'd prefer to test on a smaller scale for now to make sure this solution (with tweaks for scale) will even work for you.

So here goes...

I have 5 columns as follows:

Count (Text/Number)

Unique (Checkbox)

List (Text/Number)

Comma (Text/Number)

String (Text/Number)

Count1: =COUNTIFS(Unique:Unique, 1)

Unique1: =IFERROR(IF(COUNTIFS(List$1:List@row, List@row) = 1, 1), "")

List1: =VALUE(LEFT(String@row, FIND(",", String@row) - 1))

Comma1: 0

String1: =JOIN(COLLECT({Original Sheet ID Column}, {Original Sheet ID Column}, @cell <> ""), ",") + ","

Count2: Blank

Unique2: Dragfill from Unique1

List2: Dragfill from List1

Comma2: =LEN(LEFT(String1, FIND(",", String1)))

String2: =RIGHT(String$1, LEN(String$1) - Comma@row)

Count3: Blank

Unique3: Dragfill from Unique2

List3: Dragfill from List2

Comma3: =LEN(LEFT(String$1, FIND(",", String$1, Comma2 + 1)))

String3: Dragfill from String2

From there you would dragfill row 3 for all columns all the way down until you have covered as many rows as you expect to have. You don't want to stop at where you are now because it won't update when new rows are added to the original sheet.

If you already have thousands and anticipate thousands, you may as well dragfill to the max number of rows of 5,000.

Here's that catch that will require some accommodation:

A cell can only have 4,000 characters in it. Therefore to accommodate 5,000 rows with a single digit ID, we would need to split the data in String1 out into at least 2 (probably more for your specific case) separate cells to avoid going over that 4,000 character limit.

If you test this solution on a small scale and like it, let me know, and we will work on scaling this up for you.

gsmith21

That's quite the solution...I'll give it a shot! Thanks for the help!

Paul Newcome

Happy to help!

Let me know how it goes.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…