Find Value in Table and Return Column Header/Row Header for Each Occurrence

[jrussell19]

Hi all,

I'll simplify the table to help, but effectively I need to return the Column Header and Row Header of each occurrence of a specific value in a table. i.e.

Value: A

A B C D E F G
1 X X X X A X X
2 X X A X X X X
3 X X X X X X X
4 X X X X X X A
5 X X X X X X X
6 X X X A X X X
7 X X X X X A X

This needs to return:
Row Header Column Header
1 E
2 C
4 G
6 D
7 F

The initial table does not have a defined amount of rows. Additional rows will be added throughout the year.

I've been pulling my hair out with match,index,offset etc but can't for the life of me work it out! Any help would be absolutely amazing!

Thanks very much.

 

[vikas9385]

Function MyROW(MyVAL)    With ActiveSheet.UsedRange
        FFIND = False
        For J = .Column + 1 To .Column + .Columns.Count
            For I = .Row + 1 To .Row + .Rows.Count
                If (Cells(I, J) = MyVAL) Then
                    FFIND = True
                    GoTo MyEND
                End If
            Next I
        Next J
MyEND:
        If (FFIND) Then MyROW = Cells(I, .Column)
    End With
End Function
Function MyCOLUMN(MyVAL)
    With ActiveSheet.UsedRange
        FFIND = False
        For J = .Column + 1 To .Column + .Columns.Count
            For I = .Row + 1 To .Row + .Rows.Count
                If (Cells(I, J) = MyVAL) Then
                    FFIND = True
                    GoTo MyEND
                End If
            Next I
        Next J
MyEND:
        If (FFIND) Then MyCOLUMN = Cells(.Row, J)
    End With
End Function

 

[jrussell19]

Hi Vikas,

Thanks for replying.

Although I wish I knew how VBA worked, I barely have any idea how to apply that formula.

Is there a way of using formulas to get to the same result?

Thanks in advance.

 

[miss_ell]

Hello,

I am able to provide formulas, however I have not yet worked out how to list only those with matches. But hopefully it will give you a start.

With K for the row header and L for the column header, paste these from row 2 and copy down:

K2 =IF(L2="","",A2)
L2 =IF(ISERROR(MATCH($J$2,B2:H2,0)),"",INDEX($B$1:$H$1,1,MATCH($J$2,B2:H2,0)))

 

[miss_ell]

Hello again,

I realised that I've used the column and row headers as part of the table in error. Please ignore my previous formulas (unless they work for you ). Use these instead, and copy down:

J1 =IF(K1="","",ROW(K1))
K1 =LEFT(IF(ISERROR(MATCH($I$1,A1:G1,0)),"",ADDRESS(ROW(),MATCH($I$1,A1:G1,0),4)),1)

I still have spaces where they don't match though.

 

[Peter_SSs]

Is it possible the the value in question (A in the example) could occur more than once on a given row, meaning the results might need to be like this?

1 E
1 F
1 G
2 C
4 G
etc

 

[jrussell19]

miss_ell: Thanks, this is definitely further than I got! As you say it's just the spaces.

Peter_SSs: The value will only ever occur once on each row, however it could occur more than once on each column.

Thanks for your help! Slowly getting there!

 

[Peter_SSs]

Formulas in A10 & B10 copied down as far as you might ever need. That is, 7 rows for this sample data.

A10 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		A	B	C	D	E	F	G		Value:	A
2	1	X	X	X	X	A	X	X
3	2	X	X	A	X	X	X	X
4	3	X	X	X	X	X	X	X
5	4	X	X	X	X	X	X	A
6	5	X	X	X	X	X	X	X
7	6	X	X	X	A	X	X	X
8	7	X	X	X	X	X	A	X
9
10	1	E
11	2	C
12	4	G
13	6	D
14	7	F
15
16
Headers 1

If you wanted to keep the individual formulas a bit simpler, and were happy to use some helper columns (that could be hidden), then here is an alternative.

L1 houses a 0, or is empty.
L2 & M2 copied down to the last row of the table.
A10 & B10 again copied down as far as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		A	B	C	D	E	F	G		Value:	A	0
2	1	X	X	X	X	A	X	X				1	5
3	2	X	X	A	X	X	X	X				2	3
4	3	X	X	X	X	X	X	X				2
5	4	X	X	X	X	X	X	A				3	7
6	5	X	X	X	X	X	X	X				3
7	6	X	X	X	A	X	X	X				4	4
8	7	X	X	X	X	X	A	X				5	6
9
10	1	E
11	2	C
12	4	G
13	6	D
14	7	F
15
16
Headers 2

 

[jrussell19]

Hi Peter,

This is perfect - thank you very much!

My follow up question however, is a bit more tricky... but i'm sure you can do it (as I can't get my head around it!)

The table I use is filled with dates, so to translate from the above: Any date where it matches the specified date, return row/column header.

But for the next step I need it to do: Any date where it is less than the specified date, return row/column header.

I've managed to get to the point where it can list the row headers, by changing the the countif/if formulas to "<". But now I can't return the column header due to there being more than one occurrence on each row (as you mentioned previously).

Thank you very much in advance!

FYI: I used the 1st formulae without the helper columns.

 

[Peter_SSs]

But for the next step I need it to do: Any date where it is less than the specified date, return row/column header.

... But now I can't return the column header due to there being more than one occurrence on each row

Not clear. The red is singular, the blue is plural.

What is to be returned if more than one (or even all 7) dates in the row are less than the specified date being used as a comparison?
Some sample data and expected results may help clarify.