• 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

I have been messing around with this and can not seem to get it to work or do not know what I am doing LOL I copid and pasted abound 1400 rows of numbers in columns CDEFG starting with rows 6. I then change the 3 to say a 2 in column J row 15. Then what do I do?
 
Hi Jack,

It has been a long time, so please specify, what you are doing to refresh my memory and we can take i from there.

cheers

kanti
 
Hi Jack,

I think again the issue of range. See the attached file. I had shifted the data from original to Column C to Column G as you told above.

=IFERROR(INDEX(C$6:C$34,SMALL(IF(MMULT(COUNTIF($J$11:$N$11,$D$6:$H$34),ROW($A$1:$A$5)^0)>=$J$15,ROW(C$6:C$34)-ROW(C$6)+1),ROWS(S$11:S11))),"")

Here Red portion is what you need to modify to C$6:C$1400 and $D$6:$H$1400 i.e. your data range (adjust as per youe need). It should work.

Just advise if any issue.

Regards,
 

Attachments

yes it has, hope all is well with you. I want to be able to search up to 6 numbers in a row
If you open this file you will see in columns JKLMN the numbers 14-99-17-21-26 under the 1-5. I want to find that same rung of numbers from the group of numbers in columns DEFGH. In addition, I want to be able to pull out not only the same rung of numbers 14-99-17-21-26 but the row above and below it. So lets say the numbers 14-99-17-21-26 are found in row 12. I want to be able to see row 11 and 13 too. What would be ideal is to extract this date say to sheet 2 if possible. If not no big deal, I an copy and paste it to a separate sheet.

Now , lets say I only want to look for 2 numbers. Then I would change the 4 in column J to a 2 and search for 2 numbers. Make sense? So far I have 2000 rows of data. I will need the date plus up to 6 rows of numbers
 
Somendra Misra said:
Hi Jack,

I think again the issue of range. See the attached file. I had shifted the data from original to Column C to Column G as you told above.

=IFERROR(INDEX(C$6:C$34,SMALL(IF(MMULT(COUNTIF($J$11:$N$11,$D$6:$H$34),ROW($A$1:$A$5)^0)>=$J$15,ROW(C$6:C$34)-ROW(C$6)+1),ROWS(S$11:S11))),"")

Here Red portion is what you need to modify to C$6:C$1400 and $D$6:$H$1400 i.e. your data range (adjust as per youe need). It should work.

Just advise if any issue.

Regards,
Click to expand...
Its obvious I am doing something wrong. Also there is only a place for 5 numbers in columns DEFGH. There should be an I too correct? When I copy and paste my 2000 rows into columns DEFGH and put a new set of numbers in it does not go past row like 26. Do I need to drag down some cells. Sorry for my ignorance but I have tried several things and I am lost
 
Yes you have to mention the complete range in the red portion of the formula. Secondly you may to drag the formulas down untill you see a blank cell.

Regards,
 
What do I do about it not having the 6th location for a number. It would go in column "I" also, do I drag down columns TUVWXY? If so, I should not have to change anything in RED correct? It will change automatically when I drag the cells???
 
You must log in or register to reply here.
Back
Top