User Profile
DrExcel_Excel_MVP
Copper Contributor
Joined 2 years ago
User Widgets
Recent Discussions
GROUPBY Function And Merged Cells
How to Deal With Merged Cells in Excel if you want to summarize your data withGROUPBY 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 Excelfunction 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 LAMBDAthat 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 Excelto 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 FunctionUNPIVOT 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 " inBlank 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 #Hazem_Hassan #Dr_Excel #excel#excelskills#exceltricks#exceltips#mvp#mvpdevelopment#mvpbuzz#mvps#MicrosoftAmbassadorTEXT.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. TEXT.SELECT This formula allows the user to select a specific piece of text from a cell (in the same way as the Text.Select in PowerQuery). It consists of 4 main parts =TEXT.SELECT ( MixedText ,Select, Space, Keep) ➡MIXEDTEXT :Select the Cell ➡SELECT : Type 1 for Numbers Type 2 f or English Letters Type 3 for UppercaseEnglish Letters Type 4 for LowercaseEnglish 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-alphanumerictype 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 mentionedSome 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? #Hazem_Hassan#Dr_Excel#excel#excelskills#exceltricks#exceltips#mvp#mvpdevelopment#mvpbuzz#mvps#MicrosoftAmbassadorLeading and Trailing Zero in Excel
How toRemove Leading and Trailing Zeros in Excel We have a set of product codes like this : 000P2I290002M900 we want to make it like thisP2I290002M9 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 : https://www.youtube.com/DroosHROnline #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))
