
Count Number Of Unique Values In A Single Cell Separated By Commas


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Count Number Of Unique Values In A Single Cell Separated By Commas  Excel

View Answers


Hi, probably a very easy one here, im looking to count the unique number of values within a single cell string which are separated by commas
example:
Cell A1 contains: 1,2,3A33,444,2,1,16
The resulting formula would give a count of 5 unique value within cell A1 as values '2' and '1' are repeated twice within the cell and would therefore be excluded.
Any help would be most appreciated, thank you!
Similar Excel Video Tutorials
Count Unique Or Duplicate Records
 See how to Count unique records and Duplicate records using the SUMPRODUCT, COUNTIF, COUNT, COUNTA, IF and SUM functions. See seven (6) different meth ...
Count Matches in Single Cell
 See how to use SUMPRODUCT, MID, SUBSTITUTE, and array constant in a formula to count the number of matches of 9 values entered into a single cell. See ...
Similar Topics
Hi Everyone,
Thanks to this forum, I've learned a neat CSE formula that calculates the number of unique values in a list, namely:
=COUNT(
1/FREQUENCY(
MATCH($A$1:$A$50,$A$1:$A$50,0)
),
ROW($A$1:$A$50)ROW(OFFSET($A$1:$A$50,0,0,1,1))
+1))
What I love about this formula is that it is highly efficient  it's one formula in one cell.
Now, I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.
So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.
Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.
I'm looking for the formula that returns 5.
And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.
Thanks for your input!!
Jay
Sorry if that's a bit long winded...
Here's how it breaks down.
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
Does that make sense or should I resubmit?
I've tried a few different ways but can't come up with anything...
There's more to the project, but this bit will help me get it off the ground and hopefully I'll be able to string the rest together.
Cheers guys! Great forum!
I have a single cell with 4 values in it all separated by commas and i would like to pull out each one separately. The number of characters in each value changes. Hopefully someone can help.
Thanks,
Adam
Hello,
I am trying to find a formula to count the unique values in a cell and some of the unique values contain spaces within them; the ending of each value is indicated by an underscore. The data looks something like this (A# denotes columnrow):
A1 T2_T2_T2_T2_
A2 T3_T3_T3SE B_T3SE B_
A3 T3_T3_T3SE B_T3SE B_
A4 T3_T3_T3SE B_T3SE B_
A5 T3_T3_T3SE B_T3SE B_
A6 T3_T3_T3SE B_T3SE B_
A7 T3_T3_T3SE B_T3SE B_
A8 T3_T3_T3SE B_T3SE B_
A9 T3_T3_T3SE B_T3SE B_
A10 T3_T3_T3SE B_T3SE B_
A11 T3_T3_T3SE B_T3SE B_
A12 T3_T3_T3SE B_T3SE B_
A13 T3_T3_T3SE B_T3SE B_
A14 T3_T3_T3SE B_T3SE B_
A15 T3_T2_T2_T3
Column B will return the number of unique values. So B1=1, B2=2, B3=2, etc.
How can you count unique values within a CELL, rather than in a range? These values are concatenated, so it is no problem changing the underscore to another symbol/value.
Thanks very much,
Kim
Hi All,
I've got a very large text file with values separated by line breaks and commas that I would like to render into a spread sheet with new columns separated at the commas, and new rows separated at the line breaks.
The issue I'm having is that the values for some of the cells are too large for Excel to keep in a single cell.
Does anyone know of any workarounds? If not, it's not the end of the world, I think I can find another solution to my larger project that does not involve a spreadsheet.
Thanks!
Conor
Hi,
Does anyone know any easy formula to count the number of UNIQUE values in a range of cells?
Note that the values include both numbers and text. Ideally, the solution could be implemented in a formula contained within a single cell.
Here is an example of the data and solution
ABC
ABC
123
DEF
DEF
456
456
456
Solution = 4
Thanks!
I have a sheet with many entries in each row. Some match others don't...i want to be able to highlight the populated cells in the row, and concatenate a string of unique values separated by commas and ready to paste into another workbook.
any takers?
JB
This might not be possible with out a VB function, but I figured I would ask the experts.
Basic problem is that I have a column of data that can have a single identifier or more then one identifier in a cell with a separation of a coma. I need a way to count up all the unique items listed, but needs to be able to tell if the values in the cell are unique not the whole cell.
Example:
Data
1
3
11,5
6,7
8
Count Result = 7 since there is no 2 or 4 in the list and 11 is counted as a whole number insted of 1 and 1.
Hi
I am after something that seems simple, but I can't figure out a way to do it.
If I have a single cell containing a list of values seperated by a comma (ie a csv list in a cell), how do I count the number of values. I figure it must be by counting the commas somehow and then adding one as the last doesn't have it, but there is no way to tell how many there will be.
My cell contents look like this
18911, 18801, 18825, 18482, 18941
so I want to have another cell return the number 5.
Does anyone have any ideas?
Regards
Sandi
I can get a count for the number of cells within a column or row, but how can i find out how many unique values there are?
ie
4
5
6
7
6
5
8
9
While a count will give me 8 values, I only want to return 6 for the unique references
Hi, I am wondering if it is possible to count the number of unique values in two columns greater than some other value. To clarify, if I have two columns (A and B) with the values listed below.
A B
3 6
2 3
4 2
6 8
5 10
I want to count how many unique values between the two columns are less than 5. In this case, there should be 3 (2,3 and 4 from column A as well as 2 and 3 from B fulfill this condition). Is there any way to do this efficiently without using VBA? I have searched this forum and a few others and have seen SUM(IF(FREQUENCY(...)) being used in single column counts, but I don't know how to adapt this to multiple columns of data. Thanks.
Hi,
I need help performing a lookup function with inputs in one cell separated by commas.
I have a single cell with a given number of initials (my lookup values):
RY, AH, NR
I have a table with a cost assigned to each of these initials (my lookup array):
RY 200
AH 200
NR 400
I would like to perform a vlookup off the cell containing the comma separated values to generate an output such as 200, 200, 400.
I would also like the function to be flexible enough to provide outputs based on any given number of inputs in the cell such as
RY, AH, NR, WG or
RY, WG
Thank you so much for anyone's help on this!
There are ways to create intermediate steps to solve this problem but I want do this as a single formula in a cell.
I have a single column of values that contain some unique values that repeat a couple times. I am interested in one specific value in the string that determines a grouping. This section of the string value is represented in the Mid part of the equation below. These values range from 1 to 9. So I would like to see how many of each one of those mid values are contained in my whole column. I thought that I could create if statements and then count them as solution. My problem is that I I found that to get the below equation to work accurately I would have to enter a new if / mid statement for every row. I have 118 rows and 7 unique values so I am looking for a shortcut, some kind of a loop.
=COUNT(IF(MID(AH504,2,1)="5",1,0),IF(MID(AH505,2,1)="5",1,0),IF(MID(AH506,2,1)="5",1,0))
Thanks,
I have an excel sheet that has multiple rows for one PO number. I've created a row at the top to calculate all of the unique PO numbers in the sheet. However, I want to create another column that will give each unique PO number it's own ID number starting at 1. For instance:
Column B (PO Number)
4  Counts unique values in B
219
219
219
220
220
221
222
222
Then in Column C I'd want
1 (unique number for 219)
1 (unique number for 219)
1 (unique number for 219)
2 (unique number for 220)
2 (unique number for 220)
3 (unique number for 221)
4 (unique number for 222)
4 (unique number for 222)
Any ideas? If I'm not making sense just say so and I'll try to clarify.
Hi,
I'm stumped on this one. Attached is an excel file where I have 4 columns of data (B:E). In cells K3:M19, I need a formula that will find the those VIDs (column B) that match the VID in column J and then count the corresponding number of unique number of nonempty cells for the GDA, BN, and HT. I need to be able to drag it down and over to the right.
The formulas will reside in cells K3:M19. Also, I would like to concatenate the unique values to the count. For example, the VID in cell J10 we would have something like the following: "2:NinjaTurtle".
Does that make sense?
Thanks so much!
What I have a cell with values separated by , (commas).
Ex: Apple, Orange, Pea
The number of values will vary per worksheet and what I would like to do is separate out those values into individual cells. I know i know  text to columns... :o Slow down  there is data in the adjacent cell that I don't want to overwrite.
What I would like to do is count the occurances of the , (comma separating the values) and insert that number+1 columns to the right of it.
If that wasn't enough  I would then like to use a vlookup to look up those indiviual results and return another value (from a different worksheet).
So I have:
Cell A1=Banana, Apple, Orange;
Cell B1=Data I Need
What I want:
Cell A1=vlookup(banana);
Cell B2=vlookup(apple);
Cell C3=vlookup(orange);
Cell D4=Data I need (from cell b2...ZZ2)
Any suggestions on where to begin  is there a count feature that will count indiviudal occurances of the comma in the cell that I could then use to get the number of columns to insert?
Thanks for any and all help
Say in Cell A1, I have numbers separated by commas, like this:
123, 435, 456, and so on, many, like over 100 like this in a single cell,
I would like to paste those values so that I have 100 values spread over single cells, they would go like this:
B1: 123
B2: 435
B3: 456
Has anyone figured this out ?
Manual way and VBA examples would be nice, thanks for your time!
Dear Most Amazing Excelrs In The World,
I am trying to count the unique values that are greater than 30 in the range A1:A5. The values a
30
45
45
90
1
The answer should be 2 because 45 and 90 would be counted.
I have successfully created a few solutions with a Pivot Table and Advanced Fliter, but when I try to do one Array formula in one cell, I have no luck.
Any ideas about how to count the unique values that are greater than 30 in the range A1:A5 with an array formula?
Need a formula in Col AF to count the number of unique digits, without counting duplicate digits. The 2digit values are to be treated as 2 separate values. If the value has a leading zero, (example AA1= 06, don't count zero. All 2digit values 10,20,30, count the zero).
The unique digits below are 1,2,3,6,8 for a total of 5.
06 18 23 28 32 5
UNIQUE VALUES
Z
AA
AB
AC
AD
AE
AF
1
06/11/10
06
18
23
28
32
5
2
06/10/10
01
14
20
22
35
6
3
06/09/10
02
09
10
17
20
6
4
06/08/10
11
15
24
27
34
5
06/07/10
01
08
18
25
27
Excel 2007
HI
I am trying to create an array formula:
I have account numbers in column A and values in column B (The 1 could represent any number)
102 0
102 0
103 1
103 1
104 0
104 0
104 1
I would like to count the number of Unique account numbers in A if the value of B is 1
ie the correct answer should be 2
as I am using 2 account numbers
I have 2 formulas to count the number of unique values
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))
and an array formula
{=SUM(1/COUNTIF(A1:A7,A1:A7))}
both return the number of unique value in column A which in this case is 3
but am having problems trying to link column B to column A
ie when column B shows a zero in a cell do not include its corresponding cell in Column A in the unique count.
Can anyone help me
Kind Regards
Thomas
I'm not completely sure if I am dreaming too big here, so please let me know if my idea is a bit ludiculous.
I have a spreadsheet that contains two columns (columns "O" and "P") with serial numbers in them. The serial numbers in these two columns are separated by commas.
I wrote some script to calculate how many commas are in these last two columns. I stored this number in a variable named 'commas' as such.
Lastcell = Cells(Rows.Count, "O").End(xlUp).Row
For i = Lastcell To 1 Step 1
If (Cells(i, "O").Value) "" And Rows(i).EntireRow.Hidden = False_ Then
commas = Len(Cells(i, "O").Value)  Len(Replace(Cells(i, "O").Value, ",", ""))
So, I have my 'commas' variable. What I would like to do is perform a "text to rows" sorta deal. I'm trying to give each serial number its own unique row but have it contain the same information as the columns that come before it. The comma would be used as my delimiter in this particular case.
This process needs to be performed for column "O" first then column "P."
So the jest of it, each serial number (in column "O") gets its own row, and the information stored in columns 'A' through 'N' is right there with it. Then this process needs repeated for column 'P' whilst preserving the the information in columns 'A' through 'N' again.
Needless to say, any help would be greatly appreciated. If any clarification is needed please say so.
I know that to join the contents of several cells into a single one (concatenate is the technical word), all it takes is the function & and that to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4
My problem is that sometimes some of these cells are empty so I end up with some values separated by commas, empty spaces and more commas without more values in between.
What would be the way to concatenate only the nonempty cells?
Thanks
Greetings,
Please advise on how to count unique date values (e.g., 2/4/07, 2/4/07, 2/5/07, 2/6/07 = 3 unique values).
I'm working on a spreadsheet which has a date column with duplicate dates. I need to count the number of unique dates.
Your help is greatly appreciated!!!
Tommy
I am trying to extract all unique values as well as the quantity of repeated values from a list of nonconsecutive cells. Basically, I need to create a parts list based on part numbers which are created through a series of selections. The data is then concatenated into a single cell, and then all unique items are listed and quantified. I am able to generate the list, but can't figure out how to get a list of the unique items and quantities extracted into a separate list without the blank cells in between.
ATTACHED IS THE SCREENSHOT.
Thanks
Hi,
I have given a search on this forum for this matter but I didn't find any solution.
I have 4 coloumns (IN, OUT, IN+OUT, INOUT) and N rows. I need to know how can I be able to make a SUM & DIFFERENCE between the IN and OUT values. These values, both in IN and OUT, come into a single cell, separated by ";". Now, you'll probably tell me it's wrong to have multiple values in a single cell but I can't work any other way because the NUMBER of these VALUES is not constant in order to be able to separate them in each coloumn.
Also, the NUMBER of these multiple values in a single cell can be up to 50, 50 values. And each value can have up to 7 digits.
I have linked to you a sample of what I'm trying to do. http://uploaded.to/file/2p39ldok
I know little about VBA programming but I'm willing to do some damage.
I would really appreciate it.
Thank you.

