Ugh! Need help w/ multiple rows of data from a selected drop down list (Google Sheets!)

[thp510]

I have the following rows of data in my sheet. Here are six as an example, but I essentially have over 1000 rows of data.

	A	B	C
1	Account	Owner	Segment
2	Tesla	Jess	COM
3	Brookstone	Kelly	SMB
4	GoPro	Jess	SMB
5	Apple	Linda	ENT
6	Google	Kelly	ENT
7	Netflix	Jess	COM

What I want to do is have a drop down list or a pick list setup so that when I select Jess from the picklist, all of Accounts show up like this...

	E	F	G
1	Pick Rep:	Jess
2
3	Account	Segment
4	Tesla	COM
5	GoPro	SMB
6	NetFlix	COM
7

What excel formula do I put in cell E4 (and the rest of the rows in that column downwards) so that every time I change the account owner in cell F1, the appropriate list of accounts show up?

 

[jdellasala]

This is in Excel. I don't know Sheets well enough to translate, but maybe it will give you an idea of how to do it.

Book1
	A	B	C	D	E	F	G
1	Owner	Segment	Account		Owner	Segment	Account
2	Jess	COM	Netflix		Jess	COM, SMB	Netflix, Tesla, GoPro
3	Jess	COM	Tesla
4	Jess	SMB	GoPro
5	Kelly	ENT	Google
6	Kelly	SMB	Brookstone
7	Linda	ENT	Apple
Sheet1

Cell Formulas
Range	Formula
F2	F2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B7,A2:A7=E2,"None")))
G2	G2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C2:C7,A2:A7=E2)))

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$7

 

[thp510]

This is in Excel. I don't know Sheets well enough to translate, but maybe it will give you an idea of how to do it.

Book1
	A	B	C	D	E	F	G
1	Owner	Segment	Account		Owner	Segment	Account
2	Jess	COM	Netflix		Jess	COM, SMB	Netflix, Tesla, GoPro
3	Jess	COM	Tesla
4	Jess	SMB	GoPro
5	Kelly	ENT	Google
6	Kelly	SMB	Brookstone
7	Linda	ENT	Apple
Sheet1

Cell Formulas
Range	Formula
F2	F2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B7,A2:A7=E2,"None")))
G2	G2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C2:C7,A2:A7=E2)))

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$7

Thank will check this out!

 

[thp510]

Funny enough, someone posted an answer in a separate Google-Sheets forum, but it's pretty simple. It's actually just putting in the following in cell E4 (on my example):

=FILTER($A:$A, $B:$B=$F$1)

Very simple