Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 =============================================== |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
I think 4 hours is slightly short a time to be giving this a bump.
Give people a chance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
|
|||
|
|||
Quote:
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select minimum with duplicate values to return column headings | Excel Worksheet Functions | |||
Lookup column headings? | Excel Discussion (Misc queries) | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
Lookup a row and column and return value | Excel Worksheet Functions | |||
lookup from one column return value from another? | Excel Discussion (Misc queries) |