Search and get correspondent positions

View attachment 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.

Have a look at this and give it a try.
Alt + F8 will bring up the macro list and you can run it from there.
Hope I've commented the macro enough for you to be able to follow what is happening.

Good Luck with your project
NoS

NoS said:

Have a look at this and give it a try.
Alt + F8 will bring up the macro list and you can run it from there.
Hope I've commented the macro enough for you to be able to follow what is happening.

Good Luck with your project
NoS

Click to expand...

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?

Suppose this grid which I attached is on Sheet2, and I want these comma separated list in Sheet1, how can we reference that?

Hi Kamran

Sorry for the long response time, my schedule and our time zones don't make for great efficiency.

I've renamed the sheets in the workbook and include 3 different macros in attempt to hit the moving goal posts.

Hope you can adjust things to suit your needs.




Good luck

@NoS
Compare replies in the thread on ExcelForum before wasting your time: excelforum.com/excel-programming-vba-macros/978727-search-and-get-correspondent-positions.html (Excuse non working 'link'... Can't post links yet)

Thanks cytop

May those who cross post all be taxi cab operators, and may they always be second to arrive for a fare.