Return the column header for every value 'x' along each row in table

**pcassidy** · 01-20-2013, 06:59 PM

Hi,

The formula I am looking to write is;
"For every instance of "RULE!" in this row, return every corresponding value from the table's header row".

I have searched through the forum and although there are similar queries using INDEX and MATCH with column data, I have been unable to convert those formulae to refer to ROW data.

I have attached a workbook for reference, (this is a small subset of a very large workbook of rules that applies to investment portfolios).
I would like to write the formulae down column B (coloured orange), referring to the data in C2:G8.

The formula would 'look' along the relevant row and return the header from each column that contains the word "RULE!"
The formula once written should return the values I have entered manually in column B.
The formula only 'counts' the word "Rule!" and ignores any other text (so the values "Active" and "N/A" are ignored)

Thank you in advance for your help and for hosting a great forum!
Cheers
pcassidy

**FDibbins** · 01-20-2013, 07:42 PM

Hi pcassidy and welcome to the forum

Im sure some-one will come up with a far more eligant solution for you, but until they do, try this brute force, copied down...

=IF(C2="Rule!",C$1&",","")&IF(D2="Rule!",C$1&",","")&IF(E2="Rule!",C$1&",","")&IF(F2="Rule!",C$1&",","")&IF(G2="Rule!",C$1&",","")

**pcassidy** · 01-20-2013, 07:59 PM

Thank you very much for your suggestion FDibbins.

That would definitely work on the small table that I posted, but the problem is that the actual table of rules I want to apply this to is alot larger.
So while in the posted example spreadsheet there are only 5 columns with headers, in the full database I have over 100 headers - which would require a gigantic nested IF.

**FDibbins** · 01-20-2013, 08:06 PM

Yup, I kinda figured you would say that lol

**dredwolf** · 01-20-2013, 08:58 PM

See if you can use this : pcassidy.Rule Set Query.sol1.xlsx

I used a helper sheet (which could be hidden if desired) to hold the interim results

The named range Headers is dynamic to about 200 headers

Hopefully As FDibbins says, someone can come up with a more elegant solution....but this will work

**Tony Valko** · 01-20-2013, 08:59 PM

If you want multiple results all in a single cell then you're going to need some form of VBA to do this.

Ideally, you'd want all of the processing to be done with the VBA code. I'm not much of a programmer but if no one else comes up with a 100% code solution I have a solution that uses both a VBA function combined with worksheet functions.

I'll follow the thread to see if anyone else chimes in.

**pcassidy** · 01-20-2013, 10:08 PM

Hi dredwolf,

Thank you very much for your efforts.
I am just testing out your suggestion on my full spreadsheet - and while I have no idea how those formulae work or how the helper sheet works, it seems to be doing the job!

I will get back to you and the forum shortly.
Cheers!

**dredwolf** · 01-20-2013, 10:14 PM

Thanks

I hope they work out okay for you !

**pcassidy** · 01-20-2013, 10:22 PM

Hi dredwolf,

I tested this out on the full worksheet and it is great! You have quite literally saved me hours of frustration!
I have over 250 rows of rules and over 100 portfolio columns to apply this to but somehow with that magic helper sheet it all works a charm.

Thank you once again!

**dredwolf** · 01-20-2013, 10:26 PM

You are very welcome

**Tony Valko** · 01-21-2013, 10:12 AM

Here's that VBA UDF method...

Copy the code below and paste it into a general module:

Please Login or Register  to view this content.

Then, enter this array formula in B2 and copy down as needed:

=SUBSTITUTE(TRIM(aconcat(IF(C2:G2="Rule!"," "&C$1:G$1,"")))," ",", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**pcassidy** · 01-21-2013, 06:46 PM

Hi Tony,

Thank you as well for your suggestion.
Again, I have no idea how this works, but it does, it works perfectly! It is a very tidy and most excellent solution!

I really appreciate your response and efforts to solve my problem.
Cheers!

**Tony Valko** · 01-21-2013, 06:49 PM

You're welcome. Thanks for the feedback!

Return the column header for every value 'x' along each row in table

LinkBack

Thread Tools

Rate This Thread

Display

Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Re: Return the column header for every value 'x' along each row in table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions