User Profile
Mayukh_Bhattacharya
Iron Contributor
Joined Sep 18, 2021
User Widgets
Recent Discussions
Re: Looking for automated way to build an event attendance list
Hi Randy340 , Follow the process to apply PQ: GoTo Data Tab , Under Get & Transform Data, Click on Get Data , On doing above, will give you some options, click on the 5th option(From Other Sources), which opens few more options --> Click on Blank Query, On doing the step above opens the Power Query Window. From there under Home Ribbon Tab, Click on Advanced Editor, Now, whatever you see in the editor completely delete and paste the m-code given by Maciej_Kopczynski and then click on Done, while doing all of these ensure to either have same table name as given in the code or just change the table name as per your data in the first line of the code, also the data should be in Structured References aka Tables and not range. Below is a .gif showing the whole process. yRe: Looking for automated way to build an event attendance list
Randy340 Here is one more way of utilizing the modern excel functions like GROUPBY() although one needs to use MS365 with the Beta Channel (Office Insiders) enabled. Personally, I would have gone using POWER QUERY which makes it way easier, simpler and more importantly time saving, effortless as well can handle quite a lot of data as far I know. Perhaps here is my attempt using the said function. =LET( _GroupByLastName_Name, GROUPBY(A2:B9,HSTACK(C2:D9),HSTACK(SUM,ARRAYTOTEXT),0,0), _DroppingTempHeader, DROP(_GroupByLastName_Name,1), _ReplacingCommasWithLineFeeds, SUBSTITUTE(_DroppingTempHeader,", ",CHAR(10)), VSTACK(A1:D1,_ReplacingCommasWithLineFeeds)) Note: Variable _ReplacingCommasWithLineFeeds is unnecessary here, since the required output for Seats column uses line feeds to join them hence added to substitute the commas with line feeds, but if you are satisfied with the use commas, then can remove that part.Re: Can someone help me create the correct formula
BeckyBo334 it will be helpful, if you read the MSFT Documentations once before applying the function. However, you could use one of the followings, also instead of hardcoding the colors count needed place them in some range and use it as a reference like shown below. Using COUNTIF() , SUM() & HSTACK() =SUM(COUNTIF(D2:D12,HSTACK("blue","green","purple",""))) Or, Color refers to range F6:F8 (you may need to change the cell range as per your suit) =SUM(COUNTIF(D2:D12,Color))Re: Need capital letters in DDMMMYY date formats.
AntonioPonce AFAIK you would need to use a TEXT() function to enforce UPPER() which has already been suggested however you could try in addition to your present custom formatting change the font for that cell/range to a font that allows only UPPER case letters. Watch below: Fonts that does the trick ensuring the dates are not formatted as text and retains the dates as numbers are Stencil, Perpetua Titling MT, Engravers MT and Felix Titling!!Re: which function?
mrn2024 The easiest way to do this is using Pivot Tables, however using formulas one could use one of the followings: Using HSTACK() , UNIQUE() & SUMIFS() =UNIQUE(HSTACK(A1:A4,SUMIFS(B1:B4,A1:A4,A1:A4))) Or, Using with GROUPBY() which is applicable to MS365 Insiders Version. =GROUPBY(A1:A4,B1:B4,SUM,,0)Re: EXCEL SHEETTABS SHOWING IN A DIFFERENT WAY.
I certainly do that, but I think Sir, I might need to reinstall both windows and MS365, the shell commands might have affected something, this was one which can be seen with naked eyes may be there are something which may or may not be affected. I have to research moreRe: EXCEL SHEETTABS SHOWING IN A DIFFERENT WAY.
No all looks good for now, only this one I was able to detect. And which is still same, even after reinstalling the Office. I raised with Microsoft Support Team, they checked everything was fine, only this sheet tabs is showing different. Can you confirm how is your sheets tabs showing, like one in the first pic or the second one.EXCEL SHEETTABS SHOWING IN A DIFFERENT WAY.
Hello All, I might be facing a minor issue, but it concerns me a lot. I had contacted the Microsoft Support Team, the person who took control of my computer using remote access has guided me to this forum, although i was aware of this forum, but contacted the support team first. So my issue is the sheet tabs are showing in a different way, i have checked any documentation has been released for this change or not, but there was nothing as such. On 31st Jan 23, I ran a vba code which had some shell commands, after running the sheet tabs got changed. So this was before running the vba code. After running the vba code it shows like this Please help, what should i do to get the earlier ones.
