Ask Your Question

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

asked 2012-07-20 07:01:26 +0200

DV88 gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 04:01:14.736984



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)

JohnSUN gravatar imageJohnSUN ( 2012-07-20 13:59:21 +0200 )edit

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 gravatar imagetohuwawohu ( 2012-07-20 14:35:31 +0200 )edit

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

tohuwawohu gravatar imagetohuwawohu ( 2012-07-20 14:38:03 +0200 )edit

@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 gravatar imageDV88 ( 2012-07-21 00:57:09 +0200 )edit

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

DV88 gravatar imageDV88 ( 2012-07-21 00:59:12 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2012-07-21 18:51:31 +0200

JohnSUN gravatar image

updated 2012-08-02 08:23:18 +0200

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)
                Next i
                If count > 0 Then
                    ReDim Preserve sArr(min To count)
                    concRng = Join(sArr, separatorRows)
                    concRng = defaultRes
            Else    ' condRng ...
edit flag offensive delete link more


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

DV88 gravatar imageDV88 ( 2012-07-22 04:12:54 +0200 )edit

answered 2012-07-22 06:53:55 +0200

vojo gravatar image

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.

edit flag offensive delete link more


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

DV88 gravatar imageDV88 ( 2012-07-24 06:19:57 +0200 )edit

Question Tools

1 follower


Asked: 2012-07-20 07:01:26 +0200

Seen: 3,215 times

Last updated: Aug 02 '12