User Profile
Mayukh_Bhattacharya
Iron Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Looking for automated way to build an event attendance list
HiRandy340, Follow the process to apply PQ: GoTo DataTab , 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 QueryWindow. 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 byMaciej_Kopczynskiand 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 Tablesand not range. Below is a .gif showing the whole process.869Views0likes1CommentRe: Looking for automated way to build an event attendance list
Randy340Here 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.922Views1like0CommentsRe: Can someone help me create the correct formula
BeckyBo334it 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,Colorrefers to range F6:F8 (you may need to change the cell range as per your suit) =SUM(COUNTIF(D2:D12,Color))773Views0likes0CommentsRe: Need capital letters in DDMMMYY date formats.
AntonioPonceAFAIK 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 fontfor that cell/range to a font that allows only UPPERcase letters. Watch below: Fonts that does the trick ensuring the dates are not formatted as text and retains the dates as numbers areStencil,Perpetua Titling MT,Engravers MTandFelix Titling!!4.3KViews1like0CommentsRe: which function?
mrn2024The 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)476Views0likes0CommentsRe: 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 more2.3KViews0likes0Comments- 2.4KViews0likes2Comments
Re: 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.2.7KViews0likes4CommentsEXCEL 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.2.9KViews0likes8Comments- 1.5KViews0likes4Comments
Re: Why does REDUCE Function gives VALUE Error
Sir, even though its working now, but I am not able to figure what was the issue, anyways when its working, better not to take any more stress on it. Thank you very much for your time patience and effort. Sir how do i close the post.1.5KViews0likes6CommentsRe: Why does REDUCE Function gives VALUE Error
Ok Sir, I found out, that I can use REDUCE() Function anywhere, i.e. I have used that specific formula in other workbooks and as well as in new workbook as well, but it is not working for this specific texts strings. For those US Addresses3.3KViews0likes0Comments