• 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.

Look for U's and D's in a pattern within several rows

I have another challenge for the gurus. I want to be able to find all the U's and D's that will follow a group of U's and D's in up to 6 columns and approximately 2500 rows. There will be times I will want to search for say 4 letters, 5 letters and then 6 letters. For example 4 letters will be UDDU, 5 letters DDUUD and 6 letters DDUUDD What I am wanting is to be able to place say a 5 letter DDUUD in a box, hit a search button and it find all the next letters which will be either a U or D in the next row up so it will look like this: Columns A through AK is how the spreadsheet actually is. How can I do this?

DDUUD


and it find for example

DUUUD
DDUUD
 

Attachments

  • UD Look up.xlsx
    15.1 KB · Views: 10
Hi Jack ,

You mention that a pattern such as DDUUD will match with the following patterns :

DUUUD
DDUUD

I do not know the basis for this kind of pattern matching ; can you clarify whether given the pattern DDUUD , are the two that you have given the only patterns that will match ? Or are there more ? If yes , then can you detail all the patterns that will match with the given pattern DDUUD ?

Narayan
 
yes there could be many morel Basically any combination of a U and D up to 4 columns, 5 columns and 6 columns. I am looking for the NEXT series of U and D's that follow any combination I place in the box. such as for an example of the 4

UUUU
UUUD
UUDD
UDDD
UDDU
UDUD
DDDD
DDDU
DDUU
DUUU
DUUD
ECT...
 
Hi Jack ,

I am still in the dark !

You mention that there are many combinations that will match whatever pattern you have placed in the search box ; you have not mentioned what is the basis for matching. Is it that if you place a 5 letter pattern , only 5 letter patterns will match ? Or will 4 letter patterns also match ?

We have already started the same back and forth dialogue that is not going anywhere ; can you please assume that I do not understand anything of what for you is very easy , and explain in clear terms how to match a given pattern ?

Can you take any one pattern , and give at least 5 patterns which will match that given pattern , and then give as many patterns as possible that will not match ?

Narayan
 
