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:
image

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.
  5. If X is not found, go to next row
  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:

image

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:

image

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:

image

Now we just need to unpivot the units back:
image

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

raskarov.morph (15.3 KB)

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…

image

Thanks again for your help

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

I assume that also answers your second question.

Hi @dgudkov it works well but the only issue that’s missing is “Search in group”

Basically, if Z exists, I need to remove both Z and D. Right now, I can’t do this because I need to search for D in each group where Z exists.

If Z doesn’t exist, I need to keep D. I couldn’t find action to search in group.

You can modify your lookup table so that it includes the code into the lookup column as well. For instance

Y   Y
Y   C
Z   Z
Z   D