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

How can I search for a number and have group go to another page

Jack-P-Winner

Jack-P-Winner

Member
I want to be able to search for a number and have it go through approx. 2000 rows and pull out all the columns that have this number in it along with the row above it. In the example attached I am looking for the number 36 and the entire row it it is PLUS the one above it and set it out to the side of it or to another sheet. Please see attached
 

Attachments

  • Search for numbers.xlsx
    16.2 KB · Views: 10
Maybe this makes better sense

I want to be able to say look for all the 36's in a group of numbers down several rows. Each row can have up to 6 double digits (15,34,55 ect...) If I have a row say like this 11-25-29-45-36-70 and I want to find all the 36's and place them on say sheet two how can I write a formula or Macro to do this ? I want to place all the rows it finds with a 36 along with the row above it. So If a 36 is in row 59 I want it to grab row 59 and row 58 and place them both on sheet 2. Now, 36 is just an example. I need to be able to look up any 2 digit number 1-99
 
Hi Jack,

There is some confusion about your requirements, however, look at the attached file, the result is obtained by using Advanced Filter with the criteria as B1:G7 on Sheet2.

This can be automated with a macro and the results can be copied and pasted elsewhere as in on another sheet if required
 

Attachments

  • Search for numbers1.xlsx
    17.4 KB · Views: 7
Hi jack,

I attempted your problem and came with an array formula solution. The only hitch is I need to add an extra column H, populated with a RANK formula to rank for the number to search in each indiviual row. The same can be done with MATCH formula also.

I had obtained the result in same sheet, but the same can be done in other sheet also.

So if it OK with you to add an extra column, this is working. Just change the number to search and result will be updated.

Regards,
 

Attachments

  • Search_for_numbers1(1).xlsx
    24.3 KB · Views: 10
kchiba said:
Hi Jack,

There is some confusion about your requirements, however, look at the attached file, the result is obtained by using Advanced Filter with the criteria as B1:G7 on Sheet2.

This can be automated with a macro and the results can be copied and pasted elsewhere as in on another sheet if required
Click to expand...
Kchiba.

I can not seem to get it to work?
 
Somendra Misra said:
Hi jack,

I attempted your problem and came with an array formula solution. The only hitch is I need to add an extra column H, populated with a RANK formula to rank for the number to search in each indiviual row. The same can be done with MATCH formula also.

I had obtained the result in same sheet, but the same can be done in other sheet also.

So if it OK with you to add an extra column, this is working. Just change the number to search and result will be updated.

Regards,
Click to expand...

Somendra,

This seems to work just great!! I will mess around with it in more detail this evening and report back THANK YOU!!!!
 
Somendra,

When I added numbers and drug down the formula it did not work. Not sure what's going on. I have attached and updated the file so you can see what I am talking about. It stops after about 10 rows
 

Attachments

  • Search for numbers.xlsx
    73.1 KB · Views: 8
Hi jack,

The problem is only of refrence. I will try to explain my method so that in future you can change the formula as your need.

In sheet I had use four formula.

