PDA

View Full Version : Solved: Search for value in columns and display results of the relevant rows



hunsnowboard
07-14-2010, 04:00 AM
Hi Everyone! I've been struggling with this one for days, but couldn't manage to make it work. So I decided to ask help from you, experts. :)

As you can see in the excel file attached there are two named sheets. I would like to have a macro (or formula) which will search for the value on sheet "results" in cell B2 (validated list) on the "info" sheet and display all the findigs in the "result" sheet.

Let's see an example:

If I make a search for the value AT on the result sheet (cell B2), I would like to display the following result in the table of "result" sheet:

year____code_____initialize country_________countries involved
2006___GVH 52______AT_________________AT, BE, DE, IT, SK
2006___GVH 14_______CY_________________AT, BE, CY, DE, GB
2006___GVH 43_______IT_________________AT, DK, IT
2009___GVH 42_______AT_________________AT, CY, DE, IT, SK

Explanation: On the "info" sheet are the all the information. The results should be displayed on "results" sheet under the headers year, code, Initialize Country, Countries involved (Range B5:E5).
The makro should search for the country code ("results" sheet B2) on the info sheet and find the country code column in the table of the "info" sheet. Then display the year and code information of the row where (for the given country code: AT) there is a number (1 or 2). The number two stands for the initializing country. So in case of the row 2006, GVH 14, you can see that for AT, BE, DE and GB the value is 1, but for CY the value is 2. Which means that CY is the initializing country. In case of the row 2006, GVH 52 you can see that AT has value 2 (which stands for the initializing country) and the other countries involved are BE, DE, IT, SK (all these countries have 1 in the row).

I hope I was understandable! If you have any questions, remarks please ask! Thank you for your help in advance!

p45cal
07-14-2010, 06:48 AM
see attached.

hunsnowboard
07-14-2010, 06:58 AM
Excellent, marvelous, awesome!

Thank you so much!!! :clap: :bow:

SeshuNaresh
03-17-2011, 11:21 PM
Excellent Macro.....