# Can you seek for cells in a direction?

I have columns in descending categories like this:

``````Family         Genus            Species           Subspecies
``````

An example spreadsheet can look like this:

``````Family1
Genus1
Species1
Species2
Subspecies1
Subspecies2
Species3
Genus2
Species4
Subspecies3
``````

Now I want to create a list of all entries formatted like this:

``````Family1.Genus2.Species4.Subspecies3
``````

My problem is that if I make a formula on the cell of Subspecies3, I need to seek an unknown number of cells up in each previous column to find the entry for it (unless I clutter the entire spreadsheet with repeats, which I donât want).

Is it possible to create a formula that does this, or is there a better method for describing a tree in a spreadsheet and then converting it to the format I desire?

To be sure that I correctly understood the question - do you expect such a result?

When I understand that the formula from the built-in functions will be too cumbersome, I write a macro - itâs easier and faster. The code looks like this:

``````Function Tree_As_Strings(aData As Variant, Optional separator) As Variant
Dim aRes As Variant
Dim i As long, j As Long
If IsMissing(separator) Then separator="."
ReDim aRes(1 To UBound(aData,1), 1 To 1)
If aRes(1,1) = "" Then
Tree_As_Strings = "First cell of range must be ROOT"
Exit Function
EndIf
aRes(1,1) = aRes(1,1) + separator + Trim(aData(1,j))
Else
Exit For
EndIf
Next j
For i = 2 To UBound(aData,1)
For j = 1 To UBound(aData,2)
Else
Exit For
EndIf
Next j
If Trim(aData(i,j)) <> "" Then aRes(i,1) = aRes(i,1) + separator + Trim(aData(i,j))
Next j
Next i
Tree_As_Strings = aRes
End Function
``````

Since this function returns not a single value, but a whole array, its input should be completed with the combination of keys Ctrl+Shift+Enter instead of the usual Enter or Tab.

It works like this:

TreeToStrings.ods

Thanks, that was a very thorough and nice answer!

I copied your code and added this as a module in my document and ran it, but only the first line appearedâŚ it doesnât fill out anything in the rest of the columnâŚ would you happen to know why this may be? (I havenât looked into macros yet so Iâm going to have to start doing that to see how I can debug this.)

Perhaps you completed the entry by pressing Enter, and not three keys, as indicated in the description. (If you have a Mac, then the keys may be different â + RETURN, â + SHIFT + RETURN, or some other - you need to look in the help.) To fix this simply - select the cell with the formula, press Ctrl + F2 or click the button Master Functions (top left, on the Formula Panel, before of the Sigma sign), in the form that opens bottom left turn on the Array checkbox and click OK.

Thank you very much, that was it!

Just for completeness: A solution without user code. ask243547taxonomicTextjoin.ods
Needs LibreOffice 6.2 or higher.

(As always: Interested in refutations.)

1 Like

Well, two years later I see this, and I must admit, the âarrayâ result in the accepted answer has been driving me crazy because I really donât like the Ux around that at all (itâs not intuitive).

This made me want to implement your formula instead, but unfortunately I get Err:522 on everything but the first line when I try to split it into different sheets. Is there an easy answer as to why? Iâve already made a numerical adjustment at the end of the expression to account for the extra column compared to your example but that wasnât enoughâŚ

DungeonItems.ods (23.4 KB)