First time here? Check out the FAQ!
asked 2012-07-20 07:01:26 +0200DV88
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!
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
answered 2012-07-22 06:53:55 +0200vojo
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.
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-07-20 07:01:26 +0200
Seen: 392 times
Last updated: Aug 02 '12