Search:

# Routine needed for continuing numbers.

Asked By: Muaz    Date: Mar 07    Category: MS Office    Views: 706

i have excel data
3,5,5,3
5,3,3,5,5
3,5,5,3,3
5,5,5,5
3,3,5,5,3
i'd like to solve continuing number in row order manner
ie results are
1:5
2:7
3:
4:1

2:7 is
5 for row1
3,5 for row2
5,3 for row3
3,5 for row5
total=7

Share:

### 7 Answers Found

Answer #1    Answered By: Vinit Online     Answered On: Mar 07

It seems to me some of the info was corrupted in converting to plain text.
Perhaps you would like to try again writing your original in plain text. If
necessary use a vertical bar to show where each column ends.

I cannot understand how 5 +3,5 +5,3 +3,5 give a total of 7

Could you explain a bit more please?

Answer #2    Answered By: Jake Williams     Answered On: Mar 07

3,5,5,3
5,3,3,5,5
3,5,5,3,3
5,5,5,5
3,3,5,5,3

is placed at a1:e5

for 1st line 5 is continuing then 1:2 and 2:1
for 2nd line 3,5 is continuing then 1:1 and 2:2
for 3rd line 3,5 is continuing then 1:1 and 2:2
for forth line 5 is continuing then 4:1
for fifth line 3,5 is continuing then 1:1 and 2:2

and range and number  is changable,varied.

i'd like to get result like below,

1:5
2:7
3:
4:1

Answer #3    Answered By: Muriel Dunn     Answered On: Mar 07

I "think" what he's trying to say is that he's trying to
create some type of "pattern recognition" algorithm.
I "think" his data is:
: A|B|C|D|E
1: 3|5|5|3|
2: 5|3|3|5|5
3: 3|5|5|3|3
4: 5|5|5|5|
5: 3|3|5|5|3

and I "think" he'd like to predict the next number(s) in the series.
(what he's calling "continuing")
But I don't understand his explanation and notations of:
> for 1st line 5 is continuing then 1:2 and 2:1
> for 2nd line 3,5 is continuing then 1:1 and 2:2
> for 3rd line 3,5 is continuing then 1:1 and 2:2
> for forth line 5 is continuing then 4:1
> for fifth line 3,5 is continuing then 1:1 and 2:2
>
> and range and number  is changable,varied.
>
> i'd like to get result like below,
>
> 1:5
> 2:7
> 3:
> 4:1

So... I may be completely off-target.

Answer #4    Answered By: Trae Thompson     Answered On: Mar 07

Option Explicit
Function classify(data1 As Range, whatnum As Integer)
Dim data, loop1 As Long, loop2 As Long, odata As Integer
Dim n As Integer, an As Integer, ans(), vkey
data = data1.Value
For loop1 = 1 To UBound(data, 1)
odata = data(loop1, 1)
For loop2 = 1 To UBound(data, 2)
If odata = data(loop1, loop2) Then
n = n + 1
'Debug.Print odata
Else
'Debug.Print n
'Debug.Print "'" & odata & ":" & loop1 & ":" & loop2
& ":" & n
ReDim Preserve ans(an): ans(an) = n: an = an + 1
odata = data(loop1, loop2)
n = 0
End If
Next
Next
'For loop1 = LBound(ans) To UBound(ans)
' Debug.Print ans(loop1)
'Next
With CreateObject("scripting.dictionary")
For loop1 = LBound(ans) To UBound(ans)
If Not .exists(ans(loop1)) Then
If whatnum = ans(loop1) Then Debug.Print ans(loop1):
classify = _
WorksheetFunction.CountIf(WorksheetFunction.Transpose
(ans), ans(loop1)): _
Exit Function
Else
End If
Next
End With
End Function

i roughly make udf,but no result and error,if have any idea will help.

Answer #5    Answered By: Rochelle Elliott     Answered On: Mar 07

Option Explicit
Function str3(irng As Range, ii As Integer)
Dim i As Long
Dim j As Long
Dim data
Dim str2(), str1, str4
data = irng.Value
ReDim str2(1 To UBound(data, 1))

For i = 1 To UBound(data, 1)
str1 = 1: str4 = 1
For j = 2 To UBound(data, 2)
'Debug.Print data(i, j)
If data(i, j) <> "" Then
If data(i, j) = data(i, j - 1) Then
str4 = str4 & "_" & str1
Else
str1 = str1 + 1
str4 = str4 & "_" & str1
End If
End If
Next
'Debug.Print str2(i)
str2(i) = str4
Next
'Debug.Print str2(ii)
'str3 = str2(ii)
ReDim ds(0 To UBound(data, 2))
Dim jj As Integer, r()
For i = 1 To UBound(str2)
r = Split(str2(i), "_")
For j = 1 To r(UBound(r))
jj = WorksheetFunction.CountIf(WorksheetFunction.Transpose
(r), j)
ds(jj) = ds(jj) + 1
Next
Next
str3 = ds(ii)
End Function
' this UDF has also #VALUE! error.

Answer #6    Answered By: Jake Williams     Answered On: Mar 07

this works...

Option Explicit

Function str3(irng As Range, ii As Integer)

Dim i As Long
Dim j As Long
Dim data
Dim str2(), str1, str4

data = irng.Value
ReDim str2(1 To UBound(data, 1))

For i = 1 To UBound(data, 1)
str1 = 1: str4 = 1
For j = 2 To UBound(data, 2)

If data(i, j) <> "" Then
If data(i, j) = data(i, j - 1) Then
str4 = str4 & "_" & str1
Else
str1 = str1 + 1
str4 = str4 & "_" & str1
End If
End If

Next

str2(i) = str4
Next

ReDim Preserve str2(1 To UBound(data, 1))
ReDim ds(0 To UBound(data, 2)) As Integer
Dim jj As Integer, r As Variant

'-------------------below part not work
'On Error Resume Next

For i = 1 To UBound(data, 1)
r = Split(str2(i), "_"): ' Debug.Print r(UBound(r))

For j = 1 To r(UBound(r))
'jj = WorksheetFunction.CountIf(WorksheetFunction.Transpose
(r), j)
jj = mem_countif(r, j)
ds(jj) = ds(jj) + 1
Next

Next

str3 = ds(ii)

End Function

Sub ca()

MsgBox str3(Sheet1.Range("c5:f12"), 3)

End Sub

Function mem_countif(r, j)
'Dim a(), i&, iCnt&
'a = Array(1, 2, 3, 4, 5)
Dim i&, iCnt&
iCnt = 0
For i = 0 To UBound(r)
iCnt = iCnt - (Val(r(i)) = j)
Next i
mem_countif = iCnt
End Function

Answer #7    Answered By: Muriel Dunn     Answered On: Mar 07

Row 1 has a sequence of 1, then a sequence of 2 then a sequence of 1
identical values.
So it has 2 sequences of 1 and 1 sequence of 2

If you do that for each row  and then find the totals for each sequence value
there are 5 sequences of 1
7 sequences of 2
No sequences of 3
1 sequence of 4

Hence
1:5
2:7 etc.

He would need to step through each row identifying sequences, accumulating
the counts of sequences in an array, and then suck them out of the array and
put them in the target cells.

I do not have the time to write this for him. Is it school homework? If so
perhaps I have given enough help?

Didn't find what you were looking for? Find more on Routine needed for continuing numbers. Or get search suggestion and latest updates.