Search and get correspondent positions

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
View attachment excel-help-needed.xlsx
excel-2.jpg

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.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

Attachments

  • excel-help-needed.xlsm
    32.1 KB · Views: 17

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
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

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

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
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?
 

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
Suppose this grid which I attached is on Sheet2, and I want these comma separated list in Sheet1, how can we reference that?
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

Attachments

  • excel-help-needed_2.xlsm
    36.9 KB · Views: 10

cytop

New member
Joined
Dec 6, 2013
Messages
4
Reaction score
0
Points
0
@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)
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Thanks cytop

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