Remember Me?

#1
April 4th 08, 04:20 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 5
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?

#2
April 4th 08, 04:28 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 11,058
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?

#3
April 4th 08, 04:37 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 5
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?

#4
April 4th 08, 06:07 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
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:

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
End Function
===============================
--ron
#5
April 4th 08, 06:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
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:

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
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)
Next i
End Function
==============================
--ron

#6
April 4th 08, 07:21 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 141
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:

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
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)
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.
#7
April 4th 08, 07:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 22,908
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:

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
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)
Next i
End Function
==============================
--ron

#8
April 4th 08, 08:18 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
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
#9
April 4th 08, 08:44 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
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?

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).

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)
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
#10
April 5th 08, 02:38 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 5
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?

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).

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)
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Rowland Excel Worksheet Functions 3 January 29th 07 11:31 PM Harlan Grove Excel Discussion (Misc queries) 2 January 23rd 07 11:30 PM Ashkan Excel Discussion (Misc queries) 0 January 23rd 07 05:42 AM Johncobb Excel Worksheet Functions 2 September 7th 06 04:52 PM nazzoli Excel Worksheet Functions 1 August 17th 06 02:57 PM

All times are GMT +1. The time now is 10:12 PM.