Mike, since the challenge mentioned data cleaning, here's a simple PQ-M code that does the same: let Source = Excel.CurrentWorkbook(){[Name="fData"]}[Content], dCity = Excel.CurrentWorkbook(){[Name="dCity"]}[Content][City], GetCity = Table.AddColumn(Source, "City", each let desc = [#"Description (Only One City Name)"] in List.Select(dCity, each Text.Contains(desc,_)){0}, type text) in GetCity
@@GeertDelmulle , Cool! That looks like similar logic to my approximate match lookup formula. My video next Wednesday uses List.Accumulate because it seems faster. I learned this from you!!!! : ) : ) I will show: = Table.AddColumn(AddDataTypes, "City", each List.Accumulate(Cities,"", (i,a) => if Text.Contains([Description],a) then a else i)) or if there are multiple names: = Table.AddColumn(GetCityName, "CityWithDups", each Text.Trim(List.Accumulate(Cities,"", (i,a) => if Text.Contains([Description],a) then i&" "&a else i)))
Kudos to you for you giving props to somebody from 13 years ago! (and for being such a good son)! ❤ This is a good tip with a nice combination of old and new! 👌
It is funny, but I tend to remember many of the key concept tricks that I learned from people. It was like a gift when I learned it from them, so I usually remember. Or at least I try to remember lol
Great video!! BYROW iterates by nr. of rows, we can use REDUCE, a formula that iterates only 6 times no matter how many rows the vector has=> more efficient =REDUCE("",G7:G12,LAMBDA(v,i,v&IF(ISNUMBER(SEARCH(i,B7:B62)),i,""))) Also will be noticeable if there are more than one city/cell
@@KishoreKumar-of7sb The hypothesis and the solution of the video are obvious for scenarios of single cities per sentence, not for multiples. My formula was designed to increase only the efficiency and not the functionality. And even if no extra functionality was added, still offers the advantage that is "noticeable" when there are mult. matches Therefore I have used the word "noticeable" and not " can deal with multiple matches". Solving the delimiter "problem" is indecently simple and was not the purpose of the formula. Now that you mention it you force me to also increase the functionality. 😊 =REDUCE("",G7:G12,LAMBDA(v,i,v&IF(ISNUMBER(SEARCH(i,B7:B62)),IF(v="","",",")&i,""))) edit after Mike's last post(YT did not show it as regular reply): "Glad you liked it!!✌ The reason I avoided space and TRIM is because of composed cities like "San Francisco" (or Las Vegas, Los Angeles) that has spaces already. Looks better with other delim plus on a later task you can count the cities based on other delimiter that is not space 😉"
Thank you Mike for the neat lookup formula. The big number concepts 2^15 are so interesting. I remember seeing them for the first time in Ctrl+Shift+Enter. (9.99999E+307 and Match OMega character. I thought that was soo creative.
I was able to figure this out on my own thanks to your teachings. How would you do that with multiple results that you concatenated into a single cell?
Great video, thanks Mike. The old-school Lookup function is great but even better used with the some of the newer functions. The power query method should be an interesting video too. Doing it with the new py functionality would also be an interesting challenge. I would be interested to know you think of the ability to use python on the grid in Excel and whether you plan to produce any videos dealilng with the new functionality when your family circumstances allow. I hope your mom is recovering well from her ill-health.
I do not know python. Bad timing with writing my books (which are on hold now cuz of my Mom) and having to temp. move to California to take care of my Mom, has not allowed my to learn and make videos about python. I look forward to doing it, becasue it can bring such power. So for the time being, you will have to check out the other Excel MVP videos about this amazing new ability of Excel. Thanks for the well wishes, Roy!!!!
@@excelisfun python functionality is certainly redrawing the map especially for charting. At the moment there are a few things I would change with the implementation the main one being to allow access to a local python installation to avoid sending everything to the cloud. There are some useful tutorials out there from other MVPs for low level functionality, but I much prefer your enthusiastic style and real world examples. I look forward to seeing your response to the latest innovations in Excel when your family responsibilities allow you time. I am sure many of us older followers of your channel fully understand the dificulties of balancing family responsibilities with a career and outside interests so will remain faithful to your channel.
Hi Mike Thank you for another usefull video. Could you please suggest me a function or other functions in excel 2021 with which I can replace chooserows function from Microsoft 365? Please.All the best
Sir how to live stock price reflect in excel like stock market and working on it for algo trading please make this video because I know that u do that everything in excel😊
I am taking care of my Mom for a few months and I can't make videos. The videos posted over next four weeks are pre-posted. But you can try to post your question to the mrexcel.com/board web site, which is the best question site I konw : )
Mike consider we have a cell on which we type age like 6-9 (Six year nine month). we have a table which has a age interval columns like 6-0 to 6-5 6-6 to 6-11 7-0 to 7-5 7-6 to 7-11. how actually we get column number to match. mean lookup and get column number. Please share some thoughts. thank you.
I would build a lookup table with all possibilities. For example, if 6-0 to 6-5 returns a lookup value of 22, then build the lookup table as: 6-0, 22 6-1, 22 6-2, 22 6-3, 22 6-4, 22 6-5, 22 and so on. Use Exact Match You can build your lookup table across columns or rows. There are formulas to do what you want, but they are crazy. Sometimes, the best solution is to change the data or lookup table setup.
"lv" lookup_value (the array where you have format "6-9" values) "la" lookup_array (the vector where you have format "6-0 to 6-5" values) "ra" return_array (corresponding matching values for respective intervals of "la" ) then you can use a single cell simple formula, no other transformation needed =XLOOKUP(IF(LEN(TEXTAFTER(lv,"-"))=1,SUBSTITUTE(lv,"-","-0"),lv),SUBSTITUTE(TEXTBEFORE(la," "),"-","-0"),ra,,-1) if you need only clm/row nr. XMACH does the trick: =XMATCH(IF(LEN(TEXTAFTER(lv,"-"))=1,SUBSTITUTE(lv,"-","-0"),lv),SUBSTITUTE(TEXTBEFORE(la," "),"-","-0"),-1)
@@Excelambda how this function return a matching value . consider lookup value is 6-3 and we have column lable as 6-0 to 6-5 how formula will return true for match?
@@DataDashPro Works fine because aproximate match is not only for numbers , works with any values that can be compared, text strings included. Check it, works perfectly fine.
Next week, I will show the Power Query Method!
Challenge accepted will try to solve it in power query by next week... But this was great
I love your videos! Thank you for returning to the channel, I hope everything goes well with your beloved mother. A hug and a lot of strength.
I am happy to boomerang back. I am still helping Mom so I will just post one video each Wednesday for a while : )
That's the neatest Excel solution I have seen in a while. Great example of spilled arrays.
Glad it is neatest for you : ) : )
Thank you! I just love combining/compounding Excel functions. There is so much productivity here. Thank you for showing us these great things.
Good health to your mother and the whole family! Thanks Mike!
Thank you, Luciano : ) : ) You are welcome!!!
Wow Mike, so smart...i had not understood why using look up function but now it is clear, amazing
The original lookup function from 1979 still has great uses : ) LOOKUPisfun!
Excellent teacher Mike, for giving us several solutions
Glad you liked the video!!
That is effective and efficient, right there!
Thanks for this Golden Nugget, Mike. 🙂
Mike, since the challenge mentioned data cleaning, here's a simple PQ-M code that does the same:
let
Source = Excel.CurrentWorkbook(){[Name="fData"]}[Content],
dCity = Excel.CurrentWorkbook(){[Name="dCity"]}[Content][City],
GetCity = Table.AddColumn(Source, "City", each let desc = [#"Description (Only One City Name)"] in List.Select(dCity, each Text.Contains(desc,_)){0}, type text)
in
GetCity
@@GeertDelmulle , Cool! That looks like similar logic to my approximate match lookup formula. My video next Wednesday uses List.Accumulate because it seems faster. I learned this from you!!!! : ) : ) I will show:
= Table.AddColumn(AddDataTypes, "City", each List.Accumulate(Cities,"", (i,a) => if Text.Contains([Description],a) then a else i))
or if there are multiple names:
= Table.AddColumn(GetCityName, "CityWithDups", each Text.Trim(List.Accumulate(Cities,"", (i,a) => if Text.Contains([Description],a) then i&" "&a else i)))
Barry Houdini at Mr Excel Message Board ; )
Kudos to you for you giving props to somebody from 13 years ago! (and for being such a good son)! ❤
This is a good tip with a nice combination of old and new! 👌
It is funny, but I tend to remember many of the key concept tricks that I learned from people. It was like a gift when I learned it from them, so I usually remember. Or at least I try to remember lol
Thanks Mike! 3 amazing solutions. I like the INDEX - MATCH Variant. Big fan of these 2 functions.
INDEX and MATCH can do almost any type of lookup possible : ) : )
The pure Lookup solution makes me so nostalgic !
Riiiight, back to 1979, when Briklin and Frankston created it to do their taxes : ) : ) : )
Great video!!
BYROW iterates by nr. of rows, we can use REDUCE, a formula that iterates only 6 times no matter how many rows the vector has=> more efficient
=REDUCE("",G7:G12,LAMBDA(v,i,v&IF(ISNUMBER(SEARCH(i,B7:B62)),i,"")))
Also will be noticeable if there are more than one city/cell
Yes! That's a good one. I always forget about reduce, it's got some great uses.
👌
If More than one city. It join the City without space or delimiter.
@@KishoreKumar-of7sb The hypothesis and the solution of the video are obvious for scenarios of single cities per sentence, not for multiples.
My formula was designed to increase only the efficiency and not the functionality. And even if no extra functionality was added, still offers the advantage that is "noticeable" when there are mult. matches Therefore I have used the word "noticeable" and not " can deal with multiple matches".
Solving the delimiter "problem" is indecently simple and was not the purpose of the formula. Now that you mention it you force me to also increase the functionality. 😊
=REDUCE("",G7:G12,LAMBDA(v,i,v&IF(ISNUMBER(SEARCH(i,B7:B62)),IF(v="","",",")&i,"")))
edit after Mike's last post(YT did not show it as regular reply):
"Glad you liked it!!✌ The reason I avoided space and TRIM is because of composed cities like "San Francisco" (or Las Vegas, Los Angeles) that has spaces already. Looks better with other delim plus on a later task you can count the cities based on other delimiter that is not space 😉"
The improvements to the function are commendable.
@@Excelambda
Thanks Mike, this has much extent than what I was using search,isnumber,Filter
You are welcome! Sometimes the old school is the best : )
always More than Great Mr. Mike
Glad it is great for you!!!
Thanks Mike for this EXCELlent video.
Thank you Mike for the neat lookup formula. The big number concepts 2^15 are so interesting. I remember seeing them for the first time in Ctrl+Shift+Enter. (9.99999E+307 and Match OMega character. I thought that was soo creative.
I first learned the bib number concept from Aladin at the Mr Excel Message Baord : )
This is a great solution. It seems like Excel should make a built-in function to make this even easier.
Glad you like this Barry Houdini solution, Josh Excel : )
Amazing solution Mike. Thank you for this awesome video 📹 :)
You are welcome, Nader!!! : )
So awesome, thanks Mike!!
You are welcome, Chris!!!!
Thanks Mike. That was an amazing solution.
I bet you have seen this before, Formula Guy John : ) : ) : )
@@excelisfun Yes. You had other similar videos.
@@johnborg5419 I knew that you knew ; )
Hi Mike,
I can not thank you enough to be honest for your videos.
You are welcome!!!! : )
Awesome as always!! 🎉
Glad you like it!!!
I was able to figure this out on my own thanks to your teachings. How would you do that with multiple results that you concatenated into a single cell?
Amazing combination of old and new! Thank you Professor. 👍👍👍
You are welcome for the old and new : ) : )
Great video, thanks Mike. The old-school Lookup function is great but even better used with the some of the newer functions. The power query method should be an interesting video too. Doing it with the new py functionality would also be an interesting challenge. I would be interested to know you think of the ability to use python on the grid in Excel and whether you plan to produce any videos dealilng with the new functionality when your family circumstances allow.
I hope your mom is recovering well from her ill-health.
I do not know python. Bad timing with writing my books (which are on hold now cuz of my Mom) and having to temp. move to California to take care of my Mom, has not allowed my to learn and make videos about python. I look forward to doing it, becasue it can bring such power. So for the time being, you will have to check out the other Excel MVP videos about this amazing new ability of Excel.
Thanks for the well wishes, Roy!!!!
@@excelisfun python functionality is certainly redrawing the map especially for charting. At the moment there are a few things I would change with the implementation the main one being to allow access to a local python installation to avoid sending everything to the cloud. There are some useful tutorials out there from other MVPs for low level functionality, but I much prefer your enthusiastic style and real world examples.
I look forward to seeing your response to the latest innovations in Excel when your family responsibilities allow you time.
I am sure many of us older followers of your channel fully understand the dificulties of balancing family responsibilities with a career and outside interests so will remain faithful to your channel.
Thank you
You are welcome!!!
gain a lot of knowledge
Hi Mike
Thank you for another usefull video.
Could you please suggest me a function or other functions in excel 2021 with which I can replace chooserows function from Microsoft 365?
Please.All the best
INEX can lookup a single column or row. =INDEX(H22:L32,0,3) looksup column 3, =INDEX(H22:L32,3,0) looks up row 3.
@@excelisfun Perfect.Thank you for the answer
hi mike, thank you for share your knowledge, this works for Mac or i should work with windows pc or laptop?
I have no idea. Given the demands of the working world, I stopped using my beloved Mac in 1997. I have not used one since and do now know : (
Sir how to live stock price reflect in excel like stock market and working on it for algo trading please make this video because I know that u do that everything in excel😊
I am taking care of my Mom for a few months and I can't make videos. The videos posted over next four weeks are pre-posted. But you can try to post your question to the mrexcel.com/board web site, which is the best question site I konw : )
Mike consider we have a cell on which we type age like 6-9 (Six year nine month). we have a table which has a age interval columns like 6-0 to 6-5 6-6 to 6-11 7-0 to 7-5 7-6 to 7-11. how actually we get column number to match. mean lookup and get column number. Please share some thoughts. thank you.
I would build a lookup table with all possibilities. For example, if 6-0 to 6-5 returns a lookup value of 22, then build the lookup table as:
6-0, 22
6-1, 22
6-2, 22
6-3, 22
6-4, 22
6-5, 22
and so on.
Use Exact Match
You can build your lookup table across columns or rows.
There are formulas to do what you want, but they are crazy. Sometimes, the best solution is to change the data or lookup table setup.
"lv" lookup_value (the array where you have format "6-9" values)
"la" lookup_array (the vector where you have format "6-0 to 6-5" values)
"ra" return_array (corresponding matching values for respective intervals of "la" )
then you can use a single cell simple formula, no other transformation needed
=XLOOKUP(IF(LEN(TEXTAFTER(lv,"-"))=1,SUBSTITUTE(lv,"-","-0"),lv),SUBSTITUTE(TEXTBEFORE(la," "),"-","-0"),ra,,-1)
if you need only clm/row nr. XMACH does the trick:
=XMATCH(IF(LEN(TEXTAFTER(lv,"-"))=1,SUBSTITUTE(lv,"-","-0"),lv),SUBSTITUTE(TEXTBEFORE(la," "),"-","-0"),-1)
@@Excelambda how this function return a matching value . consider lookup value is 6-3 and we have column lable as 6-0 to 6-5 how formula will return true for match?
@@DataDashPro Works fine because aproximate match is not only for numbers , works with any values that can be compared, text strings included.
Check it, works perfectly fine.
@@Excelambda "single cell simple formula" lol Awesome, Excel Lambda!!!!!
what if we didn't have a list of cities, then how can we extract city name from a column of raw data
no working, Its says #N/A