Here is an example of what I look for when I place numbers to look up. Open this file, click on the box under G , you will see a box appear that says select range high light columns C rows 4,5,6 (run your cursor over columns C and D rows 4,5,6 . Now press ok. Then next and okay. You will see it looks for groups of the 2,4,12. I want to find groups of the U's and D's with from 4 columns to 6 columns of Us and D's. is this any clearer?

https://www.dropbox.com/s/7skl9mfd87oiece/Side Numbers forum.xlsm
 
Hi Jack ,

I am still in the dark !

You mention that there are many combinations that will match whatever pattern you have placed in the search box ; you have not mentioned what is the basis for matching. Is it that if you place a 5 letter pattern , only 5 letter patterns will match ? Or will 4 letter patterns also match ?

We have already started the same back and forth dialogue that is not going anywhere ; can you please assume that I do not understand anything of what for you is very easy , and explain in clear terms how to match a given pattern ?

Can you take any one pattern , and give at least 5 patterns which will match that given pattern , and then give as many patterns as possible that will not match ?

Narayan


okay, the 4 pattern will only display the NEXT 4 U's and D's that appear in the NEXT row UP. The 5 pattern 5 U's and D's in the next row UP and so on. I can try and do a video
 
Hi Jack ,

Fine , we are going somewhere !

So a 4-letter pattern will match only other 4-letter patterns , a 5-letter pattern will only match other 5-letter patterns.

Since you have already uploaded a working file , can you answer my second question ? Can you take any one sample pattern , and explain which of the patterns in your worksheet will and will not match ?

Narayan
 
I sure can. Say I wanted to look for all the rows that contain DDUUDD. The only rows I would want to see will be rows 4,8,20. I would not want to see any other rows
 

Attachments

  • Us and Ds.xlsx
    12.7 KB · Views: 10
Hi Jack ,

From your earlier post , if we want to match DDUUDD , you say the only matches will be rows 4 , 8 and 20. From what I can see , when you say row , do you mean the numbers you have given in column A ?

If we see the patterns on the physical excel numbered rows , they are :

UUUUUU
UUDUDU
UUDDDU

When we see the patterns on Games 4 , 8 and 20 , they are :

DDUUDD
DDUU D
DDUUDD

Which ones do you really mean , the Excel rows 4 , 8 and 20 , or the rows which have the numbers 4 , 8 and 20 in column A ?

More importantly , can you say why only these are the matches ? Why is it that the other rows do not match ?

Narayan
 
Please check the file, maybe something like this?
 

Attachments

  • Us and Ds.xlsx
    15.7 KB · Views: 7
Hi Jack ,

From your earlier post , if we want to match DDUUDD , you say the only matches will be rows 4 , 8 and 20. From what I can see , when you say row , do you mean the numbers you have given in column A ?

If we see the patterns on the physical excel numbered rows , they are :

UUUUUU
UUDUDU
UUDDDU

When we see the patterns on Games 4 , 8 and 20 , they are :

DDUUDD
DDUU D
DDUUDD

Which ones do you really mean , the Excel rows 4 , 8 and 20 , or the rows which have the numbers 4 , 8 and 20 in column A ?

More importantly , can you say why only these are the matches ? Why is it that the other rows do not match ?

Narayan

The ones in Column A
 
Please check the file, maybe something like this?

I do not understand what you mean???

I want them to be horizontal. The attached side number sheet was an EXAMPLE of how I wanted to run kind of. I place in the letters , hit a button and it finds the pattern like it and the NEXT row UP

I was hoping to do the same thing with E's and O's. Just replace the letters U and D with an E and O and do the SAME EXACT look ups. And, it does not matter where we place the answers. They do not need to be on the next sheet, they can be on the same sheet or even high light the row for me to hit next to see the next one down. I will eventually have 3000 rows filled up to look at
 
Last edited:
You want to say, with criteria ie: DUUUDD you need to retrieve value in Column A? Is that what you want? Im kind, yes Im lost here
 
You want to say, with criteria ie: DUUUDD you need to retrieve value in Column A? Is that what you want? Im kind, yes Im lost here

I'm on my iPhone but I wanna say A is the game number so No. I'm looking for the SAME EXACT pattern of UDs and the NEXT row up. Then I want to do Es and O's
 
I sure can. Say I wanted to look for all the rows that contain DDUUDD. The only rows I would want to see will be rows 4,8,20. I would not want to see any other rows
Hi, Jack-P-Winner!
Am I missing something or the indicated rows don't match the desired pattern?
Regards!
 
Hi, Jack-P-Winner!
Am I missing something or the indicated rows don't match the desired pattern?
Regards!
Hmm not sir what you mean sir and I'm on my iPhone for another hour )-: I'm basically trying to search for similar (same pattern) in all 2500 rows PLUS the one above that row. In other words if UUDD is in row 4 and I found UUDD in rows 7,25,45 ibwould want to see what's in rows 7-8,25-26 and 45-46. It's that simple ad far as the explanation goes
 
Hi Jack-P-Winner,
I think another source of confusion is the use of the word "row". In Excel, each line is a row, with a set cell address. When you say "go up", that usually means go up the sheet, which is 1 row value less (row 6 is above row 7). In your workbook, it looks like you have been referring to the Game#? Is that correct?

I'm basically trying to search for similar (same pattern) in all 2500 rows PLUS the one above that row[game].
If we are searching all 2500 games to start with, why would we need to go back and search an additional game? Is there something that would change with the criteria about that game?
 
Hi, Jack-P-Winner!
Would you please upload again the file highlighting (if you found any unused color :p) the rows where you say there're matches?
Regards!
 
Hi Jack ,

I am still not sure that this is what you are looking for ; can you check ?

Narayan
 

Attachments

  • Us and Ds.xlsm
    20.6 KB · Views: 10
Hi Jack-P-Winner,
I think another source of confusion is the use of the word "row". In Excel, each line is a row, with a set cell address. When you say "go up", that usually means go up the sheet, which is 1 row value less (row 6 is above row 7). In your workbook, it looks like you have been referring to the Game#? Is that correct?


If we are searching all 2500 games to start with, why would we need to go back and search an additional game? Is there something that would change with the criteria about that game?

I am talking about going up a game number yes. I would not need to go back and search the same game but I would want to search all 2500 games. I am basically looking to put the criteria (UUDDUU or UUDD or UUDDD) could be any combination of a U and D or an E or O and search all 2500 games for a similar criteria likeness PLUS the game above. Does that make better sense?
 
Hi Jack ,

I am still not sure that this is what you are looking for ; can you check ?

Narayan

I can not see what this does. I place the criteria in the cell (UDDDDD) and press enter. I see it calculating but nothing happens. I would want it to find the UDDDDD PLUS the game above it and perhaps highlight them or place them all on the next sheet
 
Hi ,

I would have thought you would take up the challenge.

A .xlsm file means there is a macro ; if you can click on macros , you should be able to see which macro needs to be run.

Narayan
 
Here is an example of a sheet that I had to copy and paste each one by one. I am looking for EOEO. Even though there are 5 letters and I am concerned with the 5th letter, my goal is to find all the EOEO and the game above it. I know this makes sense to someone that can help me with a macro. This is so time consuming
 

Attachments

  • EOUD.xlsx
    24.4 KB · Views: 12
Back
Top