1. Helper column formula in cell H5 (Normal formula), This is use to identify row containing the number to be searched say 36. Copy this formula till the data end. I had use below formula (RANK() function, but we can also use any other function such as MATCH() to identify row with the number.

Formula :--> =RANK($F$2,B5:G5)....$F$2 contains the number to be searched say 36 and B5:G5 is relative array of that row.

2. In cell J2 I had count total number of above rows which contain a number. This is again a normal formula, this is use to turn our main formula ON and OFF. Change the refrence if you increase the row. You will get twice the result of this number. So you had to copy the next two main formula twice the row of this number.
=COUNT($H$5:$H$278)

3. In cell K5, I had below formula to get row of data above the row in which the number is found.
=IF((INT((ROWS(K$4:K4)-1)/2+1))<=$J$2,INDEX(A$4:A$278,SMALL(IF(ISNUMBER($H$4:$H$278),ROW($A$4:$A$278)-ROW($A$4)+1),(INT((ROWS(K$4:K4)-1)/2+1)))-1),"")

The red portion of the formula is generating a number series as 1;1;2;2;3;3... and so on as we require the data in that pattern. The blue portion is getting the row number in which number is found and than -1 will return a row above it. Here also you need to change the refrence of green portion which is full data and refrence in SMALL, ISNUMBER, ROW function. Copy this formula across row 5 till Q5.

4. In cell K6, I had below formula to get row of data in which you find the number. The logic is same as above formula but in this -1 is not there. Copy this across row 6 till Q6.
=IF((INT((ROWS(K$4:K4)-1)/2+1))<=$J$2,INDEX(A$5:A$278,SMALL(IF(ISNUMBER($H$5:$H$278),ROW($A$5:$A$278)-ROW($A$5)+1),(INT((ROWS(K$4:K4)-1)/2+1)))),"")

Now select Both K5:Q6 and drag till the end (@to the minimum of row twice of the count of result of formula 2).

Hope this will clear your doubts.

Advise if any issue.

Regards,
 

Attachments

  • Search_for_numbers(1).xlsx
    79.5 KB · Views: 4
Jack,

here is an alternative to Somendra's great solution, it is VBA driven, please try it out

cheers
kanti
 

Attachments

  • Count36.xlsm
    78.4 KB · Views: 5
Somendra, that part where it shows it is working is actually an OOPs moment, I should have suppressed it
 
Somendra Misra said:
Hi jack,

The problem is only of refrence. I will try to explain my method so that in future you can change the formula as your need.

In sheet I had use four formula.

1. Helper column formula in cell H5 (Normal formula), This is use to identify row containing the number to be searched say 36. Copy this formula till the data end. I had use below formula (RANK() function, but we can also use any other function such as MATCH() to identify row with the number.

Formula :--> =RANK($F$2,B5:G5)....$F$2 contains the number to be searched say 36 and B5:G5 is relative array of that row.

2. In cell J2 I had count total number of above rows which contain a number. This is again a normal formula, this is use to turn our main formula ON and OFF. Change the refrence if you increase the row. You will get twice the result of this number. So you had to copy the next two main formula twice the row of this number.
=COUNT($H$5:$H$278)

3. In cell K5, I had below formula to get row of data above the row in which the number is found.
=IF((INT((ROWS(K$4:K4)-1)/2+1))<=$J$2,INDEX(A$4:A$278,SMALL(IF(ISNUMBER($H$4:$H$278),ROW($A$4:$A$278)-ROW($A$4)+1),(INT((ROWS(K$4:K4)-1)/2+1)))-1),"")

The red portion of the formula is generating a number series as 1;1;2;2;3;3... and so on as we require the data in that pattern. The blue portion is getting the row number in which number is found and than -1 will return a row above it. Here also you need to change the refrence of green portion which is full data and refrence in SMALL, ISNUMBER, ROW function. Copy this formula across row 5 till Q5.

4. In cell K6, I had below formula to get row of data in which you find the number. The logic is same as above formula but in this -1 is not there. Copy this across row 6 till Q6.
=IF((INT((ROWS(K$4:K4)-1)/2+1))<=$J$2,INDEX(A$5:A$278,SMALL(IF(ISNUMBER($H$5:$H$278),ROW($A$5:$A$278)-ROW($A$5)+1),(INT((ROWS(K$4:K4)-1)/2+1)))),"")

Now select Both K5:Q6 and drag till the end (@to the minimum of row twice of the count of result of formula 2).

Hope this will clear your doubts.

Advise if any issue.

Regards,
Click to expand...
One thing I have noticed is FOR SURE you guys and gals are AMAZING in what you know and what you can do. I am not sure if this is challenging or even if it can be done. I would like to enter "up" to 6 digits 0-99 in "up to" 6 cells and search for all those digits throughout a few thousand rows. Now, here is where it MAY get tricky. I want to be able to at least find 3 of the numbers in any given row. Please see the attached and let me know if you have any questions

THANKS YOU!!!!!!!
 

Attachments

  • SEARCH FOR PLAYED NUMBERS.xlsx
    11.7 KB · Views: 4
Hi Jack,

Thanks for your kind words.

Here is one propose solution. In Cell J15 you can select the number of matching numbers you require, and cell S11:Y19 will fetch you the result.

Note while adpting the soltuion to your databse do keep in mind to change the refrences.

Just advise if any issue.

Regards,
 

Attachments

  • SEARCH_FOR_PLAYED_NUMBERS(1).xlsx
    14.1 KB · Views: 8
Hi Jack,

Here is a less elegant solution on Sheet 5

Hi Somendra,
Confused, because beyond my capacity and comprehension.
 

Attachments

  • SEARCH_FOR_PLAYED_NUMBERS_2.xlsx
    18 KB · Views: 6
You must log in or register to reply here.
Back
Top