How to write a formula for conditional multiple returns to display in an ordered list in a single cell?

[DV88]

This is a bit tricky to describe, but here goes nothing. I’m trying to write a formula to display the contents of certain cells from a multi-column, multi-row range in an ordered, comma-separated list. It’s probably easier to illustrate this than describe it, really:

   	A	  B	  C

1	Name1	5	3
2	Name2	12	8
3	Name3	3	3
4	Name4	-2	-2
5	Name5	8	5
6	Name6	5	5
7	Name7	2	2
8	Name8	0	0
9	Name9	9	4

Ok, that’s basically what it’s like, except…bigger (7 columns by 30 rows, but only 3 columns are relevant for this).

What I’m trying to do is get the formula, which would output into a single cell, to scan those rows, and where the number in B and C doesn’t match up (B would always be higher than C in this case), display “A B” (including the space), and separate the entries with a comma and space, so that it would look like this: “Name1 5, Name2 12, Name5 8, Name9 4”.

I figured out how to do it very clumsily, with =IF(B1=C1,"",A1&" “&B1)&IF(SUM(B1:B$9)=SUM(C1:C$9),”",", ")) among others, but all of them have the problem of needing to add every row with addition &s (replacing the = at the front of each with & and sticking it on after). That would be fine, since the table size won’t ever change, but I’m running into error 512 (30 rows, remember - it adds up fast) and I’m almost certain there’s a better way to do this, a more elegant way, but I can’t figure it out…any help would be hugely appreciated!

[JohnSUN]

I think that without a macro - with standard functions of the application only - you don’t decide this problem. (I would be happy if I was wrong)

[tohuwawohu]

Do you really need to do every step in Calc? I suppose it would be much easier to break up the task in smaller sub-tasks. For example, filtering the rows where B > C is quite easy; adding another column for every row having name and B value is easy, too. Concatenating may be done in an extra step…

[tohuwawohu]

…, maybe outside calc (exporting the resulting table as csv and modifying it in writer or any text editor, or using unix tools like sed may be possible solutions). I agree with @JohnSUN that this is very hard to solve just using standard Calc functions.

[DV88]

@JohnSUN If I can do it with a macro that’d be fine, if it’s contained in the program and I can embed it into that file. I have no experience with macros, though - do you have a suggestion of where I should go to learn that?

[DV88]

@tohuwawohu It does need to be done in Calc, sadly - it’s part of a series of documents that all need to have the same format. If I absolutely have to do it in multiple steps I can, but I would really, really like to avoid that if at all possible and have it all go to the one cell.

[JohnSUN]

Try this solution - ConcatenateRange.ods

In case the file will be not available for download, cite the full text of user-defined function:

Option Explicit	' All variables are declared explicitly in terms of "Dim".
Option Base 0	' Each array indexing starts at zero

Function concRng(Optional sourceRng As Variant, Optional condRng As Variant, _
				Optional separatorCells As String, Optional separatorRows As String)  As String
Dim i&, j&, min&, max&, count&
Dim tn As String	' TypeName of input parameter
Dim sArr() As String	' a temporary array for intermediate results
Dim Arr() As String	' a temporary array - singl row from sourceRng
Const defaultRes = ""	' You can write custom message on the error parameters, e.g. "Wrong parm - "
Const defaultCellsSep = " "
Const defaultRowsSep = " "
REM First, process the last two parameters, which may not affect anything, 
REM but only need for decorating the result.
REM If they are missing set to their default values
	If IsMissing(separatorCells) Then separatorCells = defaultCellsSep
	If IsMissing(separatorRows) Then separatorRows = defaultRowsSep
	concRng = defaultRes
	If IsMissing(sourceRng) Then Exit Function	' No params - nothing to do
REM The first parameter is specified. What is this?
	If IsArray(sourceRng) Then	' Good! This is what we expected! The remaining variants - boring mistakes
		min = LBound(sourceRng,1)
		max = UBound(sourceRng,1)
REM What is it second parameter?
		If IsMissing(condRng) Then ' No param - just join in a single line all the values ​​of the first parameter
			ReDim sArr(min To max)
			For i = min To max
				ReDim Arr(LBound(sourceRng,2) To UBound(sourceRng,2))
				For j = LBound(sourceRng,2) To UBound(sourceRng,2)
					Arr(j) = CStr(sourceRng(i, j))	' Convert any value to string
				Next j
				sArr(i) = Join(Arr, separatorCells)	' Concatenate it
			Next i
			concRng = Join(sArr, separatorRows)	' Concatenate result
		Else	' condRng present
			If IsArray(condRng) Then	' OK - this is an array. What it is the dimension?
				If UBound(condRng,1) < max Then max = UBound(condRng,1)
				ReDim sArr(min To max)
				count = min-1
				For i = min To max
REM Doing the same thing, but only if the condition is TRUE
					If condRng(i,LBound(condRng,2)) Then
						count = count+1
						ReDim Arr(LBound(sourceRng,2) To UBound(sourceRng,2))
						For j = LBound(sourceRng,2) To UBound(sourceRng,2)
							Arr(j) = CStr(sourceRng(i, j))
						Next j
						sArr(count) = Join(Arr, separatorCells)
					EndIf
				Next i
				If count > 0 Then
					ReDim Preserve sArr(min To count)
					concRng = Join(sArr, separatorRows)
				Else
					concRng = defaultRes
				EndIf
			Else	' condRng not array. You can write your own variant of code for this case. I just fixed the error
				concRng = "Enter this formula as an array function - Ctrl+Shift+Enter"
			EndIf
		EndIf
	Else	' Not array, singl value? And what to do now?
		concRng = defaultRes + CStr(sourceRng)
	EndIf
End Function

[DV88]

I downloaded the sample file you have there, but all the examples are showing as #VALUE! - I’m not sure if I need to change some settings or something. << (EDIT: I figured it out. Thank you -so- much, this is perfect! ^^ It won’t let me upvote you, but you have my deepest thanks!)

[vojo]

so I got to ask. Wouldnt it be more useful to have column D with the formula of

if (B>C, A&B," ")

Seems to me to be this is much more useful than a single cell with say 13 pieces of text that somebody has to scan thru (let alone cell width and such). Lets face it, all that text in a single cell is basically saying “buried treasure, good luck finding it” to the user.

Might even be able to use condititional formatting in that column D is red if true, green if not.

The final single cell could be a global “good / bad” kind of thing to tell user whether to look at the D column or not. I.e. if this is some sort of report, single cell is a global statement and column D would be the specific rows with a problem.

[DV88]

In most cases it would be, but in this particular instance no, sadly. JohnSUN’s macro works perfectly though.