I have only seen how to remove the entire row if you get a single blank cell but I basically just want to slide the cells to the right over:
Original:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data | Data1 | ||
| 2 | Data | Data2 | ||
| 3 | Bad Data | Data3 | ||
| 4 | Data | Data4 | ||
| 5 | Data | Data5 |
Result trying to get after filter:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data | Data1 | ||
| 2 | Data | Data2 | ||
| 3 | Data | Data4 | ||
| 4 | Data | Data5 |
I know this is odd since normally this would mean things would be messy but columns B, C, and D are mutually exclusive so they will never both have a value. I'm also trying to accomplish this by using functions and not clicking buttons in the tabs.
CodePudding user response：
With LET and BYROW:
=LET(
grp,A1:A5,
dta,BYROW(B1:D5,LAMBDA(a,INDEX(a,MATCH("*",a,0)))),
FILTER(CHOOSE({1,2},grp,dta),grp<>"Bad Data"))
The BYROW part goes row by row of the data and returns the first cell with data in each row as an array.
Then it is just a matter of using CHOOSE() to stack the two arrays side by side and filter bases on the criteria.
There is currently a function in Beta called HSTACK that can replace the CHOOSE when it is release to the whole.
=LET(
grp,A1:A5,
dta,BYROW(B1:D5,LAMBDA(a,INDEX(a,MATCH("*",a,0)))),
FILTER(HSTACK(grp,dta),grp<>"Bad Data"))