Copy rows based on the condition above the row or below the row?

Question

Hi All,

Kindly help me to get out of this problem. Any help will be highly appreciated.

I am looking for following solution:

we need to check that each file name has H3, H2 and H1. If they are missing any levels, we should inherit the data from the level above.
              - So for example, ABC has only H1 and H2, we would create a copy of H2 and label it H1
              - If client DEF has only H3, we create 2 copies of H3 and label one as H2 and another as H1

Please find the attached input file and how does output should look like.

Input File.xlsx

output.xlsx

Kurohits · Answer

Hi @mceleavey

Thanks for sharing quick solution. It was helpful but not able to implement the concepts properly in real scenario. I am come up with other scenario that may help with to solve my problem. Can you please help to build this logic?

I have worksheet with different fields. Out of all fields there should be always 3 fields which must be there in that worksheet. H1,H2,H3. Please check the input file. In that file, h1-team field name is there but other 2 fields are missing. The logic should be if h2 and h3 is missing then copy data from h1 and label it h2 and h3, similarly, if h2 and h3 is there in the field then create new field h1 and copy data from next level data (copy from h2) and label it h1. I hope I have phrased the problem statement clearly. if not then please let me know we can do private chat if possible for you.

Sharing input and output file for your reference.

Thanks in advance!!! your help will be highly appreciated.

Input File.xlsx

output.xlsx

mceleavey · Answer

Hi @Kurohits ,

this requires something called a Cartesian Join, which creates every possible combination in a dataset. I've built this using the append tools:

This produces the correct output:

I hope this helps,

M.

cartesian join mceleavey.yxmd