Find and complete the series of numbers?

[sezuh]

Hi,
Thanks for all the help i've got before,this is my last request before going back to work tomorrow.
I have two column with string of numbers,ie;column "E" and "G". In order to complete the numbers series of column "E" i have to find the right string from column "G".Here is an example (series of 1 to 16);
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="COLOR: #00ccff">LIST</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>1 2 6 13, 3 5 10 15</TD><TD></TD><TD>2 5 11 16, 4 9 13 15</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>1 2 6 13, 3 8 11 13</TD><TD></TD><TD>2 5 11 16, 6 7 8 10</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>1 2 6 13, 4 7 11 14</TD><TD></TD><TD>2 5 11 16, 8 9 12 16</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>1 3 12 14, 4 7 11 14</TD><TD></TD><TD>3 8 11 13, 5 6 9 14</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>1 3 12 14, 4 9 13 15</TD><TD></TD><TD>3 8 11 13, 5 7 12 13</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>1 3 12 14, 5 6 9 14</TD><TD></TD><TD>4 7 11 14, 5 6 9 14</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>1 7 15 16, 2 4 10 12</TD><TD></TD><TD>4 7 11 14, 8 9 12 16</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>1 7 15 16, 2 5 11 16</TD><TD></TD><TD>4 7 11 14, 10 13 14 16</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>1 7 15 16, 3 4 6 16</TD><TD></TD><TD>5 6 9 14, 10 13 14 16</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="COLOR: #ff0000">EXPECTED RESULT</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>1 2 6 13, 3 5 10 15</TD><TD>4 7 11 14, 8 9 12 16</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>1 2 6 13, 3 8 11 13</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>1 2 6 13, 4 7 11 14</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>1 3 12 14, 4 7 11 14</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>1 3 12 14, 4 9 13 15</TD><TD>2 5 11 16, 6 7 8 10</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>1 3 12 14, 5 6 9 14</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>1 7 15 16, 2 4 10 12</TD><TD>3 8 11 13, 5 6 9 14</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>1 7 15 16, 2 5 11 16</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>1 7 15 16, 3 4 6 16</TD><TD></TD><TD></TD></TR></TBODY></TABLE>


thanks again
Kind Regards
Sezuh

 

[sezuh]

Hi Mike,thanks for your response on this tread ,my requirements for a solution not changed,but the logic is the same.I know my last post not unique,i was on hurry to send this post and i forgot to clear duplicates sorry for the confusion.I modified formula from 1 to 16 into 1 to 20,i think this bit of the formula might need to be changed,but how ?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>

&" ",","," ")

</o>
Thanks and much obliged.<o></o>
Sezuh<o></o>

 

[mirabeau]

Sezuh,

Seems my earlier code also misunderstood what you wanted.

Trying again, the following code (when I run it) produces the result you indicate that you want in your opening post. Your data are assumed to be in columns E and G, with the matchups listed in column F.

The code as it stands may give an error depending on the structure of your data. With the data you post it doesn't error for me. However, if it does error for you, then just delete entirely the bit colored red. This has the sole purpose of making the code run faster (maybe 3-4 times faster) but the code should still work without it.

Sub sezuhmatchups()
Dim na&, nc&, a, c, flg As Byte
Dim d As Object, e, i&, j&, x, u()
na = Range("E" & Rows.Count).End(3).Row
nc = Range("G" & Rows.Count).End(3).Row
Set d = CreateObject("scripting.dictionary")
a = Range("E1").Resize(na)
c = Range("G1").Resize(nc)
ReDim u(1 To na, 1 To 1)
For i = 1 To na
a(i, 1) = Replace(a(i, 1), ",", "")
For Each e In Split(a(i, 1), " "): d(e) = 1: Next
For j = 1 To nc
    flg = 0
    x = Replace(c(j, 1), ",", "")
    For Each e In Split(x, " ", -1)
        If d(e) = 1 Then flg = 1: Exit For
    Next e
If flg = 0 Then u(i, 1) = c(j, 1): Exit For
Next j
d.RemoveAll
Next i
[f1].Resize(na) = u
End Sub

This code would take quite a while to run on 180,000 or so rows (do you always have the same number of rows in ColG as you do in ColE) but could be speeded up very considerably if it's really the sort of thing you want.

 

[MikeWx]

Sezuh,

With regard to pgc01's formula, you made another change to the data that his formula did not expect; you put a leading zero to the left of numbers less than 10. If that is the way your real data appears,

change

