Convert an Array Formula's Text Results into a

问题:

When the results of an Array Formula are numbers, I find it generally easy to find an appropriate method to collapse the array into a single result. However when the results of an Array Formula are text, I find it difficult to manipulate the formula in a way which provides a single desired result. In short, is there a method of manipulating an Array of text results which I have overlooked? See the bottom of this question for the final desired formula which doesn't work, and request for solutions.

*Edit - after reading through this again, I can alternately summarize my question as: is there a way to access multiple text elements from a 'Formula Array result', without individually selecting (eg: with INDEX)?

Examples where Array Formulas work, where the Result Array is number values

(1) Example 1: Assume column A rows 1-500 is a list of product ID's in the format of xyz123, and column B rows 1-500 shows total sales for that product. If I want to find the sales for the product with the highest sales, where the last 3 digits of an ID are above 400, I could use an Array Formula like so (confirmed with CTRL + SHIFT + ENTER instead of just ENTER):

=MAX(IF(VALUE(RIGHT(A1:A500,3))>400,B1:B500,""))

(2) Example 2 Now assume that column B contains product names, instead of Sales. I now want to simply return the first name which matches criteria of the last 3 digits of the product ID being > 400. This could be done as follows:

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

Here, I have done a little manipulation, so that the actual Array part of the formula [IF(RIGHT(A1:A500,3...] returns a value result [the ROWs of the cellsA1:A500 where the last 3 digits are above 400]; I can therefore use MIN to show only the first ROW # which matches, and then I can use that collapsed result in a regular INDEX function.

(3) Example 3 For a final example, see the discussion on a similar question here [Goes more in-depth than my summarized example below, in a way not directly relevant to this question]: https://stackoverflow.com/a/31325935/5090027

Assume now that you want a list of all product names, where the last 3 digits of the product ID >400. To my knowledge, this cannot really be done in a single Cell, it would have to be done by placing each individual result on a subsequent cell. The following formula could be placed, for example, in C1 and dragged down 10 rows, and would then show the first 10 product names with the product ID's having last 3 digits > 400.

=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))

Example where Array Formulas will not work, where the result array is text values

Now assume that I want to take the results in Example 3, and perform some text manipulation on them. For example, assume I want to concatenate them all into a single string of text. The below doesn't work, because concatenate won't take an array of results like this as acceptable arguments.

=CONCATENATE((IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($B$1:$B$500),"")))

So the question is: does anyone know how to get this last formula to work? Or, how to get a formula to work which takes an array of text results, and either converts it into a 'usable range' [so it can be plugged into Concatenate above], or can be manipulated with text arguments immediately [such as mid, search, substitute, etc.]? Right now the only method I can see would be using example 3 above, and then going further and saying, for example, Concatenate(C1,C2,C3...C10).

回答1:

As stated previously, there is no native function which can do what you want in a single cell. If you absolutely cannot use VBA, then you could use a helper column (can hide the column if preferred) and then have the cell where you want the result simply show the last cell of the helper column.

Example:

Produce Name   Type
Apple          Fruit
Broccoli       Vegetable
Carrot         Vegetable
Orange         Fruit

Say you want a single cell to show all Fruit results. You could use another column to host this formula. You will be hiding the column later, so let's use one out of the way, like column Z. We also want to easily be able to change what you're looking for, so we'll put the condition in cell D2. In cell Z2 and copied down, you would use this formula:

=IF(B2=$D$2,IF(Z1="",A2,Z1&", "&A2),IF(Z1="","",Z1))

That would result in the following:

Produce Name   Type              Search For   (other columns until you get to Z)      
Apple          Fruit             Fruit                                             Apple
Broccoli       Vegetable                                                           Apple
Carrot         Vegetable                                                           Apple
Orange         Fruit                                                               Apple, Orange

Then in wherever you want your result cell, we'll say D3, simply use this formula to get the last result from your helper column, and then hide the helper column.

=Z5

Which results in the following:

Produce Name   Type              Search For
Apple          Fruit             Fruit
Broccoli       Vegetable         Apple, Orange
Carrot         Vegetable
Orange         Fruit

