2 way data table search for data

[madmathsman]

Hi

Have a 2 way data table, example in pic, and want to create a "search" function where you can enter a row title and it outputs the column title if that title has data in.

Eg in example, if you enter "Mon" in search it would output: Maths, Eng on seperate rows.

Any help or advice welcome

Thanks

 

[Georgiboy]

Welcome to the forum,

If you have the TOCOL function then maybe the below:

Book1
	B	C	D	E	F	G	H	I	J	K
2		Maths	English	History	Geography	Music
3	Mon	Maths	English						Day:	Subjects:
4	Tue		English						Fri	Maths
5	Wed				Geography					Geography
6	Thu		English							Music
7	Fri	Maths			Geography	Music
8	Sat	Maths
9	Sun			History		Music
Sheet1

Cell Formulas
Range	Formula
K4:K6	K4	=TOCOL(FILTER(C3:G9,B3:B9=J4,""),1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J4	List	=$B$3:$B$9

Here's an option without TOCOL:

Book1
	B	C	D	E	F	G	H	I	J	K
2		Maths	English	History	Geography	Music
3	Mon	Maths	English						Day:	Subjects:
4	Tue		English						Fri	Maths
5	Wed				Geography					Geography
6	Thu		English							Music
7	Fri	Maths			Geography	Music
8	Sat	Maths
9	Sun			History		Music
Sheet1

Cell Formulas
Range	Formula
K4:K6	K4	=LET(x,XLOOKUP(J4,B3:B9,C3:G9),TRANSPOSE(FILTER(x,x<>"")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J4	List	=$B$3:$B$9

 

[madmathsman]

Hi

Realised I only have Office 2016 and I don't have TOCOl,XLOOKUP or FILTER (unless I'm being stupid), im guessing that makes the solution much harder?

 

[Georgiboy]

Do the subjects have to be in seperate cells or can they be in the same cell but new row as below:

Math.xlsm
	A	B	C	D	E	F
1		Maths	English	History	Geography	Music
2	Mon	Maths	English
3	Tue		English
4	Wed				Geography
5	Thu		English
6	Fri	Maths			Geography	Music
7	Sat	Maths
8	Sun			History		Music
9
10
11
12
13
14	Day:	Subjects:
15	Fri	Maths Geography Music
Sheet1

Cell Formulas
Range	Formula
B15	B15	=GetSub(A2:A8,B1:F1,B2:F8,A15)

Cells with Data Validation
Cell	Allow	Criteria
A15	List	=$A$2:$A$8

 

[madmathsman]

Same cell but new row works.
I can't get the formula to work, or the allowing List for cell A15, but thats exactly the output I want

Its good to know the idea is actually possible

 

[madmathsman]

Ohh, I Understand the GetSub is probably a placeholder, oops.
But yeah, that concept works, just how to do it in Excel 2016

 

[Georgiboy]

Sorry, i probably should have explained that.

So this was done with a custom function in the form of VBA code, the code used is below:

Function GetSub(x As Range, y As Range, t As Range, sv As String)
    Dim xVar As Variant, yVar As Variant, tVar As Variant
    Dim a As Long, s As Long, r As Long
    Dim oVar() As Variant
 
    xVar = x.Value
    yVar = y.Value
    tVar = t.Value
 
    a = Application.Match(sv, xVar, 0)
 
    For s = 1 To UBound(yVar, 2)
        If tVar(a, s) <> "" Then
            ReDim Preserve oVar(r)
            oVar(r) = tVar(a, s)
            r = r + 1
        End If
    Next s
 
    GetSub = Join(oVar, vbCrLf)
End Function

This has been used on the worksheet as below:

Math.xlsm
	A	B	C	D	E	F
1		Maths	English	History	Geography	Music
2	Mon	Maths	English
3	Tue		English
4	Wed				Geography
5	Thu		English
6	Fri	Maths			Geography	Music
7	Sat	Maths
8	Sun			History		Music
9
10
11
12
13
14	Day:	Subjects:
15	Fri	Maths Geography Music
Sheet1

Cell Formulas
Range	Formula
B15	B15	=GetSub(A2:A8,B1:F1,B2:F8,A15)

Cells with Data Validation
Cell	Allow	Criteria
A15	List	=$A$2:$A$8

Let me know if you need to know where to put the code etc...

EDIT: The result cell will need to have wrap text format set
EDIT 2: This is currently returning the value from the table area and not the heading, i can change it to return the headings if you plan to use say an 'x' in the table to denote that subject.

 

[Georgiboy]

This is the code that will return the value in the header instead:

Function GetSub(x As Range, y As Range, t As Range, sv As String)
    Dim xVar As Variant, yVar As Variant, tVar As Variant
    Dim a As Long, s As Long, r As Long
    Dim oVar() As Variant
    
    xVar = x.Value
    yVar = y.Value
    tVar = t.Value
    
    a = Application.Match(sv, xVar, 0)
    
    For s = 1 To UBound(yVar, 2)
        If tVar(a, s) <> "" Then
            ReDim Preserve oVar(r)
            oVar(r) = yVar(1, s)
            r = r + 1
        End If
    Next s
    
    GetSub = Join(oVar, vbCrLf)
End Function

 

[madmathsman]

Wow, I'll try this out and let you know If i get into any problems, thanks very much

 

[madmathsman]

Thanks very much, that worked perfectly