(--ISNUMBER(FIND(" "&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}


to

--ISNUMBER(FIND(" "&RIGHT("0"&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20},2)

 

[sezuh]

Mike, your version of change worked perfect thanks very much indeed.
I'll try it i a larger list tonight and see what happen..
Mirabeau i tried the code it run without error but no result in column "F"
sezuh

 

[mirabeau]

Mike, your version of change worked perfect thanks very much indeed.
I'll try it i a larger list tonight and see what happen..
Mirabeau i tried the code it run without error but no result in column "F"
sezuh

I guess the reason for that is I had supposed your data started from the first row of the worksheet when it actually seems to start from the second.
So, if you still want to try the code, make the following 2 minor modifications (shown in red)(or just run the below code as it is)(alternatively, start your data from the first row with my earlier code) and give it another go.

Sub sezuhmatchups()
Dim na&, nc&, a, c, flg As Byte
Dim d As Object, e, i&, j&, x, u()
na = Range("E" & Rows.Count).End(3).Row
nc = Range("G" & Rows.Count).End(3).Row
Set d = CreateObject("scripting.dictionary")
a = Range("E1").Resize(na)
c = Range("G1").Resize(nc)
ReDim u(1 To na, 1 To 1)
For i = 2 To na
a(i, 1) = Replace(a(i, 1), ",", "")
For Each e In Split(a(i, 1), " "): d(e) = 1: Next
For j = 2 To nc
    flg = 0
    x = Replace(c(j, 1), ",", "")
    For Each e In Split(x, " ", -1)
        If d(e) = 1 Then flg = 1 ': Exit For
    Next e
If flg = 0 Then u(i, 1) = c(j, 1): Exit For
Next j
d.RemoveAll
Next i
[f1].Resize(na) = u
End Sub

 

[sezuh]

Hi,Mirabeau,1001 apologies for not coming back to you earlier,sorry!
The code in post #17 it gives only the missing numbers from Column "E" it does not give anyting from column "G" but i can use it for something else.
The code from post #25 works for the example on my first post but somehow it does not work for something else and the funny thing there is no error!!!?
Pedro,i managed to get result from 181471 rows list with Mike's help modifying your formula. But the disappointed to get over than4000 unique
rows ,i was expecting to get only 57 rows .....
i have no ideas how to reduce it to that number?
Anyhow thanks for the wonderful help i got,much obliged.
Kind Regards
Sezuh

 

[mirabeau]

The code from post #25 works for the example on my first post but somehow it does not work for something else and the funny thing there is no error!!!?
Sezuh

Sezuh,

Nothing funny about the no error for that code. The code didn't error. The error was in my reading of your original example which had two equally valid (from the information you gave) interpretations, of which that code seemingly took the wrong one.

So, by a trial and error process we're maybe making headway.

Try the following code on your 181,471 rows, with data in Cols E and G.
It does assume that all of your data numbers are from 1 to 16. If this is not correct then please post back.

You'll very soon find out, within 10 seconds or so, whether the code works and does what you want.

Sub sezuh_problem()
Dim na&, nc&, a, c
Dim d As Object, e, i&, j&
Dim b() As Boolean, mx&
Dim u(), v()
mx = 16
Set d = CreateObject("scripting.dictionary")
na = Range("E" & Rows.Count).End(3).Row - 1
nc = Range("G" & Rows.Count).End(3).Row - 1
a = Range("E2").Resize(na)
c = Range("G2").Resize(nc)
ReDim u(1 To na), v(1 To nc), w(1 To na, 1 To 1)

For i = 1 To na
    ReDim b(1 To mx)
    For Each e In Split(a(i, 1), " ", -1)
        b(e) = True
    Next e
    For j = 1 To mx
        If b(j) Then u(i) = u(i) & " " & j
    Next j
Next i

For i = 1 To nc
    ReDim b(1 To mx)
    For Each e In Split(c(i, 1), " ", -1)
        b(e) = True
    Next e
    For j = 1 To mx
        If Not b(j) Then v(i) = v(i) & " " & j
    Next j
    d(v(i)) = i
Next i
For i = 1 To na
    If d(u(i)) <> Empty Then w(i, 1) = c(d(u(i)), 1)
Next i
Range("F2").Resize(na) = w
End Sub

 

[sezuh]

Mirabeau,my data is not always 1 to 16 ,it varies up to 1 to 52 that is my maximum it'll not be more than that. I've just tried last code on 1 to 25 and column "E" have 4980 ,column "G" have 59001 rows and i got (error 13 ) with this line highlited

B(e)=TRUE

thanks
sezuh

 

[mirabeau]

Sezuh,

1. Can I request that you run that code on your originally posted data with numbers 1 to 16, although with as many rows as you like (a million if need be - it shouldn't take long) and report back whether or not it works as you want in that case.

2. If it doesn't work for you then clearly I've again misunderstood just what it is you want, and there's little purpose in my doing any more without clear directions.

3. if your data numbers can range up to 52 then
(a) is this something known in advance of the whole exercise, or does the number 52 need to be determined from the data?
(b) if your numbers do range up to 52, you would need to present a specific and unambiguous example of a string that is a valid match to say the top entry in Col E, and also an example of as string that is not.

 

[sezuh]

Mirabeau,your last code work for the example in my #1 post, but somehow it does not work to the longer version of the same list (1 to 16),i dont think something to do with your code,it must be from my side but what ?.....
For numbers " 1to 52 " it's very early for that now , but to answer your question ; the data known in advance of the whole exercise.what ever data there is always differ from each list,But the logic is the same to complete the missing numbers from that specific list or Column,ei "1 to 16,1 to 18,1to 20,1to 25..............1 to 52".
Thanks very much for your time and excellent contribution,very much appreciated.
Regards
Sezuh