Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non repeating random numbers | Excel Worksheet Functions | |||
random numbers repeating | Excel Discussion (Misc queries) | |||
random numbers repeating | Excel Discussion (Misc queries) | |||
I want random numbers generated without repeating numbers | Excel Worksheet Functions | |||
REPEATING NUMBERS IN A ROW | Excel Worksheet Functions |