Lookup all occurences and return their column headings
 

Hi all

Please see the attached.

I am trying to use the value in column G to find which columns on that row contain this value. I then want to list the column headings for each column which contain that value. So far I have something working that lists the first column heading, but I need to list all column headings, ideally in a single cell separated by commas.

Note that some cells contain the lookup letter, in addition to another letter. These must not be included in the above formula. I also need a way to list the column headings of these guys (which contain 2 letters, one of which is in column G) in a separate cell.

Please help.

Thanks.

Lookup all occurences and return their column headings
 

On Thu, 13 Sep 2012 16:29:24 +0000, grantt wrote:


Hi all

Please see the attached.

I am trying to use the value in column G to find which columns on that
row contain this value. I then want to list the column headings for each
column which contain that value. So far I have something working that
lists the first column heading, but I need to list all column headings,
ideally in a single cell separated by commas.

Note that some cells contain the lookup letter, in addition to another
letter. These must not be included in the above formula. I also need a
way to list the column headings of these guys (which contain 2 letters,
one of which is in column G) in a separate cell.

Please help.

Thanks.


+-------------------------------------------------------------------+
|Filename: book2.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=580|
+-------------------------------------------------------------------+

This can be accomplished with a long, nested formula, that examines each cell in turn; or more simply with an easy to write User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Note that this workbook will need to be saved as an .xlsm or .xlsb workbook, as an .xlsx workbook cannot contain macros.

To use this User Defined Function (UDF), enter a formula like

=MatchG($A2:$F2,$G2) to obtain the exact match or
=MatchG($A2:$F2,$G2,TRUE) to obtain a match if the value in G is found anywhere in the other cells

============================================
Option Explicit
Function MatchG(RangeToSearch As Range, sFind As String, Optional Inclusive As Boolean = False) As String
Dim c As Range
Dim sTemp As String
If Inclusive = False Then
For Each c In RangeToSearch
If UCase(c.Text) = UCase(sFind) Then
sTemp = sTemp & ", " & Cells(1, c.Column).Text
End If
Next c
Else
For Each c In RangeToSearch
If InStr(1, c.Text, sFind, vbTextCompare) 0 Then
sTemp = sTemp & ", " & Cells(1, c.Column).Text
End If
Next c
End If

MatchG = Mid(sTemp, 3)

End Function
===============================================

No one able to take a crack at this?

I think 4 hours is slightly short a time to be giving this a bump.
Give people a chance!

Lookup all occurences and return their column headings
 

On Thu, 13 Sep 2012 20:30:00 +0000, grantt wrote:

No one able to take a crack at this?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+


Perhaps if you indicate the problem with my posted answer, someone might be able to add a suggestion.

Thank you! This works wonderfully!

Lookup all occurences and return their column headings
 

On Fri, 14 Sep 2012 07:53:44 +0000, grantt wrote:

Thank you! This works wonderfully!


Glad to help. Thanks for the feedback.
By the way, this does a case-INsensitve match (ie "a" will match "A"). If you need a case-sensitive match, only minor modifications would be required.