Lookup all occurences and return their column headings

[grantt]

Hi al

Please see the attached

I am trying to use the value in column G to find which columns on tha
row contain this value. I then want to list the column headings for eac
column which contain that value. So far I have something working tha
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 anothe
letter. These must not be included in the above formula. I also need
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
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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
===============================================

 

[grantt]

Hi all

Please see the attached.

I am trying to use the value in column G to find which columns on tha
row contain this value. I then want to list the column headings for eac
column which contain that value. So far I have something working tha
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 anothe
letter. These must not be included in the above formula. I also need
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.

No one able to take a crack at this

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

 

[Ron Rosenfeld]

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.

 

[grantt]

;1605489']On Thu, 13 Sep 2012 16:29:24 +0000, grant

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 fo each
column which contain that value. So far I have something working that
lists the first column heading, but I need to list all colum 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 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 eac
cell in turn; or more simply with an easy to write User Define
Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visua
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 .xls
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 foun
anywhere in the other cells

============================================
Option Explicit
Function MatchG(RangeToSearch As Range, sFind As String, Optiona
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
===============================================

Thank you! This works wonderfully

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

 

[Ron Rosenfeld]

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.