Forum Discussion
Merged subcells and displaced data
Attached is a possible LAMBDA solution. Please note my solution accounts for 6 columns. I see there were a few entries in the column right of Skill so I included it in the calculations.
Fill-in gaps in the data:
=LAMBDA(arr,col_num,SCAN("",INDEX(arr,SEQUENCE(ROWS(arr)),col_num),LAMBDA(a,v,IF(v="",a,v))))Piece it all together:
=LAMBDA(arr,CHOOSE({1,2,3,4,5,6},FILL(arr,1),FILL(arr,2),FILL(arr,3),FILL(arr,4),INDEX(TEXT(arr,";;;General"),SEQUENCE(ROWS(arr)),5),INDEX(TEXT(arr,";;;General"),SEQUENCE(ROWS(arr)),6)))I avoided using insider functions which would have made it simpler.
The end result (all you need to give CLEANUP is the entire range, including header row):
Jean
Attached is a possible LAMBDA solution. Please note my solution accounts for 6 columns. I see there were a few entries in the column right of Skill so I included it in the calculations.
Fill-in gaps in the data:
=LAMBDA(arr,col_num,SCAN("",INDEX(arr,SEQUENCE(ROWS(arr)),col_num),LAMBDA(a,v,IF(v="",a,v))))
Piece it all together:
=LAMBDA(arr,CHOOSE({1,2,3,4,5,6},FILL(arr,1),FILL(arr,2),FILL(arr,3),FILL(arr,4),INDEX(TEXT(arr,";;;General"),SEQUENCE(ROWS(arr)),5),INDEX(TEXT(arr,";;;General"),SEQUENCE(ROWS(arr)),6)))
I avoided using insider functions which would have made it simpler.
The end result (all you need to give CLEANUP is the entire range, including header row):
Patrick2788 Thank you! Thank you! That worked.
I've tried it with all of the data I've been given, nearly 500,000 lines of information, and it handles every unintended variation in the data so far. I think you just saved my sanity 8^) , or at least saved me from tearing my hair out.
Thanks again,
Jean
Good to see that your problem got resolved, but I can't help wondering how you dealt with the "Donald Duck" part of your example data set (rows 34 and down). The "Mickey Mouse" rows are easily resolved with Power Query. No need for LAMBDA functions. They work great for Mickey but they don't seem to handle Donald very well. Perhaps I missed something.
- Riny_van_Eekelen
You didn't miss anything.
I don't think the Donald problem is fixed, but once I cleaned up the Mickey examples, I found the Donald problem only occurred twice, in the input by two different people, throughout the whole data set. I'm still trying to figure Donald out.
Thanks!
Jean
- Glad to hear it worked on such a large data set! It may be a bit quicker once CHOOSECOLS/CHOOSEROWS become available.
