User Profile
DrExcel_Excel_MVP
Copper Contributor
Joined Aug 24, 2023
User Widgets
Recent Discussions
GROUPBY Function And Merged Cells
How to Deal With Merged Cells in Excel if you want to summarize your data with GROUPBY here is my first try : =GROUPBY( WRAPCOLS( SCAN( , TOCOL(B7:C24, , 1), LAMBDA(x, y, IF(y = "", x, y)) ), ROWS(B7:C24) ), D7:F24, SUM ) a Powerful use of my custom Excel function FILLDATA is demonstrated in situations like this, where the first two columns (which would be used as the Row Field in the GROUPBY function) have merged cells. And that is the solution with FILLDATA Function : =GROUPBY(FILLDATA(B7:C24, "d", , ), D7:F24, SUM) FILLDATA is a custom function built using LAMBDA that automates the process of filling empty cells in your spreadsheets in all Directions (Down – Up – Right – Left ) If you want to know more about it , check out this Post : https://lnkd.in/d82GChJW if you find it useful , please vote for it: https://lnkd.in/dvYe3JnQUse GROUPBY in Excel to add Blank Rows/Cols After Each Group
Use GROUPBY in Excel to add Blank Rows/Cols After Each Group to add blank Rows: =LET( a, GROUPBY(B4:D13, E4:F13, SUM, , 2), DROP(IF(BYROW(--(a = ""), SUM), "", a), -2) ) to add blank Cols: =TRANSPOSE( LET( a, GROUPBY(B4:D13, E4:F13, SUM, , 2), DROP(IF(BYROW(--(a = ""), SUM), "", a), -2) ) )UNPIVOT (My Custom Excel Function)
"Thanks LAMBDA" Please welcome my new Custom Function https://www.linkedin.com/feed/hashtag/?keywords=unpivot&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 This FUNCTION empowers you to efficiently Flat your data, similar to Power Query's unpivot functionality, but with enhanced flexibility and control = UNPIVOT ( Range , Cols , Blank Options , Header ) It consists of 4 parts Range : Select the range of data you want to unpivot. Cols: Specify which columns to treat as "keys" (preserved after unpivoting) and which to unpivot (spread into rows). ** Default: Use the first leftmost column as the key and unpivot all other columns. (Same behavior for entering "1") or you can skip it. ** Positive Integers: Specify the number of leftmost columns to be keys and unpivot the remaining columns. (e.g., "2" uses the first two columns as keys and unpivot other Cols.) ** Negative Integers: Specify the number of rightmost columns to be keys and unpivot the remaining columns. (e.g., "-1" uses the last column as the key and unpivot other Cols.) Blank Options: Choose how to handle empty cells during unpivoting: 1 : Include only rows with empty cells. 2 : Include only rows with non-empty cells. 3 : Include all rows (empty and non-empty). Headers: Default (Blank) "" or skip : No header will be added to the unpivoted data. Custom Header: Specify a header within curly braces: {"Name","Loc",Qty."} Or you can use HSTACK .. And for Numeric Sequence you can use SEQUENCE Function to Generate sequential headers (1, 2, 3...). Example 1 : Basic unpivot, ( wait till Example 3 to know the meaning of " 3 " in Blank Options) Example 2 : Unpivot data and add headers Example 3 : let's show the Blank Options and how it works Example 4 : unpivot based on which cols WHAT DO YOU THINK ? IS IT USEFUL ? WOULD IT SAVE TIME? If you agree with this, I encourage you to vote for my suggestion https://feedbackportal.microsoft.com/feedback/idea/e6aea412-0fc0-ee11-92bd-6045bd7fe601 https://www.linkedin.com/feed/hashtag/?keywords=hazem_hassan&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=dr_excel&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=excel&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=excelskills&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=exceltricks&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=exceltips&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvp&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvpdevelopment&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvpbuzz&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvps&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=microsoftambassador&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368TEXT.SELECT -My Custom Excel Function
I would like to talk to you about one of my favorite Excel Functions that I created with the help of LAMBDA If you always need to separate/extract the components of a cell so that you can use each part separately, then I think this formula will be useful for you. http://text.select/ This formula allows the user to select a specific piece of text from a cell (in the same way as the http://text.select/ in PowerQuery). It consists of 4 main parts =http://text.select/ ( MixedText , Select , Space , Keep) ➡MIXEDTEXT : Select the Cell ➡SELECT : Type 1 for Numbers Type 2 f or English Letters Type 3 for Uppercase English Letters Type 4 for Lowercase English Letters Type 5 for Other Languages (Some languages are still not supported) ➡SPACE : Type 1 to Keep the Space Skip this part if you want to ignore the space ➡Keep : In this part , if you want to keep Non-alphanumeric type them like this {".";"@";"-"} between Quotations and separated by the semicolon. If you want to ignore this part , then type "" Example #1: in this example I am showing how to extract :Number, All Letters , Uppercase Letters , Lowercase Letters and any other language if found Example #2: How to Extract emails with all its components like [ @ , . , - , _ ] Example #3: How to Extract emails and any text in different (and as I mentioned Some languages are still not supported) carefully notice that ,It is possible to find a space between sentences, and it is also possible not to find one. So, TEXT.SELECT handles this easily.. WHAT DO YOU THINK ? IS IT USEFUL ? WOULD IT SAVE TIME? https://www.linkedin.com/feed/hashtag/?keywords=hazem_hassan&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=dr_excel&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=excel&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=excelskills&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=exceltricks&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=exceltips&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=mvp&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=mvpdevelopment&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=mvpbuzz&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=mvps&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272 https://www.linkedin.com/feed/hashtag/?keywords=microsoftambassador&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7157295034118070272Leading and Trailing Zero in Excel
How to Remove Leading and Trailing Zeros in Excel We have a set of product codes like this : 000P2I290002M900 we want to make it like this P2I290002M9 which means removing all leading and trailing zeros from the code All my attempts to solve this =MAP( B6:B14, LAMBDA(x, LET( a, SUBSTITUTE(x, 0, ""), b, MID(x, SEQUENCE(LEN(x)), 1), MID( LEFT(x, XMATCH(RIGHT(a), b, , -1)), MATCH(LEFT(a), b, 0),99)))) =MAP( B6:B14, LAMBDA(x, LET( a, TEXTSPLIT(x, , 0), TEXTJOIN(0, 0,DROP( DROP(a, MATCH(FALSE, a = "", 0) - 1), XMATCH(FALSE, a = "", , -1) - COUNTA(a)))))) =MAP( B6:B14, LAMBDA(x, LET( a, SUBSTITUTE(x, 0, ""), b, SEQUENCE(LEN(x)), s, HSTACK(LEFT(a), RIGHT(a)), t, MID(x, b, 1), f, TOCOL(FIND(s, t) + b - 1, 3), MID(x, MIN(f), MAX(f) - MIN(f) + 1)))) =MAP( B6:B14, LAMBDA(x, LET( a, SUBSTITUTE(x, 0, ""), b, FIND(LEFT(a), x), MID(x,b, MAX(TOCOL( FIND(RIGHT(a), MID(x, b, LEN(x)), SEQUENCE(LEN(x))),3)))))) =MAP( B6:B14, LAMBDA(x, LET( a, TEXTSPLIT(x, , 0), b, XMATCH(FALSE, a = "", 0, 1), TEXTJOIN(0,0,CHOOSEROWS(a, SEQUENCE(XMATCH(FALSE, a = "", 0, -1) - b + 1, , b)))))) My LinkedIn : https://www.linkedin.com/in/hazemhassandrexcel My YouTube Channel : http://My%20Youtube Channel #Hazem_Hassan #Dr_Excel #excel #excelskills #exceltricks #exceltipsInsert a Blank Row After Every Change of Product
Insert a Blank Row After Every Change of Product let's do it step by step: Step by Step #1 =SEQUENCE(ROWS(B4:E13)) #2 =B4:B13=B5:B13 #3 =DROP(H4#,-1) Which is : =DROP(B4:B13=B5:B13,-1) #4 =I4#*1 Which is : =DROP(B4:B13=B5:B13,-1)*1 #5 =VSTACK(1,J4#) Which is : =VSTACK(1,DROP(B4:B13=B5:B13,-1)*1) #6 =FILTER(G4#,K4#=0) Which is : =FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0) #7 =HSTACK(G4#,B4:E13) Which is : =HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13) #8 =IFNA(VSTACK(L4#,M4#),"") Which is : =IFNA(VSTACK(FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0),HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)),"") #9 =SORT(C18#,1,1) Which is : =SORT(IFNA(VSTACK(FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0),HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)),""),1,1) #10 =DROP(I18#,,1) Which is : =DROP(SORT(IFNA(VSTACK(FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0),HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)),""),1,1),,1) In one formula : =LET( a, B4:E13, b, TAKE(a, , 1), c, SEQUENCE(ROWS(a)), DROP( SORT( IFNA( VSTACK( FILTER( c, VSTACK(1, DROP(b = DROP(b, 1), -1) * 1) = 0 ), HSTACK(c, a) ), ), 1, 1), , 1))
