filter multiple columns at the same time

[INN]

Hi, I want to filter column "StoreA" by "agent1" to see which customers helped by agent1 but at the same time I still want to see which customers agent1 helped in StoreB and StoreC. Now if I filter Store A column by agent1, then I wont see which customers he/she helped in storeB and StoreC. I can filter all columns by agent1 without going to individuals columns one by one? Thank you very much.

customer Name	Store A	Store B	Store C
Mary	agent1	agent2	agent3
Alex	agent1	agent2	agent4
Sam	agent2	agent2	agent5
Tim	agent3	agent2	agent1
John	agent1	agent2	agent2
Anna	agent1	agent3	agent3
Tony	agent3	agent3	agent4
Bob	agent4	agent1	agent2

 

[alansidman]

With Power Query Unpivot your data and then filter out all agents except Agent 1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer Name", type text}, {"Store A", type text}, {"Store B", type text}, {"Store C", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"customer Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "agent1"))
in
    #"Filtered Rows"

Book3
	A	B	C	D	E	F	G	H
1	customer Name	Store A	Store B	Store C		customer Name	Attribute	Value
2	Mary	agent1	agent2	agent3		Mary	Store A	agent1
3	Alex	agent1	agent2	agent4		Alex	Store A	agent1
4	Sam	agent2	agent2	agent5		Tim	Store C	agent1
5	Tim	agent3	agent2	agent1		John	Store A	agent1
6	John	agent1	agent2	agent2		Anna	Store A	agent1
7	Anna	agent1	agent3	agent3		Bob	Store B	agent1
8	Tony	agent3	agent3	agent4
9	Bob	agent4	agent1	agent2
Sheet1

 

[DanteAmor]

Check these formulas:

Dante Amor
	A	B	C	D	E	F	G	H	I
1	customer Name	Store A	Store B	Store C		customer Name	Store A	Store B	Store C
2	Mary	agent1	agent2	agent3		Mary	agent1	agent2	agent3
3	Sam	agent2	agent2	agent5		Tim	agent3	agent2	agent1
4	Jim	agent5	agent2	agent3		John	agent1	agent2	agent2
5	Tim	agent3	agent2	agent1		Anna	agent1	agent3	agent3
6	John	agent1	agent2	agent2		Bob	agent4	agent1	agent2
7	Anna	agent1	agent3	agent3		Alex	agent1	agent2	agent4
8	Tony	agent3	agent3	agent4
9	Bob	agent4	agent1	agent2
10	Carlos	agent6	agent3	agent4
11	Alex	agent1	agent2	agent4
12	Daniel	agent6	agent3	agent4
Hoja1

Cell Formulas
Range	Formula
F2:F12	F2	=IFERROR(INDEX($A$2:$A$12, SMALL(IF($B$2:$D$12="agent1", ROW($A$2:$A$12)),ROWS($E$2:E2))-ROW($A$2)+1),"")
G2:I12	G2	=IFERROR(VLOOKUP($F2,$A$2:$D$12,COLUMNS($F$1:G1),0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Or this macro:

Sub SortAgent()
  Dim a As Variant, b As Variant
  Dim i&, j&, k&, m&
  a = Range("A2", Range("D" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1)
    For j = 2 To UBound(a, 2)
      If LCase(a(i, j)) = "agent1" Then
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
        Next
        Exit For
      End If
    Next
  Next
  Range("F2").Resize(k, UBound(b, 2)).Value = b
End Sub

Since I see you have 365, maybe someone will help us with the formula.

 

[Fluff]

Another formula option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	customer Name	Store A	Store B	Store C
2	Mary	agent1	agent2	agent3		agent1	Mary	agent1	agent2	agent3
3	Alex	agent1	agent2	agent4			Alex	agent1	agent2	agent4
4	Sam	agent2	agent2	agent5			Tim	agent3	agent2	agent1
5	Tim	agent3	agent2	agent1			John	agent1	agent2	agent2
6	John	agent1	agent2	agent2			Anna	agent1	agent3	agent3
7	Anna	agent1	agent3	agent3			Bob	agent4	agent1	agent2
8	Tony	agent3	agent3	agent4
9	Bob	agent4	agent1	agent2
10
Data

Cell Formulas
Range	Formula
G2:J7	G2	=FILTER(A2:D9,MMULT(--(B2:D9=F2),SEQUENCE(COLUMNS(B1:D1),,,0)))
Dynamic array formulas.

 

[INN]

Thank you all for the help. I appreciate it. Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

Thanks for the feedback