Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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
  #5   Report Post  
Old 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
Default 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


  #6   Report Post  
Old 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
Default 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.
  #7   Report Post  
Old 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,909
Default 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


  #8   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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
  #10   Report Post  
Old 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
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Non repeating random numbers Rowland Excel Worksheet Functions 3 January 30th 07 12:31 AM
random numbers repeating Harlan Grove Excel Discussion (Misc queries) 2 January 24th 07 12:30 AM
random numbers repeating Ashkan Excel Discussion (Misc queries) 0 January 23rd 07 06:42 AM
I want random numbers generated without repeating numbers Johncobb Excel Worksheet Functions 2 September 7th 06 04:52 PM
REPEATING NUMBERS IN A ROW nazzoli Excel Worksheet Functions 1 August 17th 06 02:57 PM


All times are GMT +1. The time now is 03:38 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017