Transpose Matrix data to Column formate

Question

Hi,

I'm trying to transpose a matrix dataset into column format in two columns RelatedFromID & RelatedToID,

1. Where the RelatedFromID is always "1001" in level 1 and RelatedToID is taken from level 2, has to happen until the last data in Level 1 column

2. Once the iteration in Level 1 is done, the iteration should pick level 2 data and put in RelatedFromID and pick level 3 data in RelatedToID and this should happen until the last level 11.

3. Finally I need to assign GID (unique ID) based on common ID

The sample data and desired output is attached. I'm still contemplating on how to assign GIDs.

Thanks!

Bharat

MatrixData.xlsx

Christina_H · Accepted Answer

This will convert the matrix into the output format.  I'm using the GID to remove duplicate values.