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):
The way I look at your data, you need to unmerge all cells then fill in the blanks with the value above so you can sort or filter. This becomes tedious if you have to do this each time.
If you have access to SCAN or MAP, something can be drawn up.
- Thank you! I'll try this.
JeanAttached 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
