Repeating numbers in same cell
 

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

Repeating numbers in same cell
 

1. Use Text to Columns to move the numbers to individual cells
2. Copy the row to a column using Paste/Special/Transpose
3. create Pivot Table listing the count of values by value.
--
Gary''s Student - gsnu200777


"Tabby" wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

Repeating numbers in same cell
 

This would work if the worksheet wasn't so big. I have numerous cells that
have repeating numbers in them. The problem is that the numbers are
different for each cell.

Using Text to Columns wouldn't work. There are up to 80 numbers in any
given cell.

Thanks for your quick response though.

"Gary''s Student" wrote:

1. Use Text to Columns to move the numbers to individual cells
2. Copy the row to a column using Paste/Special/Transpose
3. create Pivot Table listing the count of values by value.
--
Gary''s Student - gsnu200777


"Tabby" wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

Repeating numbers in same cell
 

On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

You could write a short UDF to do that. This will add the numbers in a string
that consists of comma-separated numbers.

To USE this UDF, enter a formula like:
=AddCSN(cell_ref)

To ENTER the code, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Enjoy. Note there is no error-checking in this code -- illegal values will
give a #VALUE! error.

============================
Function AddCSN(str As String) As Double
AddCSN = Evaluate(Replace(str, ",", "+"))
End Function
===============================
--ron

Repeating numbers in same cell
 

On Fri, 04 Apr 2008 13:07:53 -0400, Ron Rosenfeld
wrote:

On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

You could write a short UDF to do that. This will add the numbers in a string
that consists of comma-separated numbers.

To USE this UDF, enter a formula like:
=AddCSN(cell_ref)

To ENTER the code, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Enjoy. Note there is no error-checking in this code -- illegal values will
give a #VALUE! error.

============================
Function AddCSN(str As String) As Double
AddCSN = Evaluate(Replace(str, ",", "+"))
End Function
===============================
--ron

I just read your comment that you might have up to 80 numbers in each cell. The
above will only work for strings up to 255 characters in length, so I will
suggest this instead, which does not have that limitation:

===============================
Option Explicit
Function AddCSN(str As String) As Double
Dim i As Long
Dim sTemp
sTemp = Split(str, ",")
For i = 0 To UBound(sTemp)
AddCSN = AddCSN + sTemp(i)
Next i
End Function
==============================
--ron

Repeating numbers in same cell
 

On Apr 5, 4:40*am, Ron Rosenfeld wrote:
On Fri, 04 Apr 2008 13:07:53 -0400, Ron Rosenfeld
wrote:





On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell. *
Example: *1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? *i.e. (2) 1204,
(2) 1205?

You could write a short UDF to do that. *This will add the numbers in a string
that consists of comma-separated numbers.

To USE this UDF, enter a formula like:
* *=AddCSN(cell_ref)

To ENTER the code, <alt-F11 opens the VBEditor. *Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Enjoy. *Note there is no error-checking in this code -- illegal values will
give a #VALUE! error.

============================
Function AddCSN(str As String) As Double
AddCSN = Evaluate(Replace(str, ",", "+"))
End Function
===============================
--ron

I just read your comment that you might have up to 80 numbers in each cell.. The
above will only work for strings up to 255 characters in length, so I will
suggest this instead, which does not have that limitation:

===============================
Option Explicit
Function AddCSN(str As String) As Double
Dim i As Long
Dim sTemp
sTemp = Split(str, ",")
For i = 0 To UBound(sTemp)
* * AddCSN = AddCSN + sTemp(i)
Next i
End Function
==============================
--ron- Hide quoted text -

- Show quoted text -

Hi,

If using a UDF, the VBA Split function would be perfect.

Try:

Function SplitStr(InputS As String, Optional Delim As String = ",") As
Variant
Dim a As Variant, b As Variant, i As Long
a = Split(InputS, Delim)
For i = LBound(a) To UBound(a)
b = Split(InputS, a(i))
If InStr(SplitStr, " (" & UBound(b) & ") " & a(i)) = 0 Then
SplitStr = SplitStr & ", (" & UBound(b) & ") " & a(i)
End If
Next
SplitStr = Right(SplitStr, Len(SplitStr) - 2)
End Function

