Hello, I have a question about power query. I created date table, added events. But events with same date create duplicated row. I have same date in two rows. I want to have one date value and the names of events in two separated columns.
Using the same for power query Mynda, in these busy days it's helped make it more accessible to update values easily in the query editor and 'how to tricks'
ChatGPT 3.5 o4 4.0 or Copilot or Bard? BTW, my stumps are: - comparing different tables with similar data to find differences - not able to import data from tables from various web sources, just won't import (mainly ones with daily currencies etc - creating centralized data table out of very complex business, so that various reports are created, and sharing it with various parties, connecting with SharePoint data, Outlook... All in on 365 platform, company wise.
My top formula issues tend to be dynamic hyperlinks (don't use them enough and keep forgetting), long IF...SUBSTITUTE chains, working with dates in array formulas and LAMBDAs.
I know. But takes too long. Tbh I dont think it will happen VBA is last updated in 2007. Thats 15 years. Yet office script is far behind VBA. And ppl still use VBA. I believe office desktop application will still be user favorite.
my tough one, have not solved it yet, is to have a sortfilter command that is looking at a table and it outputs a list. Then I want to put another sortfilter under that output to create another output area. If I leave one or 2 blank rows it works, then if I add more data to the original table to increase the length of the output, I get spill as there is not enough room for the top one to output before running into the second output. Was thinking it would be cool to have the distance between the 2 outputs expand based on the data size.
I use the excel sheets to make test or certificate documents and use "Rows to repeat at top". But the only way to get page numbers on each page is in headers/footer, i can't find a way to get it in to a cell in the part of "Rows to repeat at top" area that i picked.
Trying to determine employee headcount by month using start dates and termination dates is a hassle. For me, it involves nexted IF statements that include AND and OR combinations to account for all the possible scenarios (e.g., an employee starts and leaves in the same month, starts partway through the month, etc.).
I’m thinking you might be able to write a custom lambda function for that, similar to how I wrote one to calculate tax: www.myonlinetraininghub.com/excel-lambda-function
I have a stocks portfolio that updates every time you open the document. Is there a way to make it stop asking to save the auto-changes every time you close it, cause it gets annoying?
I face challenge when there are too much columns are there and I have to scroll to and fro a lot. 2ndly when I have to make Sales Budget for the year month wise dynamically on multiple criteria. 3rdly, I want my formula to spill both in rows and columns spontaneously. 4rth I cant see Pivotby and Groupby function I have tried every thing. I am using Office 365. and my excel is really slow it start calculating each time I copy formula down and across or try to save a file with ctrl+S
Thanks for sharing! In 365 you can CTRL+SHIFT+ mouse scroll to scroll left and right. Or you could use groups to temporarily hide/unhide columns: ua-cam.com/video/9ptDanWErh4/v-deo.html Spilling formulas into rows and columns is not available in all cases. Some formulas are constrained by the array or arrays limitation. PIVOTBY & GROUPBY are currently only available to 365 users on the beta channel. Slow Excel will be file specific. Sounds like your formula may be inefficient. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
What stumps me: I just can't memorize how to write an absolute reference for a table column, i.e. TableName[@[Column Name]:[Column Name]].. Is @ supposed to sit inside or outside of [ ] ?? Is [ ] supposed to wrap around each column name or around both?? I do eventually figure it out, but not before several error messages... EVERY. SINGLE. TIME...😳
I'm not sure what you mean. Surely if there are no figures or text in a cell then it isn't showing anything. You're welcome to post your question and sample Excel file that demonstrates what you mean on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
❓What's the one Excel task or formula that always seems to stump you?
LEARN MORE in my Excel courses: bit.ly/excelandchatgpt
Hello, I have a question about power query. I created date table, added events. But events with same date create duplicated row. I have same date in two rows. I want to have one date value and the names of events in two separated columns.
Index match always stumps me. It seems simple, but I always find myself watching videos or reading about it
Great video Mynda! I've started to use ChatGPT several times when I get stuck with a formula, and also in DAX.
Index & match always stump me!!!!😔!!! Fantastic tutorial Mynda!
Thanks 🙏 here’s a tutorial on INDEX & MATCH: www.myonlinetraininghub.com/excel-index-and-match-functions
Using the same for power query Mynda, in these busy days it's helped make it more accessible to update values easily in the query editor and 'how to tricks'
Awesome to hear 😁
Amazing Mynda, thanks for all your content!
Thank you for the support!
Many thanks, Mynda.
Thanks for watching 😊🙏
Did you speed up the ChatGPT responses Mynda? Mine are always so slow. Great post, as ever. 👍
Yes, I did but not by much.
ChatGPT 3.5 o4 4.0 or Copilot or Bard?
BTW, my stumps are:
- comparing different tables with similar data to find differences
- not able to import data from tables from various web sources, just won't import (mainly ones with daily currencies etc
- creating centralized data table out of very complex business, so that various reports are created, and sharing it with various parties, connecting with SharePoint data, Outlook...
All in on 365 platform, company wise.
Lots to tackle there. You can use Power Query to compare tables: www.myonlinetraininghub.com/excel-compare-two-lists
My top formula issues tend to be dynamic hyperlinks (don't use them enough and keep forgetting), long IF...SUBSTITUTE chains, working with dates in array formulas and LAMBDAs.
Thanks for sharing!
Excelente vídeo Mynda! Parabéns, vou incluir no ChatGPT no meu dia a dia! ☺
Wonderful to hear 😊🙏
Can you please make videos about REDUCE function and the use of MAP and FILTER
On the list 😉 FILTER is already done here: ua-cam.com/video/ZCQAweoAdOw/v-deo.html
Not updating VBA is a mistake of MS.
Excel online wont match excel desktop.
Why use office script?
Microsoft are working towards bringing Excel online to parity with the desktop, but it will be a while.
I know. But takes too long.
Tbh I dont think it will happen
VBA is last updated in 2007. Thats 15 years. Yet office script is far behind VBA. And ppl still use VBA.
I believe office desktop application will still be user favorite.
my tough one, have not solved it yet, is to have a sortfilter command that is looking at a table and it outputs a list. Then I want to put another sortfilter under that output to create another output area. If I leave one or 2 blank rows it works, then if I add more data to the original table to increase the length of the output, I get spill as there is not enough room for the top one to output before running into the second output. Was thinking it would be cool to have the distance between the 2 outputs expand based on the data size.
You could probably get around this by stacking the two ranges in one formula using VSTACK, rather than two separate formulas.
With let and lambda you can do apportionment with a single formula
Yes, those functions are game changers for 365 users. 👍
@@MyOnlineTrainingHub can u do a demo on the different apportionment methods as single cell formulas please?
Super!!!
Thank you! Cheers!
I use the excel sheets to make test or certificate documents and use "Rows to repeat at top". But the only way to get page numbers on each page is in headers/footer, i can't find a way to get it in to a cell in the part of "Rows to repeat at top" area that i picked.
Yes, page numbers are only added in the header or footer. You could manually add them in cells in the worksheet.
Trying to determine employee headcount by month using start dates and termination dates is a hassle. For me, it involves nexted IF statements that include AND and OR combinations to account for all the possible scenarios (e.g., an employee starts and leaves in the same month, starts partway through the month, etc.).
I’m thinking you might be able to write a custom lambda function for that, similar to how I wrote one to calculate tax: www.myonlinetraininghub.com/excel-lambda-function
Thank you for the idea. I’ve done that for other tasks, so I’ll give that a try. I love your videos!!!!😊
Hmm, I'm still on the fence with ChatGP, but it's definitely something to think about 😎
Yeah, it can be hit and miss still, but it’s improving.
If and or formulas
Check out this video: www.myonlinetraininghub.com/excel-if-and-or-functions-explained
Hi yMnda I can't download the workfile, would you please resend it?
There’s no file for this video, but you can download the cheat sheet.
I have a stocks portfolio that updates every time you open the document. Is there a way to make it stop asking to save the auto-changes every time you close it, cause it gets annoying?
Excel will always ask you to save changes if it detects changes in the file. I'm not aware of any way to prevent this.
@@MyOnlineTrainingHub thanks Mynda!
save it on your onedrive and enable auto save in excel for the file.
I face challenge when there are too much columns are there and I have to scroll to and fro a lot.
2ndly when I have to make Sales Budget for the year month wise dynamically on multiple criteria.
3rdly, I want my formula to spill both in rows and columns spontaneously.
4rth I cant see Pivotby and Groupby function I have tried every thing. I am using Office 365.
and my excel is really slow it start calculating each time I copy formula down and across or try to save a file with ctrl+S
Thanks for sharing! In 365 you can CTRL+SHIFT+ mouse scroll to scroll left and right. Or you could use groups to temporarily hide/unhide columns: ua-cam.com/video/9ptDanWErh4/v-deo.html
Spilling formulas into rows and columns is not available in all cases. Some formulas are constrained by the array or arrays limitation.
PIVOTBY & GROUPBY are currently only available to 365 users on the beta channel.
Slow Excel will be file specific. Sounds like your formula may be inefficient. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Making a "Sumproduct IFs" function is always challenging
Indeed it is 👍
Hi, hope you are fine, the request is can I share some Excel files having data for creating therein some conditional formulas & send them back to me?
You’re welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Find numbers in sum [Excel Solver]
What stumps me: I just can't memorize how to write an absolute reference for a table column, i.e. TableName[@[Column Name]:[Column Name]]..
Is @ supposed to sit inside or outside of [ ] ?? Is [ ] supposed to wrap around each column name or around both?? I do eventually figure it out, but not before several error messages... EVERY. SINGLE. TIME...😳
😁 I can never remember either!
I feel a little comforted 😅
This world has gone crazy with productivity
I don’t see it stopping anytime soon 😁
🙏 thank
Pleasure 😊
Index match
My Challenge: Array formulas always are beyond me.
Thanks for sharing. I’ve found with the new dynamic array functions it’s easier to see how they work: www.myonlinetraininghub.com/excel-dynamic-arrays
index and match 😭😭i never get it
Check out this video on INDEX & MATCH: 4 Mins. to MASTER Excel INDEX & MATCH formulas!
ua-cam.com/video/-4yCXpv-drg/v-deo.html
I am easily stumped by making a cell show nothing when there are no figures or text
I'm not sure what you mean. Surely if there are no figures or text in a cell then it isn't showing anything. You're welcome to post your question and sample Excel file that demonstrates what you mean on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub it is a cell that is part of a formula
Oh, ok. You can use two double quotes to return a blank cell. e.g. this will return a blank if the formula A1/B1 returns an error =IFERROR(A1/B1, "")
Not sure about this... For me, your videos are easier to follow than ChatGPT.
Glad you like them!
"hot" topic :)
Thanks for watching 🙏😊
To use chatgpt they ask you your phone number... no way that thing gets my phone number !
I don’t recall it requiring my phone number 🤔
@@MyOnlineTrainingHub Yes it does
V lookups
Lamda function
Sure, get ChatGPT to help you write lambdas.