Hey I am trying to dynamically flatten the attached matrix to return two columns (A6:B17). The first being the associated letter, and the second being a combination of the numbers and columns. I think this can be down with a combination of lookups, tocol, and unqiue; but I can't quite get it to work. Thanks so much!
You are talking in your post about TOCOL.
If your Excel recognizes TOCOL you are using Office 365, please update your profile.
Excel 365 solution:
One way:
Please try:Formula:
Please Login or Register to view this content.
Last edited by HansDouwe; 02-07-2024 at 09:56 PM.
Another way.
Formula:Please Login or Register to view this content.
Last edited by DJunqueira; 02-08-2024 at 12:53 AM.
Nice formula @DJunqueira, but your formula returns only the second column, but not the first column with a's, b's and c's.
Tks Hans,
Tks again Hans.
Formula:Please Login or Register to view this content.
Last edited by DJunqueira; 02-08-2024 at 01:01 AM.
Nice formula DJunqueira
Tks Hans, your is too as usual.
Re: Flatten a Random Matrix
Thanks so much Hans and DJ, impressive!
Last edited by andy521; 02-08-2024 at 11:59 AM.
Both excellent! One tweak (in case cols B, C & D can be blank):
=TEXTSPLIT(TEXTJOIN(";",1,BYROW(A2:D4,LAMBDA(x,IFERROR(TEXTJOIN(";",1,TOROW(TAKE(x,,1)&""&(TOROW(DROP(x,,1),1)&TOCOL(E1:F1,1)),1)),"")))),"",";",1)
It's always nice to see these approaches to help learning.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Nice addition Glenn!
=TEXTSPLIT(TEXTJOIN(";",1,BYROW(A2:D4,LAMBDA(x,IFERROR(TEXTJOIN(";",1,TOROW(TAKE(x,,1)&"|"&(TOROW(DROP(x,,1),1)&TOCOL(E1:F1,1)),1)),"")))),"|",";",1)
I would just point the 2 missing signs '|' that you may have some problem with it disappearing as I did when writing the formula in the forum message. First I tried with \ that is the sign Excel uses in my locale to indicate change column.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks