Fill to the left

1

Hi Team

We have fill down and fill to the left.
Let’s say I have the following:

Org1; Org2; Org3: Org4; Org5; Org6: Org7; Org8
Value1; Blank; Value2; Blank; Blank; Value3; Blank

I want to rearrange it to:

Org1; Org2; Org3: Org4; Org5; Org6: Org7; Org8
Value1; Value2; Value3

How can I achieve this without a lot of if statements?

Thanks

Rykie

2

Hello @Rykie, a couple of questions - is the table always going to have only one row of data? Are the numbers in the column headers going to be there, or is that just a sample?

Thanks

3

Hi Roberto
Thank you for your quick response.

The above data refers to the Organisation structure. There are no numbers in it, but I can rename it to Org Level1, Org Level2 etc.
Every Row with have a person (and other data) and then the organisation structure next to it.
The organisational levels needs to shift to the left if it is empty.

Hope that answers the questions.

R

4

Hi @Rykie,

I’m afraid I don’t understand it very well. Can you give me an example with more sample data? So the table can have multiple rows?

Regards

5

Here is an example:

Person ID Org1 Org2 Org3 Org4 Org5 Org6 Org7 Org8
1 xxxx xxxx xxxx xxxx xxxx
2 xxxx xxxx xxxx xxxx xxxx
3 xxxx xxxx xxxx xxxx
4 xxxx xxxx
5 xxxx xxxx
6 xxxx xxxx xxxx xxxx xxxx
7 xxxx
8 xxxx xxxx xxxx xxxx xxxx
9 xxxx xxxx xxxx
10 xxxx xxxx

The xxxx presents different values

This is the end result

Person ID Org1 Org2 Org3 Org4 Org5 Org6 Org7 Org8
1 xxxx xxxx xxxx xxxx xxxx
2 xxxx xxxx xxxx xxxx xxxx
3 xxxx xxxx xxxx xxxx
4 xxxx xxxx
5 xxxx xxxx
6 xxxx xxxx xxxx xxxx xxxx
7 xxxx
8 xxxx xxxx xxxx xxxx xxxx
9 xxxx xxxx xxxx
10 xxxx xxxx

Need to remove all the empty cells and shift everything left.
I can do this with multiple if statements.
I just wanted to know if there is a better way of doing this
The Org1 will never be empty

6

Hello @Rykie, thanks for the example.

Here is how I would do it - I would unpivot the table, transform the data and then pivot the table again to obtain the desired output.

Here is sample project and the table to transform in Excel. Download both files (storing them in the same folder), and then open the .morph to see the transformations.

Fill to the left.morph (4.6 KB)
Table to transform.xlsx (8.8 KB)

For this solution to work it’s important for the Column names to have the number at the end. This also orders the columns correctly after the final pivot.

Here’s a general view of the steps:

Fill to left

Regards

3 Likes
7

Hi,

here’s another solution. Maybe the formula is too ugly, but finally it works. :slight_smile:

Fill to the left.morph (4.1 KB)
Just copy&paste your example data into the first action.

Always interesting to see the different possibilities to solve a problem with easymorph.

Regards,

Jochen

8

Thank you @roberto. This is easier than all my if statements. I am going to try it.

9

Thanks, @Jochen_Marquardt - I will try this out.

10

@roberto - I implemented this solution with a tweek. Thank you. It works well. :grinning:

1 Like
11

@Jochen_Marquardt I love that we can approach the same issue from different sides. Thank you for your contribution. I always say there is more than on way to get to the end result. We need to figure out what works best in your current environment.

1 Like