Having trouble with TEXTJOIN and FILTER

zjamespryor

New Member
Joined
Aug 7, 2014
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello Spreadsheet Experts,
I am building this in Google Sheets. I am having trouble coming up with a formula to filter through some data and join the results together in a single cell. In the spreadsheet below, the data being collected by a Google Form is on the right. On the left I've put my desired results for how I'd like to filter out the data on another sheet. I've included a small table below that as a place to figure out the correct formula. Thank you in advance for any help you can offer.
Book1
ABCDEFGHIJK
1Group Selection:Group 1
2CampusGold PartnerSilver PartnerBronze PartnerNameCaliforniaFloridaNew YorkNorth CarolinaVirginia
3CaliforniaJamesNancyJamesGoldBronzeSilver
4FloridaChris, SusanNancyChrisGoldSilverBronze
5New YorkChrisJamesSusanGoldBronzeSilver
6NancySilverBronzeGold
7
8
9CampusGold PartnerSilver PartnerBronze PartnerGroup 1Group 2
10CaliforniaNorth Carolina
11FloridaVirginia
12New York
13
14
Sheet1
Cell Formulas
RangeFormula
A3:A5A3=IFERROR(INDEX(F10:G12,,MATCH(B1,F9:G9,0)),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B1List=$F$9:$G$9
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try this in B3 and copy throughout your results table in Sheets:
Excel Formula:
=IFERROR(TEXTJOIN(", ","",FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")
 
Upvote 0
Try this in B3 and copy throughout your results table in Sheets:
Excel Formula:
=IFERROR(TEXTJOIN(", ","",FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")
Hey Kirk, thanks for the help. The result is a blank cell, it doesn't show any values.
 
Upvote 0
Do you have Excel 365? (You should update your profile details to show this). If so, try this and let me know what you see in Excel.
Mrexcel_20220916b.xlsx
ABCDEFGHIJK
1Group Selection:Group 1
2CampusGold PartnerSilver PartnerBronze PartnerNameCaliforniaFloridaNew YorkNorth CarolinaVirginia
3CaliforniaJamesNancy JamesGoldBronzeSilver
4FloridaChris, Susan NancyChrisGoldSilverBronze
5New York ChrisJamesSusanGoldBronzeSilver
6North CarolinaNancy Chris, SusanNancySilverBronzeGold
7Virginia James, Susan 
Sheet3
Cell Formulas
RangeFormula
A3:A7A3=TRANSPOSE(G2:K2)
B3:D7B3=TEXTJOIN(", ",,FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1),""))
Dynamic array formulas.
 
Upvote 0
In Sheets, here is what I have in...
A3:
Excel Formula:
=ARRAY_CONSTRAIN(ARRAYFORMULA(TRANSPOSE(G2:K2)), 5, 1)
B3 and copied throughout results table:
Excel Formula:
=IFERROR(TEXTJOIN(", ","",FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")
There are some minor differences between the Excel version and the Sheets version.
 
Last edited:
Upvote 0
Solution
Thanks, the sheets version works great! Is there a difference to using TRUE vs "" in the text join?
 
Upvote 0
Good question. That should normally be a 1 or TRUE if you want to avoid having TEXTJOIN include extra delimiters when the list of items to concatenate is empty. But in this case, the nested FILTER functions eliminate any empty array elements (the array is "tightened up"). So a cleaner version is to eliminate the 2nd argument altogether. It's better to avoid the quotation marks in my earlier post...Excel complains about them, but Sheets is okay with them. Stick with 1/0 or TRUE/FALSE, unless you know the array passed to TEXTJOIN is cleaned of empty elements (the case here), in which case the 2nd argument can be eliminated (except for a placeholder indicated by the double comma).
Excel Formula:
=IFERROR(TEXTJOIN(", ",,FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")
 
Upvote 0
Good question. That should normally be a 1 or TRUE if you want to avoid having TEXTJOIN include extra delimiters when the list of items to concatenate is empty. But in this case, the nested FILTER functions eliminate any empty array elements (the array is "tightened up"). So a cleaner version is to eliminate the 2nd argument altogether. It's better to avoid the quotation marks in my earlier post...Excel complains about them, but Sheets is okay with them. Stick with 1/0 or TRUE/FALSE, unless you know the array passed to TEXTJOIN is cleaned of empty elements (the case here), in which the 2nd argument can be eliminated (except for a placeholder indicated by the double comma).
Excel Formula:
=IFERROR(TEXTJOIN(", ",,FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")
Thank you for the explanation.
 
Upvote 0

Forum statistics

Threads
1,223,323
Messages
6,171,458
Members
452,405
Latest member
DiamondHand_Jo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top