Use as: =SplitStr(A1,",")

If you wanted them in order, you would just have to sort the array "a"
first.

Cheers,
Ivan.

Repeating numbers in same cell
 

Ron

I think OP wants to count duplicates, not sum the numbers.

See Ivyleaf's function which I think is spot on.


Gord

On Fri, 04 Apr 2008 13:40:08 -0400, Ron Rosenfeld
wrote:

On Fri, 04 Apr 2008 13:07:53 -0400, Ron Rosenfeld
wrote:

On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

You could write a short UDF to do that. This will add the numbers in a string
that consists of comma-separated numbers.

To USE this UDF, enter a formula like:
=AddCSN(cell_ref)

To ENTER the code, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Enjoy. Note there is no error-checking in this code -- illegal values will
give a #VALUE! error.

============================
Function AddCSN(str As String) As Double
AddCSN = Evaluate(Replace(str, ",", "+"))
End Function
===============================
--ron

I just read your comment that you might have up to 80 numbers in each cell. The
above will only work for strings up to 255 characters in length, so I will
suggest this instead, which does not have that limitation:

===============================
Option Explicit
Function AddCSN(str As String) As Double
Dim i As Long
Dim sTemp
sTemp = Split(str, ",")
For i = 0 To UBound(sTemp)
AddCSN = AddCSN + sTemp(i)
Next i
End Function
==============================
--ron

Repeating numbers in same cell
 

On Fri, 04 Apr 2008 11:59:21 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Ron

I think OP wants to count duplicates, not sum the numbers.

See Ivyleaf's function which I think is spot on.


Oops, I misread. Thanks for pointing that out.
--ron

Repeating numbers in same cell
 

On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

I misread your initial request.

This UDF will return a sorted horizontal array consisting of the list of
numbers in row 1, and the count for each number in row two.

You can return the values on your worksheet in a variety of ways.

You could enter this array formula in two adjacent columns, with dimensions
large enough to include all of the unique values:

=TRANSPOSE(UniqueNums(A1)))

(gives you a vertical array; without the TRANSPOSE you'd have a horizontal
array).

With your data in A1

B1: =INDEX(UniqueNums($A$1),1,ROWS($1:1))
C1: =INDEX(UniqueNums($A$1),2,ROWS($1:1))

and fill down as far as required. (With LOTS of data, this method may take a
while to run).

There are other solutions, based on this algorithm, that may be appropriate
depending on more specifics.

Also, I'm not sure how you want the results sorted. As posted, it will sort by
the most frequent to the least frequent value. If you just want the results
sorted in numerical order, comment out the second Bubblesort line in the first
procedure.


Here is the code. Enter it into a regular module as I posted previously.

===========================================
Option Explicit
Function UniqueNums(CSN As String)
'Returns a horizontal two dimensional
' array of unique words and count
Dim cNumList As Collection
Dim Str
Dim sRes() As Variant
Dim I As Long, J As Long

'Split string
Str = Split(CSN, ",")

'get list of unique words
Set cNumList = New Collection

On Error Resume Next
For I = 0 To UBound(Str)
cNumList.Add Str(I), Str(I)
Next I
On Error GoTo 0

ReDim sRes(0 To 1, 1 To cNumList.Count)
For I = 1 To cNumList.Count
sRes(0, I) = cNumList(I)
Next I

'get number count for each number
For I = 1 To UBound(sRes, 2)
sRes(1, I) = (Len(CSN) - Len(Replace(CSN, sRes(0, I), ""))) / Len(sRes(0,
I))
Next I


'Sort Numerically Ascending
BubbleSort sRes, 0, True

'then sort by Count highest to lowest
BubbleSort sRes, 1, False

UniqueNums = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSort(TempArray As Variant, d As Long, _
bSortDirection As Boolean)
'bSortDirection = True means sort ascending
'bSortDirection = False means sort descending
Dim Temp1 As Variant, Temp2
Dim I As Long
Dim NoExchanges As Boolean
Dim Exchange As Boolean

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For I = 1 To UBound(TempArray, 2) - 1

