Creating a hierarchy in excel

[safniki]

In an excel sheet I have hierarchy data of units from columns 1-8 (or A-H). The first row is a header. In each row there is only 1 cell that is not empty and that one cell includes the name of a unit. The following example describes the under-over-order relationship of units: Unit A's name is located in e.g. E10 and its superior unit's name is located upwards in the first row from Unit A's row but exactly 1 column left from Unit A's column so in this example the superior unit name will be in column D and somewhere between row 2-9. In column I there is a selfID which is uniqie to each unit. I would like to have an excel formula that provides in column J (parentID) the selfID of the unit's superior unit. If a unit doesn't have a superior unit (because the given unit is in column A and there is no other columns to the left of A) leave the superior unit's cell empty. In the attached photo I already manually filled column J with data that I would expect to get as a result of some excel magic.

 

[duggie33]

Hi safniki,

I am not a data formatting expert, but from my limited experience, your data would be easier to use if you had one column for the Level rather than eight columns for the levels. The selfID could be generated somewhat automatically, but it seems like the parentID should be entered by the user for cases where, for example, a department is added at a later date. You want to link it to an earlier parent without have to find the spot and insert a row, etc.

Book1
	A	B	C	D
1	Level	Description	selfID	parentID
2	1	Country1	1001
3	2	Region1	1002	1001
4	3	Department1	1003	1002
5	2	Region2	1014	1001
6	3	Department2	1015	1014
Sheet1

Again, I am not an expert on this topic; there are likely many others in the forum that can offer much better advice.

Hope that helps,

Doug

 

[safniki]

Hi Doug,

Thanks a lot for your thoughts, however, I wanted to keep the original structure. In another forum I have received a solution that seems to be working. I am sharing it here so it may help others with similar problems:
=LET(_a,INDEX(I$2:I2,XMATCH(TRUE,INDEX($A$2:$H$2,XMATCH(TRUE,A2:H2<>"")-1):INDEX(A2:H2,XMATCH(TRUE,A2:H2<>"")-1)<>"",,-1)),IF(IFERROR(_a=I2,TRUE),"",_a))

Cheers,
Safniki

 

[Fluff]

Welcome to the MrExcel Message Board!

For future reference

In another forum I have received a solution that seems to be working.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide links to any site where you have asked this question.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[safniki]

This was another forum where I posted my question:

https://www.reddit.com/r/excel/comments/16gc78k