Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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?

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

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.

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

.Add ans(loop1), Nothing

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.

Option Explicit

Function str3(irng As Range, ii As Integer)

Dim i As Long

Dim j As Long

Dim data

Dim str2(), str1, str4

'Debug.Print irng.Address

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.

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

'http://www.ozgrid.com/forum/showthread.php?t=68838

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

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.

Related Topics:

- Converting a "Stringed" Number to a Number
- Converting a "Stringed" Number to a Number
- vba routine for eliminating rows with all zeros from spreadsheet
- Updating text in form while routine is running
- calling a sub routine
- Setup cannot continue because this version of the .NET Framework is incompatible
- application.quit - code continues to run
- sum of series of natural numbers
- prime number
- Display character which appears number of time equal half of maximum time
- assembly program that reads an integer number (from 0 to 9).
- an assembly program that reads an integer number from the keyboard and then displays its diviso
- gcd of given number
- number finder!
- sum and difference of 2 bcd numbers
- prime number problem
- addition of N numbers in NASM
- Multiplication And division of real binary and Hexa numbers in linux
- sum and Multiply number and print in decimal
- Assembly program that displays numbers
- C: Find the longest series of even and positive numbers
- odd number
- counting the frequency of randomly generated real number
- HW HELP! loop, histogram, random number
- Average of numbers ; matrix