1

How do I convert a string from a cell to a text array inside of a function, that should use the array, without using VBA and without adding the array into any other part of the document? It will be one of these arrays on more than 1000 rows. The string format is ^[a-zA-Z0-9.,-]*$ with "," as delimiter.

This is the functionality I would like to achieve

I have an excel table with the following columns
A: ID numbers to compare, separated by comma (delimiter can be changed if needed). About 100 ID's would be good to support at least.
B: ID (Each value on the rows in the column are unique but not sorted and can't be sorted because sorting is needed based on other criterias)
C: Value (Several rows in the column can have the same value)
D: Output the one ID of the comma separated ID's that has the highest value on its row

The problem part of the output
So far I have made a function which find the correct ID in column B based on the values in column C but only if I enter the string from column A as an array constant manually within the function. I have not managed to get the function to create the array itself from column A, which is required.

Working part of the code

In this code I have entered the values from column A manually and this is working as it should.

=INDEX({"1.01-1","1.01-3","1.08-1","1.01-1-1A"},MATCH(MAX(INDEX(C$10:C$20,N(IF(1,MATCH({"1.01-1","1.01-3","1.08-1","1.01-1-1A"},B$10:B$20,0))))),INDEX(C$10:C$20,N(IF(1,MATCH({"1.01-1","1.01-3","1.08-1","1.01-1-1A"},B$10:B$20,0)))),0))

Note that the start row is not the first row and the array is used 3 times in the function.

Code to try to convert the string to a text array

Not working but if wrapped in SUMPRODUCT() it provide an array to the SUMPRODUCT() function, of course not usable since I then can't pass on the array. The background to this code can be found in question Split a string (cell) in Excel without VBA (e.g. for array formula)!.

=TRIM(MID(SUBSTITUTE(A10,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A10)-LEN(SUBSTITUTE(A10,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A10)-LEN(SUBSTITUTE(A10,",",""))+1)))=1),99))

The second code output the first item of the array and inserted in the first code do not change this result as it did when wrapping the second code in SUMPRODUCT().

Here is a picture of my simplified test setup in Excel for this case, identical to what is described above.

Simplified test setup

Matthias Tidlund
  • 168
  • 10
  • 2
    Edit your question to show an example of the data and layout you are trying to convert to an array. Is it just a few comma separated values as you show in your screenshot in `A10`, or is it more involved than that? – Ron Rosenfeld Jan 16 '19 at 15:41
  • @RonRosenfeld String format added and picture updated with expected output for each row instead of only the first. The number of comma separated values can be anything between 0-99 and will be different from row to row. – Matthias Tidlund Jan 16 '19 at 15:58
  • Well, I have no tool to transfer a screenshot easily to my excel program, so that's not terribly useful. But my answer provides you a method of converting what appears to be the contents of A10 into a array of strings that you should be able to adapt to whatever it is that you are doing. – Ron Rosenfeld Jan 16 '19 at 16:02
  • @RonRosenfeld Your answer solved it but to describe the purpose; it will be used in a larger set of data to track relations between different parameters. I tried to simplify the case as much as possible for this post, but it wasn't easy to make it understandable. – Matthias Tidlund Jan 16 '19 at 16:19

1 Answers1

2

I'm not really sure what you are doing with your formula. But to convert contents of a cell to a comma separated text array to be used as the array argument to the INDEX or MATCH functions, you can use the FILTERXML function. You'll need to educate yourself about XML and XPATH to understand what's going on, but there are plenty of web resource for this.

For example, with

A10:  "1.01-1","1.01-3","1.08-1","1.01-1-1A"

The formula below will return 1.08-1. Note the 3 for the row argument to the INDEX function.

=INDEX(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A10,"""",""), ",", "</s><s>") & "</s></t>", "//s"),3)
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • It worked as it should to insert the FILTERXML function and enter the function as an array function with CTRL+SHIFT+ENTER. The resulting function then became "{=IFERROR(INDEX(FILTERXML("" & SUBSTITUTE(SUBSTITUTE(A10,"""",""), ",", "") & "", "//s"),MATCH(MAX(INDEX(C$10:C$20,N(IF(1,MATCH(FILTERXML("" & SUBSTITUTE(SUBSTITUTE(A10,"""",""), ",", "") & "", "//s"),B$10:B$20,0))))),INDEX(C$10:C$20,N(IF(1,MATCH(FILTERXML("" & SUBSTITUTE(SUBSTITUTE(A10,"""",""), ",", "") & "", "//s"),B$10:B$20,0)))),0)),"")}" – Matthias Tidlund Jan 16 '19 at 16:15