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
- calling a sub routine
- vba routine for eliminating rows with all zeros from spreadsheet
- Updating text in form while routine is running
- Setup cannot continue because this version of the .NET Framework is incompatible
- application.quit - code continues to run
- Number fields in Excel being considered NULL (empty)
- Jbuilder Serial number
- Math function to determine if a number is an int
- Getting the time for the Number of records retrived
- Datagrid Display - right align numbers
- changing the port number on JBOSS
- get the number of JList which is created by array
- How to access the Phone Number from the J2ME Application
- Error preallocating sequence numbers. The sequence table information
- Formating the Number
- julian day number
- Pocket PC 2002 PDA to dail a phone number
- Number Format
- generating random numbers
- Fibonacci and Prime numbers
- convert from number (0-255) to character
- How to truncate a floating number?
- Getting CPU Number (Id) of a computer using Applet