You could use a dynamic named range instead of simply =Z5 to make sure you're always getting the last cell in your helper column so that your data can grow or shrink and you'll still get the correct result. But now you can change the contents of cell D2 from Fruit to be Vegetable and now the result cell will show Broccoli, Carrot. Hopefully something like this can be adapted to your needs.

回答2:

To reiterate other responses, I did not find a way to use the concatenate function on an array. However, I did find a way to concatenate the "product names" using only one array function and no so-called "helper column." Although it is rather long and tedious, I think this may add to the discussion. For one, if you are actually going to use a formula like this for some valid purpose or to overcome a specific barrier, it can be easily used via copying and pasting of the formula (that is, it is actually relatively adaptable). On the other hand, if your interest is more a curiosity, my answer may be more banal than you might like.

In my simulation of your problem, I also had two columns, but shortened the row count to 40. The leftmost column ("C") contains sequences of three letters and three numbers, while the right column ("D") contains random sequences of letters and numbers that simulate your "product names."

I used a combination of nested replace and concatenate functions. The function below is chopped to focus on the "base unit" of the agglomerated function.

Base Unit

REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)))=TRUE,””,

The above formula essentially looks at the first product name with a corresponding product ID with numerical sequence > 400, then replaces it with a concatenation, given that there exists another product meeting the same product ID criteria. This can be thought of as a "accumulating" concatenation, starting at the innermost parentheses. This "base unit" of the formula can be repeated to an arbitrary extent. That is, if you believe that there are anywhere from 200 to 280 products in the list meeting the product ID criteria you set, you can repeat this base code 280 times. As you see, if the formula attempts to concatenate product names that do not exist (you have 280 formula base units and only 275 products meeting the criteria), the formula self-terminates...in a sense. It actually begins to concatenate nothing over and over again until all base units are enacted. The result will be all desired product names concatenated in one cell, with a period separating each one.

Only one number changes from base-block to base-block, and that is the kth element of the SMALL array. These variables will obviously step by one in each base unit. For my test, I used 14 base units.

Complete Formula with 14 Base Units

=REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),1)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),2)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),3)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),4)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),5)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),6)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),7)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),8)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),9)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),10)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),11)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)))=TRUE,””,**REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),12)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)))=TRUE,””,REPLACE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)),1,LEN(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13))),CONCATENATE(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),13)),".",IF(ISERR(INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),14)))=TRUE,””,INDEX($D$1:$D$40,SMALL(IF(VALUE(RIGHT($C$1:$C$40,3))>400,ROW($D$1:$D$40),""),14)))))))))))))))))))))))))))))))))))))))))

Obviously, if you look at the entire formula, it is pretty indecipherable. But, looking at it in terms of base units, you may see how it can be easily constructed then copied and pasted (after writing the initial base unit, it took about 2 minutes to put it all together).

回答3:

This is a VBA-free solution using Get&Transform in Excel 2016 or the Power Query Add-In for versions before:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractLast3Digits = Table.AddColumn(Source, "Value", each Text.End([ProductID],3)),
    ChangeToNumber = Table.TransformColumnTypes(ExtractLast3Digits,{{"Value", type number}}),
    FilterAbove400 = Table.SelectRows(ChangeToNumber, each [Value] > 400),
    Concatenate = Text.Combine(FilterAbove400[ProductName])
in
    Concatenate

You can perform all sorts of text manipulation on the “array-output” (Step “FilterAbove400”), in this example I’ve just concatenated without separators as I understood your request.

It takes your input data that should be in table-form and named “Table1” in the 1st step (Source).

Link to file with solution: https://www.dropbox.com/s/utsraj0bec5ewqk/SE_ConvertArrayFormulasTextResult.xlsx?dl=0

回答4:

You can create your own aggregate function to handle the results of a formula array. It does require a little VBA... but it's not difficult. This will allow you to do all kinds of string manipulation or numerical analysis on arrays of values.

