SOLVED

Rearranging columns with hierarchy

%3CLINGO-SUB%20id%3D%22lingo-sub-3233316%22%20slang%3D%22en-US%22%3ERearranging%20columns%20with%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3233316%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20need%20to%20re-arrange%20two%20columns%20into%20three%20depending%20on%20hierarchy%20and%20I%20am%20not%20sure%20how%20to%20approach%20it.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20three%20levels%20in%20the%20hierarchy%3A%3C%2FP%3E%3CP%3EA%20-%20Grand%20parent%3C%2FP%3E%3CP%3EB%20-%20Parent%3C%2FP%3E%3CP%3EC%20-%20Child%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20three%20levels%20are%20distributed%20in%20two%20columns%20like%20so%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EChild%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EParent%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E-%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20them%20in%20the%20following%20arrangement%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EGrandparent%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EParent%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EChild%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EC%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3233316%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3238547%22%20slang%3D%22en-US%22%3ERe%3A%20Rearranging%20columns%20with%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3238547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1320893%22%20target%3D%22_blank%22%3E%40EdgarEngieImpact%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20hard%20to%20follow%20exactly%20what%20you%20want%20here.%20The%20basic%20concept--of%20hierarchy%2C%20three%20level%20hierarchy--is%20clear.%20What's%20%3CU%3E%3CSTRONG%3Enot%3C%2FSTRONG%3E%3C%2FU%3E%20clear%20is%20how%20to%20take%20that%20A-B-C%20and%20the%20labels%20organized%20in%20cells%2C%20what%20the%20actual%20data%20is%20that%20you're%20working%20with%2C%20how%20it's%20laid%20out%2C%20how%20it's%20current%20identified%20as%20Grandparent%2C%20Parent%20or%20Child.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20it%20would%20help%20if%20you%20gave%20a%20few%20more%20illustrations%20complete%20illustrations%2C%20preferably%20NOT%20using%20A%20B%20C%20as%20your%20data%20elements%2C%20since%20they're%20obviously%20sequential%20in%20and%20of%20themselves.%20What%20are%20some%20random%20data%20elements%2C%20how%20would%20one%20know%20which%20is%20grandparent%2C%20parent%20or%20child%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3240025%22%20slang%3D%22en-US%22%3ERe%3A%20Rearranging%20columns%20with%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%2C%20thanks%20for%20your%20comments.%20I%20am%20trying%20to%20organize%20marketing%20campaigns%20hierarchally.%20The%20first%20table%20is%20given%20to%20me%20by%20a%20SalesForce%20report%2C%20which%20can%20provide%20only%20a%20two%20level%20hierarchy%20(Campaign%20name%20and%20Campaign%20Parent%20name).%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20know%20if%20it's%20grandparent%3A%26nbsp%3B%26nbsp%3BIt%20doesn't%20have%20a%20parent%20associated%3C%2FP%3E%3CP%3EHow%20to%20know%20if%20it's%20parent%3A%20it%20has%20a%20parent%20associated%20which%20in%20turn%20doesn't%20have%20a%20Campaign%20parent%20associated.%3C%2FP%3E%3CP%3EHow%20to%20know%20if%20it's%20a%20child%3A%20it%20has%20a%20Campaign%20parent%20associated%2C%20which%20has%20another%20Campaign%20parent%20associated%2C%20which%20doesn't%20have%20one%20itself.%3C%2FP%3E%3CP%3EAs%20a%20more%20defined%20example%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2244.44406133549868%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2241px%22%3E%3CU%3E%3CSTRONG%3ECampaign%20name%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2241px%22%3E%3CU%3E%3CSTRONG%3ECampaign%20parent%20name%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2257px%22%3ECampaign%20Grandparent%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2257px%22%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2257px%22%3ECampaign%20Parent%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2257px%22%3ECampaign%20Grandparent%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECampaign%20child%201.1%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECampaign%20parent%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECampaign%20child%201.2%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECampaign%20Parent%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECampaign%20Grandparent%202%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECampaign%20Parent%202%3C%2FTD%3E%3CTD%3ECampaign%20Grandparent%202%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20goal%20is%20to%20have%20it%20organized%20in%20hierarchy.%20I%20tried%20with%20pivot%20tables%20while%20keeping%20only%20only%20these%20two%20columns%2C%20but%20the%20fact%20that%20the%20campaign%20hierarchy%20depth%20varies%20between%201%2C%202%20or%203%20levels%20makes%20it%20very%20hard%2C%20that's%20why%20I%20thought%20splitting%20it%20in%20three%20columns%20should%20make%20it%20easier.%20Like%20so%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EGrandparent%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EParent%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EChild%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECampaign%20Grandparent%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECampaign%20Parent%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECampaign%20Child%201.1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECampaign%20Grandparent%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECampaign%20Parent%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ECampaign%20Child%201.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECampaign%20Grandparent%202%3C%2FTD%3E%3CTD%3ECampaign%20Parent%202%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it's%20more%20clear.%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I need to re-arrange two columns into three depending on hierarchy and I am not sure how to approach it. 

