Find and complete the series of numbers?

sezuh · Jun 10, 2011

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

Cindy Ellis · Jun 11, 2011

In reviewing this thread about 5 times (at least) and attempting to read your mind, it appears that you have 2 sequences of 4 numbers between 1 and 16 in column A, and 2 sequences of 4 numbers between 1 and 16 in column C, and you want to use the contents of A to choose a sequence from C, and populate that into column B.
My first assumption was that the 2 groups of 4 numbers in column A were non-overlapping, and that you were looking for the complementary set (the "other 8" numbers) from column C, but that assumption is contradicted by the data in A3 (and other cells). So...here are a bunch of questions:

How is the "correct string" determined?
If there are duplicates between the 2 groups of 4 in column A, how does that affect the selection of the "missing" numbers from column C?
How are the numbers divided between the 2 groups of 4?
What everyone is asking is...what is the logic behind the "right" answer?

Please take us through it in words. While the example data will be very useful for testing out a formula or macro, I can't even get to a point where there's something to test.

Cindy Ellis · Jun 11, 2011

pgc is a better mind reader than I am.

Looks like a workable solution, and looking at the response answered some of my questions about the inputs.
Regards,
Cindy

sezuh · Jun 11, 2011

Pedro,
Thank you very very ..........very much for the amazing solution .
I tried it in a small list and it work like a charm..

Now i'm trying it on a list with 181470 rows i'll let you know the outcome
tomorrow.
Thanks again and your outstanding contribution to this tread been much
apreciated indeed........

Sezuh

pgc01 · Jun 11, 2011

Sezuh ,I'm glad it helped.

I hope it works for your 200k rows. I cannot test it now, as I'm working with excell 2000 but I'll wait for your feedback.

This could also be done with code. Maybe someone will post a vba solution.

Ruddles · Jun 11, 2011

Cindy Ellis said:
... attempting to read your mind...

It helps if you take a few moments to go through OP's previous requests of a similar nature.

Cindy Ellis · Jun 11, 2011

Ruddles said:
It helps if you take a few moments to go through OP's previous requests of a similar nature.

Good idea...I didn't think of that

(maybe next time)

mirabeau · Jun 11, 2011

hey Sezuh,

here's a VBA code that should do what I think it is you want.
data in colE, and resulting lacunae (i.e. fill-ins) in colF. The code took me under 3 seconds to do 200,000 rows on some test data.

However, I didn't figure out the logic of the ordering of your example fill-ins, so I used the natural ordering

Code:

 Sub testcode()
t = Timer
Dim lr&, a, e, x, b() As Byte, k&, s&
Dim c() As String, q As String, j&
lr = Range("E" & Rows.Count).End(3).Row
a = Range("E1").Resize(lr)
ReDim c(1 To lr, 1 To 1)
For Each e In a
ReDim b(1 To 16)
    k = k + 1
    e = Trim(Replace(e, ",", ""))
    For Each x In Split(e, " ", -1)
        b(x) = 1
    Next x
    For j = 1 To 16
        If b(j) = 0 Then
           s = s + 1
           If (s = 5) + (s = 9) Then
                q = q & ", " & j
            Else
                q = q & " " & j
            End If
        End If
    Next j
    s = 0
    c(k, 1) = q
    q = Empty
Next e
[f1].Resize(lr) = c
 MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

chuckles1066 · Jun 12, 2011

sezuh said:
Hi Chuckles,
Thanks for your interest on this tread and i'm sorry if it's not clear for you to understand,but i can not explain it more clearer than that.

Thanks again
Sezuh

Apologies if I came across a litttle more bluntly than I intended

I'm now getting to a point where my knowledge means I might just be able to start giving something back to this wonderful forum.....and it's frustrating when you can't make head or tail of the question being asked!

I will bite my lip in future

sezuh · Jun 12, 2011

Hi Mirabeau,
i tried your code and i'm getting "error 13" and this line highlited

HTML:

b(x) = 1

, and alsu would you care to send an example of your result please?
Chukles, no worries; because, sometimes i have a difficulty explaining myself.
Pedro, i tried it on big list but after more than 12 hours the result was blank rows here an example maybe i done something wrong????

Sheet4

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 174px"><COL style="WIDTH: 72px"><COL style="WIDTH: 245px"></COLGROUP><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: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>01 02 03 05, 04 05 06 08</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 09 10 12</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>01 02 03 05, 04 05 09 11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 09 13 15</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>01 02 03 05, 04 05 10 18</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 09 17 18</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>01 02 03 05, 04 05 12 20</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 09 19 20</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>01 02 03 05, 04 05 13 14</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 10 13 17</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>01 02 03 05, 04 05 15 16</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 10 14 19</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>01 02 03 05, 04 06 09 13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 10 16 18</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>01 02 03 05, 04 06 10 15</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD>02 03 04 06, 05 06 07 09, 08 11 13 14</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>{=LOOKUP("a",IF({1,0},"",INDEX($G$2:$G$181471,MATCH(TRUE,MMULT(--ISNUMBER(FIND(" "&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}&" ",SUBSTITUTE(" "&E2&" "&$G$2:$G$181471&" ",","," "))),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})=20,0))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>
thanks again for all of you realy appreciate your interest on this.

Sezuh

MikeWx · Jun 12, 2011

Sezuh,

Your latest post shows data that doesn't follow the same rules as your earlier data. Your earlier data sample indicated you wanted to match eight unique values in a cell in Column E to the cell in Column G that had the remaining eight unique values that would result in all 16 values from 1 to 16.

In your latest post, Cell E2 has two occurrences of 05 instead of eight unique values.

Also, Column G now has 12 values instead of 8, and every displayed cell in Column G has two occurrences of 06, so Column G's values are also no longer unique.

It looks like pgc01's formula works for your earlier data. Have your requirements for a solution changed?

Find and complete the series of numbers?

sezuh

Well-known Member

Cindy Ellis

MrExcel MVP

Excel Facts

Cindy Ellis

MrExcel MVP

sezuh

Well-known Member

pgc01

MrExcel MVP

Ruddles

Well-known Member

Cindy Ellis

MrExcel MVP

mirabeau

Banned user

chuckles1066

Banned

sezuh

Well-known Member

MikeWx

Active Member

Similar threads

Forum statistics

Share this page

Find and complete the series of numbers?

Well-known Member

MrExcel MVP

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Banned user

Banned

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock