Can you seek for cells in a direction?

[eobet]

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?

[JohnSUN]

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

[JohnSUN]

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)
	aRes(1,1) = Trim(aData(1,1))
	If aRes(1,1) = "" Then 
		Tree_As_Strings = "First cell of range must be ROOT"
		Exit Function 
	EndIf 
	For j = LBound(aData,2)+1 To UBound(aData,2)
		If Trim(aData(1,j)) <> "" Then
			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)
			If Trim(aData(i,j)) = "" Then 
				aData(i,j) = Trim(aData(i-1,j))
			Else
				Exit For 
			EndIf 
		Next j
		aRes(i,1) = Trim(aData(i,1))
		For j = LBound(aData,2)+1 To UBound(aData,2)
			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

[eobet]

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.)

[JohnSUN]

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.

[eobet]

Thank you very much, that was it!

[Lupp]

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

(As always: Interested in refutations.)

[eobet1]

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)