User Profile
TTodorov
Copper Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Re: Recursive Lambda: Unpacking LET function
djclementsMany thanks, formula works perfect, seems to be fastest one. Would be possible to "remove duplicates"? I mean, now formula duplicates positions by objects. If I create sorted table by positions and objects, I see duplicates: In case of duplicates, I want to keep just line with priority = 0Re: Recursive Lambda: Unpacking LET function
Hi all, PeterBartholomew1, SergeiBaklan I'm respected a lot from you knowledges in Excel, many thanks for your time and shared solutions. rachelagain want to thank you for kind support with solutions. djclementsthank you for suggestions and shared variants. I started with simple task - to try to identify duplicates and prevent mistakes in future data transformations in Power Query. To do that, I realized that I need a list. That was my first challenge. Seems easy, even for me, I created some kind of list. But realized, that I need not only splitting, but also replacing in same time. And I realized that I have not enough knowledges to do that with formulas. I solve this challenge with Power Query, but I need result without need of refreshing. I have data table like this: Key columns are Objects, Positions, ID and Priority. I also have table ObjetsPositions: In data table, I need to handle these replacements: 1. Challenge "For all". This mean that I need to replace "For all" with all objects (from table ObjetsPositions) which have same positions (from data table). 2. Chanllenge "99". This mark "last" of positions. Seems easy, but something here, I realized that I need to get information from other two columns - ID and Priority. a) ID = "PARK-0000". In this case, I need to replace 99 with last position for each Object. b) ID = "PERS-0000". In this case, if the store have more than 4 positions, I need to replace 99 with 4. In other case, I need to replace 99 with last position for each Object. Facing with this challenges, rachel helped me with solutions. Many thanks rachel! 🙂 First solutions works good with 2 key columns, but in with more columns, probably because of Excel limitation, there is and errors. After that rachel adapted it and with recursive Lambda in named range ListPositionsAndObjects: =LAMBDA( data; LET( pairs; DROP( REDUCE( ""; SEQUENCE(ROWS(data); 1; 1; 1); LAMBDA( list; idx; VSTACK( list; TOCOL( LEFT(INDEX(data; idx; 3); 9) & "@" & TEXTSPLIT( INDEX(data; idx; 1); ", "; ; TRUE; 0; "" ) & "@" & TRANSPOSE( TEXTSPLIT( INDEX(data; idx; 2); ", "; ; TRUE; 0; "" ) ) & "@" & INDEX(data; idx; 5) ) ) ) ); 1; 0 ); DROP( REDUCE( ""; pairs; LAMBDA( list; pair; VSTACK( list; TEXTSPLIT(pair; "@") ) ) ); 1; 0 ) ) ) And after that with one more formula: =LET( objects_by_position; ListPositionsAndObjects(SampleTable); ids; INDEX(objects_by_position; ; 1); positions; INDEX(objects_by_position; ; 2); objects; INDEX(objects_by_position; ; 3); replaced_positions; VALUE(IF(TRIM(positions) <> "99"; positions; IF(ids = "PERS-0000"; IF(MAXIFS(ObjetsPositions[Position]; ObjetsPositions[Object]; objects) < 4; MAXIFS(ObjetsPositions[Position]; ObjetsPositions[Object]; objects); "4"); MAXIFS(ObjetsPositions[Position]; ObjetsPositions[Object]; objects)))); replaced_objects; IF(objects <> "For all"; objects; MAP(replaced_positions; LAMBDA(x; TEXTJOIN(", "; TRUE; FILTER(ObjetsPositions[Object]; ObjetsPositions[Position] = x))))); ListPositionsAndObjects(HSTACK(replaced_positions; replaced_objects; ids)) ) I received desired list. But realized that for identifying duplicates I need to use at least 4 columns, not 3. After that SergeiBaklan shared also solution (with 2 columns, without additional logic). Many thanks Sergei! 🙂 Seems that due to complexity of the task, finding duplicates with formulas also take time. I didn't measured to compare with result from Power Query, but calculations take time. 🙂 Will continue to try to find a way (with 4 and more columns), in any way! 🙂 Till then, as attached file you can find sample table with solutions from rachel and SergeiBaklan. Again, want to thank you for sharing knowledges and solutions! 🙂 Best regards TodorRe: Recursive Lambda: Unpacking LET function
rachel Probably was my mistake, excuse me! =TEXTSPLIT( ARRAYTOTEXT( MAP( SEQUENCE(ROWS(A1:A30); 1; 1; 1); LAMBDA(row_index; LET( data; A1:B30; xs; TEXTSPLIT(INDEX(data; row_index; 1); ", "); ys; TEXTSPLIT(INDEX(data; row_index; 2); ", "); ARRAYTOTEXT(xs & "-" & TRANSPOSE(ys)) ) ) ) ); ; "; " ) This works perfect, many thanks! 🙂Re: Recursive Lambda: Unpacking LET function
Hi, I'm trying to transform this table (two columns, separated in text bellow with symbol "|"): 3 | 138, 169, 192, 193 3, 4 | 127 2, 3 | 115, 138, 144 to this column: 3-138 3-169 3-192 3-193 3-127 4-127 2-115 2-138 2-144 3-115 3-138 3-144 I prepared with helper table, wit formula TOROW(TEXTSPLIT($A2, , ", ") & "-" & TEXTSPLIT($B2, ", ")) for each row. In sample above $A2 = 3 and $B2 = 138, 169, 192, 193 After that with TOCOL() from helper table will have result column as mentioned. But wonder for better way (without helper table). Is it possible to be done with recursive LAMBDA?
