• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to autofilter 8-10 rows of numbers to sort in a group

Jack-P-Winner

Jack-P-Winner

Member
I need to find "liked" number patterns within my spreadsheet that has around 500,000 rows. The data that I have entered looks like this:

ExampleofCounterLocations_zpsf957ab08.jpg


You will notice it has game numbers (column 1) and a location (Column 6) along with four numbers (column 2-3-4-5) that we will call counter numbers. When I run my filters I want to be able to find a group of numbers that look like the example above How can I set up a Macro to find similar number patterns like this?

Patternsrows1-8_zpscbb6aec6.jpg


this is what I have so far and bare with me as I am a newbie I need it to find up to 8 to 10 rows like the top picture


Sub simple_search()
Cells.Find(What:=Range("B3").Value & "^" & Range("C3").Value & "^" & Range("D3").Value & "^" & Range("E3").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

End Sub
 
Hi Jack,

Based on your example there is only one result, you can use Advance Filter, with Criteria to give you the result.

cheers

kanti
 
I want to be able to do a data sort or find a pattern like the picture through out all the rows. Please take a look at this video, in rows 3-11 I want to be able to put in different 4 digit counter numbers. Press run filters and it goes out and finds all the places that the same exact numbers are as in rows 3-11

I deleted the code by accident and need help from someone please
 
Last edited:
You will notice that I have game numbers. I have a spreadsheet that has roughly 400,000 rows of info so far. They have games called different things and average 2200 rows each. I need to find the grid of 8 within 1 (ONE) of the games. I want to be able to put various numbers in rows 1-8 and try to find the exact match within the 400,000 rows
 
Hi Jack,

Try the attached:

You enter the criteria on the Target Sheet and press the button.

The Ranges are hard-coded in the macro, but that can be made dynamic, this was a quick and dirty test. Enjoy

I recorded this as a macro as I am too lazy to write out the steps, but if you want the steps to do the Advance Filter, please let me know.

kanti
 

Attachments

Its kinda hard to say if it will work, it appears to . I have attached a larger book to try. I would like to be able to capture the following information when it does a search. What I mean is say I am looking for the pattern below with the numbers 7689,7679,7678,7668 ect.... to 1423. I would like to be able to place these numbers in rows , high lite them, click on a button to go find them EXACTLY as they appear. That's important because i am looking for a similar pattern that is attached to numbers. Does this make sense?

ExampleofCounterLocations_zpsf957ab08.jpg
 

Attachments

Jack,
I am in a small town in Indonesia, so very slow connection, if you need something else can you please explain.

Like I said the Ranges can be made dynamic.

Can you upload the file, or a sample of the data
 
I am in Texas USA and suppose to have high speed LOL but video taking forever to upload. I attached a file last time perhaps you can open it. I wish to data sort up to 8 rows of numbers that come from the same Game location and sequential for example: If you notice the Game number is "sequential" 1251-1250-1249 etc... and the location is the "same" The numbers are different. The numbers is what I wish to place in rows say 1-10 (7689-7679-7678-7668 etc...) Once I place the group of numbers in the rows and click on a button I wish for it to come up like this below with the sequential game and same location. Now, I know it may not find them all all the time and thats okay, let me see what it does find. Say it only finds Game numbers 1247-1248-1249-1250-1251 . Thats. okay, I would like to display it BUT ideally I will find all the pattern of the numbers I lace in the rows. Also I need the numbers to be colored like this. That helps see the pattern better, Basically Odd is yellow back ground with red font, Even is Green and gray. I have to get a little sleep as it is 3 a.m here and I get up at 5 am LOL so I have two hrs of sleep before I go to work LOL BUT, I will look at this first thing. We own our own Cafe so I can get on line throughout the day Thanks for your help and as soon as the video upload I will send it on. You may have enough info now IDK. Oh, I have almost 500,000 rows that I will be searching so whatever you set up I will copy and paste my info on to the sheet. The info is EXACTLY like this pic is below

ExampleofCounterLocations_zpsf957ab08.jpg
 
I finally uploaded the video. I noticed that the location number is different in the video. they have to be the same. When I did this quickly I did not copy and paste. Instead I dragged it down making it count 1,2,3,4,5,6, LOL sry if there is any confusion on that part. The rest of the video is what I am looking for.


 
kchiba said:
Hi Jack,

Did not watch the video
we are the victims of the tyranny of time zones, I have attached the updated file

Hope it is OK
Click to expand...
I do THANK YOU!!!!. All I had to do on the first sheet is drag the cells down and hide the column. This new sheet looks like its the icing on the cake. I will load it up and check it out as soon as I get home. Man, THANK YOU THANK YOU THANK YOU
 
kchiba said:
Hi Jack,

Did not watch the video
we are the victims of the tyranny of time zones, I have attached the updated file

Hope it is OK
Click to expand...
It appears that this sheet basically copies the info from the source and places it in the target area. I went to the critsht sheet and dragged down to get 8 rows (can I do this?) then i put in new numbers, went to main and pressed Do it. When you look at target it has all the source numbers

Yes we are caught in the time zone warp LOL, last night I stayed up all night. I have been looking for this exact thing you have done for well over a month and i was not going to go to bed knowing I had a guru on the other end THANK YOU!!!
 

Attachments

  • critsheet.jpg
    critsheet.jpg
    123.8 KB · Views: 6
  • source.jpg
    source.jpg
    207 KB · Views: 5
  • target.jpg
    target.jpg
    189.6 KB · Views: 5
Last edited:
Can I make more rows for the criteria, say from 8 to maybe up to 20 and on the first sheet is it possible to place a BOX around each "find" for quick reference? Easier and quicker to spot (see)
 
Hi jack,

try the attached, i note that your criteria did not have any matches in the data.

you can enter as many rows as you wish in the criteria.

cheers

kanti

ps - what is the time where you are?
 

Attachments

Hi there new friend. It is 10:50pm here in East Texas. Man, that 1 st sheet is working very well. I will load up this sheet ad try it out too. How long have you been programming?
 
Glad to see it is working, I am not really a programmer, I respect them too much to call myself one. I am more a frustrated programmer.
 
You must log in or register to reply here.
Back
Top