' If the element is greater/less than the element
' following it, exchange the two elements.

Exchange = TempArray(d, I) < TempArray(d, I + 1)
If bSortDirection = True Then Exchange = _
TempArray(d, I) TempArray(d, I + 1)
If Exchange Then
NoExchanges = False
Temp1 = TempArray(0, I)
Temp2 = TempArray(1, I)
TempArray(0, I) = TempArray(0, I + 1)
TempArray(1, I) = TempArray(1, I + 1)
TempArray(0, I + 1) = Temp1
TempArray(1, I + 1) = Temp2
End If
Next I
Loop While Not (NoExchanges)
End Sub
==============================================
--ron

Repeating numbers in same cell
 

WOW! All of that just to fix somebody's boo-boo? I'll give it a try. As I
don't understand any of this, I'm skeptical that I will succeed.

Thanks to all of you for your assistance.

Tess

"Ron Rosenfeld" wrote:

On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

I misread your initial request.

This UDF will return a sorted horizontal array consisting of the list of
numbers in row 1, and the count for each number in row two.

You can return the values on your worksheet in a variety of ways.

You could enter this array formula in two adjacent columns, with dimensions
large enough to include all of the unique values:

=TRANSPOSE(UniqueNums(A1)))

(gives you a vertical array; without the TRANSPOSE you'd have a horizontal
array).

With your data in A1

B1: =INDEX(UniqueNums($A$1),1,ROWS($1:1))
C1: =INDEX(UniqueNums($A$1),2,ROWS($1:1))

and fill down as far as required. (With LOTS of data, this method may take a
while to run).

There are other solutions, based on this algorithm, that may be appropriate
depending on more specifics.

Also, I'm not sure how you want the results sorted. As posted, it will sort by
the most frequent to the least frequent value. If you just want the results
sorted in numerical order, comment out the second Bubblesort line in the first
procedure.


Here is the code. Enter it into a regular module as I posted previously.

===========================================
Option Explicit
Function UniqueNums(CSN As String)
'Returns a horizontal two dimensional
' array of unique words and count
Dim cNumList As Collection
Dim Str
Dim sRes() As Variant
Dim I As Long, J As Long

'Split string
Str = Split(CSN, ",")

'get list of unique words
Set cNumList = New Collection

On Error Resume Next
For I = 0 To UBound(Str)
cNumList.Add Str(I), Str(I)
Next I
On Error GoTo 0

ReDim sRes(0 To 1, 1 To cNumList.Count)
For I = 1 To cNumList.Count
sRes(0, I) = cNumList(I)
Next I

'get number count for each number
For I = 1 To UBound(sRes, 2)
sRes(1, I) = (Len(CSN) - Len(Replace(CSN, sRes(0, I), ""))) / Len(sRes(0,
I))
Next I


'Sort Numerically Ascending
BubbleSort sRes, 0, True

'then sort by Count highest to lowest
BubbleSort sRes, 1, False

UniqueNums = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSort(TempArray As Variant, d As Long, _
bSortDirection As Boolean)
'bSortDirection = True means sort ascending
'bSortDirection = False means sort descending
Dim Temp1 As Variant, Temp2
Dim I As Long
Dim NoExchanges As Boolean
Dim Exchange As Boolean

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For I = 1 To UBound(TempArray, 2) - 1

' If the element is greater/less than the element
' following it, exchange the two elements.

Exchange = TempArray(d, I) < TempArray(d, I + 1)
If bSortDirection = True Then Exchange = _
TempArray(d, I) TempArray(d, I + 1)
If Exchange Then
NoExchanges = False
Temp1 = TempArray(0, I)
Temp2 = TempArray(1, I)
TempArray(0, I) = TempArray(0, I + 1)
TempArray(1, I) = TempArray(1, I + 1)
TempArray(0, I + 1) = Temp1
TempArray(1, I + 1) = Temp2
End If
Next I
Loop While Not (NoExchanges)
End Sub
==============================================
--ron