Timeline for merge data of columns that have the same label vertically in google sheets
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 30, 2022 at 16:49 | history | edited | Martín | CC BY-SA 4.0 |
added 702 characters in body
|
| Dec 30, 2022 at 16:47 | comment | added | Martín | I'm glad it was helpful!! Just in case a couple of clarifications: in order for this to work you'll need to always have those 5 headers each time a new set of columns if added. If by chance some time it doesn't, include it or the sorting will be messed up. 2) In order to include more columns, if you know that the rows won't be that much (you can change the ranges to include all further columns: =SORT(SCAN(;A2:E2;LAMBDA(ag;va;{FLATTEN(FILTER('Raw Data'!A4:100;'Raw Data'!A2:2=va))}))) Just change 100 with some bigger or smaller number of your convenience. Good luck! | |
| Dec 30, 2022 at 15:36 | comment | added | MarioJBlidge | ok, got ya, smart approach. This is such a great and simple solution for this very common problem, that no one else (at least not the forum threads that I read) had a simple solution for. I do hope that more people read this thread. Thank you very much! Appreciate it | |
| Dec 30, 2022 at 13:58 | comment | added | Martín | I meant that "ag" is actually empty and skipped it intentionally. For example: =SCAN(0,A1:A,LAMBDA(ag,val,ag+val)) would return a cumulative sum of the values of that column. But by not using it doesn't accumulate and do independent calculations | |
| Dec 30, 2022 at 13:48 | comment | added | Martín | Right, it's a kind of "trick". SCAN is used for partial or accumulative calculations and sequences, for example. But by not using that argument it allows me to have the five independent columns in just one formula, that won't be possible with the limitations of another ARRAYFORMULAs | |
| Dec 30, 2022 at 13:36 | comment | added | MarioJBlidge | still have a couple of questions if it's ok: =SORT(SCAN(;A2:E2;LAMBDA(ag;va;{FLATTEN(FILTER('Raw Data'!A3:Z1000;'Raw Data'!A2:Z2=va))}))) ag = is this the name of the lambda function? va = is actually a variable to store A2:E2? SCAN(**;**A2:E2 = the first semicolumn confuses me a bit (in the formula description it says that the first argument is the "first value" - but you don't need that in this case so you just leave it empty, correct? | |
| Dec 30, 2022 at 13:28 | comment | added | MarioJBlidge | ok this was very helpful | |
| Dec 30, 2022 at 12:39 | comment | added | Martín | No problem: SCAN let me perform the same operation through the five headers of A2:E2 one by one (LAMBDA is just auxiliary of it for naming variables) I use FILTER to find the columns that match the headers, FLATTEN to join all those found columns in one. And finally I use SORT because FLATTEN would have the values mixed by its original rows. Let me know if it's helpful! | |
| Dec 30, 2022 at 12:17 | comment | added | MarioJBlidge | thank you so much, this helps a lot!! The only thing is, that I don't quite understand it. Would you mind explaining it a little bit? I know it's probably a lot to ask, but it would really help me using it further. | |
| Dec 30, 2022 at 12:10 | vote | accept | MarioJBlidge | ||
| Dec 30, 2022 at 11:00 | history | answered | Martín | CC BY-SA 4.0 |