Search, remove, and shift

Hi EasyMorph Team,
So far, EasyMorph has helped us to do some crazy transformation and saves a lot of time, thank you for all your help over the past couple of months.

I have an issue I do not know how to approach

Basic premise: find and extract portions of the file into a separate file and shift data.

Simplified example file attached.

Tab: Code:

Tab: Data (Source)

What we need to have as a result:
File 1:

File 2:

Steps:

1. Search all “Code” columns for X and if found, cut all four related columns (Fee, Code, Multiplicator, Minimum)
2. In the same row, find Z and cut all four related columns (Fee, Code, Multiplicator, Minimum.)
3. Paste X and Z values (8 columns) + related info such as name, company name, etc into a separate file.
4. Shift cells left after cutting.
6. Once all rows are processed, do the same for code “Y”

The complexities that I can’t figure out:

1. Fee,Code,Multiplicator,Minimum can appear from 5 to 50 times on the file and I’m not sure how to properly search
2. Is there a way to shift cells left similar to excel after cutting?

Any help and pointers are appreciated!
ExtractingData.xlsx (13.5 KB)

Here is how I understood the logic:

Fee,Code,Multiplicator,Minimum represent a unit of information. Basically the structure of table is as follows:

When one unit is removed (e.g. Unit2), the units to the right of it move left. It would be easier to deal with if we straighten the table:

Now, instead of moving units leftward, they will move upward. But wait, this is just regular filtering! So let’s mark the units to be removed, and then split them into two different tables:

Now we just need to unpivot the units back:

All this can be done in EasyMorph. Here is a project that does exactly that:

Gotchas:

• To preserve column order in the result tables I use the feature of the “Append” action to preserve the column order of the table to which the other table is appended.
• Before the very first unpivoting, I enumerated rows to make sure each row has a unique ID that doesn’t depend on other columns’ values, so unpivoting won’t accidentally deduplicate rows.

@dgudkov

I’m impressed that this is even possible in easy morph. I ran this on my file and it works perfectly. Thank you for spending time and working on this.

One question - In your example, you put ‘Y’ in column “Remove” but I also need to remove and shift the lookup value. So, I need to do what you do for “Y” but also do it for “X” (Remove eight cells per row, not four cells per row.) Do I need to add “Remove (2)” column and put lookup value there and iterate?

And one more question - in “expected result (1) unordered”, you have Filter action to remove all “Y” values. Is there a way to make this value dynamic based on the lookup table. (Since lookup table is dynamic in our project.) Not sure if this is possible…

The “Y” in the “Remove” column simply means “yes”. It’s a flag that indicates which rows have to be removed.

``````Y   Y