@@excelisfun You are the Engine behind whatever I (and 1000s Others) know about MS Excel. (I think I have mentioned this before in My earlier comment in another video) You are 1 of the Reasons that I have fallen in Love with MS Excel. Best Wishes!
+Sal A Yes! Here is a video: Excel Magic Trick 1159: Extract Unique List For Data Validation List with Power Query th-cam.com/video/3ICk356kEZo/w-d-xo.html Excel Magic Trick 1252: Power Query To Create Unique List of Invoice Numbers with Balances th-cam.com/video/ZAg7AzBkg30/w-d-xo.html
Great video, very clear and great pace.Can you indicate how does PowerQuery work when instead of having a single column for Calls, there are 12 (one per month). Instead of days I have names.Thanks
Mike I really enjoyed this video. I've seen you do many INDEX+MATCH+AGGREGATE formulas over the years but this comparison video somehow made it all make sense to me. Thanks a lot. Oh yes, your CSE book has made a big difference also. One question > What is PowerQuery doing in the background to provide the top20 result? Is it using the INDEX+MATCH+AGGREGATE formulas or is there some other "black magic" going on?
Great trick!! I have an issue with a spreadsheet, not knowing how to extract certain data. Top 12 Archery scores; AT LEAST 4 scores have to include the opposite sex. It can be 8m-4f, 7m-5f, 6m-6f, etc, out of 16 males and 8 females. Need to list Rank, Name, and Score. Then a SUM of Top 12 scores. I have used AGGREGATE, VLOOKUP, INDEX, MATCH, ROWS, DMAX, COUNTIF, LARGE. I am so close, yet I feel stuck. Any Ideas?
I am trying to do something similar to what you did in this video but I fail... I have one column with a set date and then another column with a textline. I want to search for the word (lets say "apple") within the column containing text. Each time time I get a hit I would like it to print the date to a third column, like in this video. Any suggestion as to what functions and code I could use?
Very nice formula! btw, just in case formula or Power Query is not the option... we still have Advanced Filter to do so, although we need to sort the result after filtering. :)
+ExcelIsFun Thanks Mike, unfortunately it is not a solution for a 20k lines. I have two value columns the first should be less than $50k and the other greater than 0...
hi mike.could u pls show some more tricks about extracting data from a list with dublicates.this s the situation especially when comparing for example bank statements and account records.thanks a lot.regards..
Hey HELP! I am trying to create an excel workbook used for photo captions. something that would speed up the copy to clipboard so the cell(s) can be pasted into a word doc or other programs. The columns would have certain notes.
Say i have columns foe sales area, state,county reps, sales$, #items....how do i get top ten but using drop down to get top ten sales in the state or county
@3'28" "We can't use VLOOKUP" =VLOOKUP(F6,CHOOSE({1,2},fCalls[Calls],fCalls[Day]),2,0) I saw this somewhere online about a month or two ago (though I cannot remember who came up with it). I have started using it more than INDEX & MATCH.
+Clint Gubich , That is a clever solution! I have a number of videos on that particular formula, however the general consensus is that it is over complicated with the array operation and that INDEX and MATCH is more straight forward. It was not correct at 03:28 when I side that "can't use VLOOKUP". I should have said the easiest solution is... That said, either way is fine, mostly based on personal preference.
Token of Gratitude!
Thank you for being an engine behind my channel, Ankur!!!! : ) : ) : ) : ) : ) : ) : )
@@excelisfun You are the Engine behind whatever I (and 1000s Others) know about MS Excel.
(I think I have mentioned this before in My earlier comment in another video) You are 1 of the Reasons that I have fallen in Love with MS Excel.
Best Wishes!
@@ankursharma6157 I am so very glad that the fun I have with Excel has helped you to fall in love with Excel!!!!!!!!
Power Query is super easy. Thank you Mike !!
You are welcome, HIMANSHU!!!
Another "top" notch video...thanks Mike!
Nice Array formula...PQ makes it so easy. Can PQ extract unique data only?
+Sal A Yes! Here is a video:
Excel Magic Trick 1159: Extract Unique List For Data Validation List with Power Query
th-cam.com/video/3ICk356kEZo/w-d-xo.html
Excel Magic Trick 1252: Power Query To Create Unique List of Invoice Numbers with Balances
th-cam.com/video/ZAg7AzBkg30/w-d-xo.html
Great video, very clear and great pace.Can you indicate how does PowerQuery work when instead of having a single column for Calls, there are 12 (one per month). Instead of days I have names.Thanks
I know why I had issues with the array formula! I am trying to return a name not a number :(
Mike I really enjoyed this video. I've seen you do many INDEX+MATCH+AGGREGATE formulas over the years but this comparison video somehow made it all make sense to me. Thanks a lot. Oh yes, your CSE book has made a big difference also.
One question > What is PowerQuery doing in the background to provide the top20 result? Is it using the INDEX+MATCH+AGGREGATE formulas or is there some other "black magic" going on?
+Philip Bennett Other Black Magic. It is its own program.
Great trick!! I have an issue with a spreadsheet, not knowing how to extract certain data. Top 12 Archery scores; AT LEAST 4 scores have to include the opposite sex. It can be 8m-4f, 7m-5f, 6m-6f, etc, out of 16 males and 8 females. Need to list Rank, Name, and Score. Then a SUM of Top 12 scores. I have used AGGREGATE, VLOOKUP, INDEX, MATCH, ROWS, DMAX, COUNTIF, LARGE. I am so close, yet I feel stuck. Any Ideas?
I am trying to do something similar to what you did in this video but I fail...
I have one column with a set date and then another column with a textline. I want to search for the word (lets say "apple") within the column containing text. Each time time I get a hit I would like it to print the date to a third column, like in this video.
Any suggestion as to what functions and code I could use?
Very, VERY cool the duplicate formula. BRAVO
+Victor Platon Glad you like it!
Power Query way is cool.
+pmsocho Glad you like it!
Thanks Mike for all your Magic Trick's .. but this Power Query example was great.
+George de Beer You are welcome!
Very nice formula!
btw, just in case formula or Power Query is not the option... we still have Advanced Filter to do so, although we need to sort the result after filtering. :)
Thank you so much, was wondering if there is an easy way to make 2 or more value filters in a pivot table. thanks
+issam khirallah , Maybe a Slicer.
+ExcelIsFun Thanks Mike, unfortunately it is not a solution for a 20k lines. I have two value columns the first should be less than $50k and the other greater than 0...
+issam khirallah , I am not sure. Try THE best Excel question site: mrexcel.com/forum
+ExcelIsFun Excellent. Thank you so much. you are the best!!
Great Video, Thank you very much
You are welcome!
hi mike.could u pls show some more tricks about extracting data from a list with dublicates.this s the situation especially when comparing for example bank statements and account records.thanks a lot.regards..
+bohemax xtum i HAVE MANY VIDEOS ON EXTRACTING DATA. cHECK THIS OUT:
th-cam.com/play/PL63A7644FE57C97F4.html
Hey HELP! I am trying to create an excel workbook used for photo captions. something that would speed up the copy to clipboard so the cell(s) can be pasted into a word doc or other programs. The columns would have certain notes.
Many thanks!
My power query doesn't refresh automatically when I add more data in the table.. :) I can't fix it! :(
Hey Man, you are Boss. Amazing....
Say i have columns foe sales area, state,county reps, sales$, #items....how do i get top ten but using drop down to get top ten sales in the state or county
Feeling songs
@3'28" "We can't use VLOOKUP"
=VLOOKUP(F6,CHOOSE({1,2},fCalls[Calls],fCalls[Day]),2,0)
I saw this somewhere online about a month or two ago (though I cannot remember who came up with it). I have started using it more than INDEX & MATCH.
+Clint Gubich , That is a clever solution! I have a number of videos on that particular formula, however the general consensus is that it is over complicated with the array operation and that INDEX and MATCH is more straight forward. It was not correct at 03:28 when I side that "can't use VLOOKUP". I should have said the easiest solution is... That said, either way is fine, mostly based on personal preference.
Thanks Mike for this video ,keep up the great work :)
Nice
WOW... sooo cooolll :-)))
Thanks
+Bill Szysz , Glad you like it! Thanks for your Power Query Mastery! :)
Fucking AMAZING !!! It was just what I was looking for! Thanks , I need to use tables more.
PowerQuery rules
+Laza Lazarevic I agree!!!
Happy 29th February!
+Ashay Dwivedi It is great to get an extra day this month!!!
ExcelIsFun Yeah! Extra Day = Extra Excel
+Ashay Dwivedi Extra Day = Extra Excel = Extra Fun!!!
ExcelIsFun Yeah!