TEXTJOIN and FILTER functions to concatenate cells that meet certain criteria
ฝัง
- เผยแพร่เมื่อ 28 ส.ค. 2024
- How do you return all cells that meet a certain criteria?
How do you Concatenate the results with a delimiter?
In this video, I show you how to use the TEXTJOIN and FILTER functions to concatenate the results based on certain criteria. As the FILTER function is only available in O365, I also show an alternative approach to that
Workbook can be found here
drive.google.c...
#TextJoin #FilterFunction #microsoftExcel
This was unbelievably helpful.
Who would have thought! 😁😁 You are welcome
The only video which gave a proper solution, thanks man!!!
Thanks for the feedback. Much appreciated
I have been stuck with this for months. Back then, I simply dont know the right question to ask. Thank you sir. You saved my life
You are welcome. Glad to be of help
You are a legend! Thank you! You've saved me so much time.
You are welcome. Thank you so much for the feedback
Your videos are great. You explain things very well. I demand more people subscribe to your channel!
Thank you for your feedback and kind words, it does mean a lot to me.👌 Kindly spread the word so we can have more people benefitting from these videos 😀😀
That’s exactly what I wanted to do today! I googled it and all kinds of nonsense solutions came up. I should have checked here first. You have a new subscriber, thank you!
You are welcome. i am glad that you found the solution helpful. Thanks for subscribing
You did good explanation here. I appreciate the content!
Glad you enjoyed it! Thanks for the feddback
Thank you. Helped me.
You are welcome
Thanks so much for this sir! This formula just saved my life :)
I don't know about saving lives, but yes, you are welcome 😃😀
Just what I was looking for - thanks very much, greatly appreciated
You are welcome. I am glad you found it helpful.
thank you very much, sir. this video is a really big help!💙
That's really good to know. Thanks for the feedback
OMG best video!!!! i WISH i knew this function sooner
You are welcome. It is indeed a lifesaver 😃😄
Thank you! Very helpful.
Thanks Helena for the feedback
Amazing, i knew it was textjoin and filter but I couldnt figure out how
Tricky at first, but it becomes better with time
Thank you very much!!!!
You are welcome
Incredible, thank you so much! Just became a new subscriber :D
You are welcome. Thanks for subscribing, you can spread the word too 😀
Super helpful!
That's really good to know. You are welcome
Thank you!!!
Do you know if you can filter for multiple values using this method
thanks for teaching and this video
may i have a question about
the video at 2:59
how you do that let the value show in the function texting box
Thanks for your feedback. When you select a portion of the formula, you press F9 and it evaluates that portion , some may need to press Fn+F9, but whatever you do, make sure you press CTRL+Z after to undo that action
@@ExcelMoments can't wait to try this
Great... Thank you!
You arew welcome
Can we use in excel 2016?
FILTER is not available in 2016, as far as I know, so this would not work
What if Christiano Ronaldo has visited London three times? Is there a way to avoid double results like
Paris, London, 𝙇𝙤𝙣𝙙𝙤𝙣, 𝙇𝙤𝙣𝙙𝙤𝙣, Abuja, Los Angeles?
Very good question. What you do in such a case is to put a UNiQUE around the FILTER to ensure each city appears once, then apply the TEXTJOIN to glue the cities together
All the data in column B is all different,
what if the data in column B somehow has a couple more same Text ?
its like it become repeated,
it become like this :
Christiano Ronaldo | Paris, Paris, Paris, London, Abuja, Abuja, Abuja, Abuja, LA, LA, LA, LA, LA, LA
do you have solution so it get only just one?
i met a dead end for this matter T_T
Hopefully you have the UNIQUE function, that would make it extremely easy , look at my formula on the FILTER worksheet and simply wrap a UNIQUE around the filter in the formula, so the cities are unique. Let me see if I can modify this and show you an example
Check the modified worksheet here docs.google.com/spreadsheets/d/1Kfbyl6JF3qSm6Enh3eu4BEUEUpO4tNwY/edit?usp=drivesdk&ouid=115481529442131367093&rtpof=true&sd=true
What is the function if you have Excel 2013 to delimit multiple returns? So far I have '=IF(COUNTA(D2)=1, CONCATENATE(D2,": ",A2, ""). Where D2 is 'cities' and A2 are is the person's name.
Without the OCNCAT or TEXTJOIN function, this would be very difficult in Excel 2013, especially if you are not using helper cells
I guess that explains why pre-Office 2016, we used the CONCAT user defined function (UDF) becuase the concatenate and Ampersand have to go element by element as opposed to other functions that can just pick up a range
@@ExcelMoments I used VBA to code the TEXTJOIN function and it works without the Filter function. So I'm still doing the old way. How do I modify this formula allowing the each rows range automatically adjust so I can use Excel as a template to copy/paste data spreadsheets with different numbers of rows? The columns remain the same. Thank you for your time.
thanksssssssssssssssss
You are welcome
Hi,
how can I do this if let's say I type
Name: Harry Kane, Zlatan Ibrahimovic
Result: Barcelona, Paris
Hello Michael
The logic would be
1. Split the text based on the comma delimiter(there's no single function that can do that without VBA) but you can NEST functions to achieve the result
2. lookup the individual elements from 1 above and return the "visited cities"
3. Concatenate the results using TEXtJOIN
See drive.google.com/file/d/1G341DXWRNBd0JOQwukJYMA2s3KGS7-yg/view?usp=sharing for an example using formulas
Assuming search string in E2
=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(MID(SUBSTITUTE($E2,",",REPT(" ",LEN($E2))),LEN($E2)*(SEQUENCE(1+LEN($E2)-LEN(SUBSTITUTE($E2,",","")))-1)+1,LEN($E2))),$A$2:$A$17,$B$2:$B$17))
N.B: It may fail as the entries get longer, limited by how many characters a formula can accommodate
@@ExcelMoments thanks for the reponse. Will try this.
i have a spreadsheet with a column for first name and a column for last name, i need to combine the 2 cells as , where end date = null
this would give me a list of employees who are active.
currently i have 3 columns for this task (but would like to simplify this down to a single column:
column b (last name) -hidden column column c (first name) -hidden column column d -displayed results column
=FILTER(Compile!$G:$G,ISBLANK(Compile!$M:$M)) =FILTER(Compile!$H:$H,ISBLANK(Compile!$M:$M)) =if(len(B6)+len(C6)=0,"",B6&", "&C6)
any help would be greatly appreciated!
Check docs.google.com/spreadsheets/d/1LyEYSemOZ8NGlt4z3Zrr-j3Zi88xGyXI/edit?usp=shari
ng&ouid=115481529442131367093&rtpof=true&sd=true and see if it describes and solves your problem