To do your concatenation function, open up a VBA code window and create a new module by right clicking on the project -> insert -> new module. Double click the new module and insert this code to create the function that will concatenate an array into one large string:

Function ConcatenateArray(ParamArray Nums() As Variant) As Variant
Dim BigString As String
Dim N As Long
Dim A() As Variant
Let A = Nums(0)

BigString = ""
For N = LBound(A) To UBound(A)
    BigString = BigString & A(N, 1)
Next
ConcatenateArray = BigString

End Function

Then change your array formula in the cell to:

=ConcatenateArray(IF(VALUE(RIGHT($A$1:$A$500,3))>400,$A$1:$A$500,""))

Of course you have to hit CTRL + SHIFT + ENTER instead of just ENTER to confirm the cell as an array formula.

回答5:

I would try to address the several question raised in this post:

how to get a formula to work which takes an array of text results, and either converts it into a 'usable range' [so it can be plugged into Concatenate above],

Even if the first part of this question is feasible, the last part (i.e. "[so it can be plugged into Concatenate above]" is not possible as the CONCATENATE function does not take ranges as argument.

or can be manipulated with text arguments immediately [such as mid, search, substitute, etc.]? Right now the only method I can see would be using example 3 above, and then going further and saying, for example, Concatenate(C1,C2,C3...C10).

That’s certainly one method, but please give a try to this:

Let's start from this:

Now assume that I want to take the results in Example 3, and perform some text manipulation on them. For example, assume I want to concatenate them all into a single string of text.

But first let’s assume the following:

-. Data range is located at D10:F510 and includes fields: Product, Product, Sales and Product Name (Selection)*

*used to list results from formula in example 3

.- Data contains 23 records complying with the criteria defined in example 1 (see Fig. 1)

.- Value 400 is enter in cell E4 to ease modifications to the criteria instead of hard code in the formulas (see Fig. 3).

Fig. 1

Now, in order to generate an Array with the concatenated results and to post it a usable range, let’s apply a minor modification to the formula in example 3. Enter this FormulaArray in G11 and copy till last record (not just 10 lines)

=TRIM(CONCATENATE(
IF(ROW(G11)-ROW(G$11)+1=1,"",G10)," ",
IFERROR(INDEX($E$11:$E$510,
SMALL(IF(VALUE(RIGHT($D$11:$D$510,3))>$E$4,ROW($D$11:$D$510)-ROW($D$11)+1,""),
ROW(G11)-ROW(G$11)+1)),"")))

Fig. 2

The in the Summary section located at D4:E8 we have the results from examples 1 & 2 and the Concatenated results with the list of selected products (see Fig. 3). Enter this formula in E8 (suggest to increase the row height to the max of 409 and Wrap Text to true)

=INDEX($M$11:$M$510,1+MAX(ROW($M$11:$M$510))-ROW($D$11))

Fig. 3

As regards this question:

Is there a way to access multiple text elements from a 'Formula Array result', without individually selecting (eg: with INDEX)?

On this particular case (i.e. concatenation of array elements) I would apply a different perspective and generate the array with concatenated results then to pick the needed element, even if the use of INDEX is required.

Last I would like to make a minor note about these formulas:

Example 2:

=INDEX(B1:B500,MIN(IF(VALUE(RIGHT(A1:A500,3))>400,ROW(A1:A500),"")))

If the data range does not start at Row 1 use this formula instead:

=INDEX($E$11:$E$510,MIN(IF(VALUE(RIGHT($D$11:$D$510,3))>400,
1+ROW($D$11:$D$510)-ROW($D$11),"")))

Example 3:

=INDEX($B$1:$B$500,SMALL(IF(VALUE(RIGHT($A$1:$A$500,3))>400,ROW($A$1:$A$500),""),ROW()))

If the data range does not start at Row 1 use this formula instead:

=IFERROR(INDEX($E$11:$E$510,
SMALL(IF(VALUE(RIGHT($D$11:$D$510,3))>$E$4,
1+ROW($D$11:$D$510)-ROW($D$11),""),
1+ROW()-ROW($K$11))),"")