I have three levels in the hierarchy:

A - Grand parent

B - Parent

C - Child

 

These three levels are distributed in two columns like so:

ChildParent
CB
BA
A-

 

I would like to have them in the following arrangement:

GrandparentParentChild
ABC

 

Thank you in advance.

3 Replies

@EdgarEngieImpact 

 

It's hard to follow exactly what you want here. The basic concept--of hierarchy, three level hierarchy--is clear. What's not clear is how to take that A-B-C and the labels organized in cells, what the actual data is that you're working with, how it's laid out, how it's current identified as Grandparent, Parent or Child. 

 

I think it would help if you gave a few more illustrations complete illustrations, preferably NOT using A B C as your data elements, since they're obviously sequential in and of themselves. What are some random data elements, how would one know which is grandparent, parent or child, etc.

@mathetes 

I see, thanks for your comments. I am trying to organize marketing campaigns hierarchally. The first table is given to me by a SalesForce report, which can provide only a two level hierarchy (Campaign name and Campaign Parent name). 

How to know if it's grandparent:  It doesn't have a parent associated

How to know if it's parent: it has a parent associated which in turn doesn't have a Campaign parent associated.

How to know if it's a child: it has a Campaign parent associated, which has another Campaign parent associated, which doesn't have one itself.

As a more defined example:

Campaign nameCampaign parent name
Campaign Grandparent 1-
Campaign Parent 1Campaign Grandparent
Campaign child 1.1Campaign parent
Campaign child 1.2Campaign Parent
Campaign Grandparent 2-
Campaign Parent 2Campaign Grandparent 2

 

The goal is to have it organized in hierarchy. I tried with pivot tables while keeping only only these two columns, but the fact that the campaign hierarchy depth varies between 1, 2 or 3 levels makes it very hard, that's why I thought splitting it in three columns should make it easier. Like so:

 

GrandparentParentChild
Campaign Grandparent 1Campaign Parent 1Campaign Child 1.1
Campaign Grandparent 1Campaign Parent 1Campaign Child 1.2
Campaign Grandparent 2Campaign Parent 2-

 

Hope it's more clear. Thank you.

best response confirmed by EdgarEngieImpact (Occasional Contributor)
Solution

@EdgarEngieImpact Have a look at the attached file. It contains a Power Query solution that uses a wonderful script that I picked-up from the Thebiccountant.com (link is in the file). I tweaked it a bit so that it matches the outcome you desire, taking no credit for it.

Riny_van_Eekelen_0-1646212999680.png

It's actually meant to explode a Bill of Materials with quantities into a list of all the individual items used. But it works the same for your list of campaigns. Kindly refer to the link in the file to learn more about the techniques used.