Search and get correspondent positions

excel-2.jpg
excel-help-needed.xlsx

This file has got few cells which are showing "NO", I want to search this grid i.e P1 to AD23, check that where are the "no" cells located and than in cells R31 and S31, want the corresponding values of each "no" cell.

For e.g:

For P4 the value is "no" so in R31 I need 1478 and in S31 I need 1912, that are two values of "no" in P4.

Similarly for next "no" i.e Q3, I need values in next row R32=1478 and S32=2285

it should continue in rows for each "no", in this file, there are 11 cells with "no" so there should be 11 rows showing 2 corresponding values of each "no".

Many Thanks.

Maybe:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

You are a star it worked great, perfectly as I wanted. Thank you so so much

Can you make a little change, instead of using two columns, can we show results in 1 column with comma separated values. The result in rows is ok, but instead of using columns R and S, can we use only R and show both the values with comma separation?

Do you mean like this?

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Do you mean like this?

Please Login or Register  to view this content.

Perfectly this....thank you thank you thank you so very much

You're welcome. Glad to help out and thanks for the feedback.

Hi

IS there a way that I can reference it to another sheet, for e.g I have the grid which has got "no" values on Sheet 2 so I need to search "no" on sheet 2 and display results on sheet 1.

Originally Posted by xlbiznes

Just change this line of code of @JOHN H. DAVIS

from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Run-time error: 1004
Application-defined or object-defined error

Originally Posted by xlbiznes

Just change this line of code of @JOHN H. DAVIS

from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

When i run it on sheet2, it is working fine but i need results in sheet 1, where it is giving following error:

Run-time error: 1004
Application-defined or object-defined error

Originally Posted by xlbiznes

This code should be under the sheet1 code window.

Yes I tried that on sheet1, it is still showing same error

Maybe:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

Thank you so much, working perfect!

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

Agadir report complete - (DOC 2).xlsm

In this file I want the result in 'Sheet1' Cell 'A303', I tried putting it but it's showing blank no error, no results :/ can you check please

Try:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Try:

Please Login or Register  to view this content.

Wow I need to understand this code, how it is working.....thanks a lot John, really thankful to you.

You're welcome. You're previous request was to have the results go too Column R. So it had to be adjusted for that. ie. Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2).Value). It also started the search from Row2 on Sheet2, therefore another adjustment was made to take that into account.

Originally Posted by JOHN H. DAVIS

You're welcome. You're previous request was to have the results go too Column R. So it had to be adjusted for that. ie. Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2).Value). It also started the search from Row2 on Sheet2, therefore another adjustment was made to take that into account.

hmmmm perfect, thanks man, thanks a lot.

Sorry for getting late, can I limit the results to 15, like the number of "no", if they are above 15, than in my final sheet 1 list, i just get 15 results and not more?