View Single Post
 
Old 05-09-2014, 12:39 AM
Catalin.B Catalin.B is offline Windows Vista Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Nice
Instead of posting an image of the error, you can describe it in english, just the word "error" is not enough, details matter, like: "Formula is too long", (i guess you can translate such a message in english )

2. If you type ROW($A$2:$A$12) it will return wrong results, offseted with 1 row, and you have to apply a correction: ROW($A$2:$A$12)-1

3. To return a list of unique entries, you can use another array formula, in J2:
=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($B$2:$B$ 12,"<"&$B$2:$B$12)-SUM(COUNTIF($B$2:$B$12,$J$1:J1)),0)),"")
As you can see, only 1 column from those 3 is used to generate this list, if you want a list from all these 3 columns, it gets even more complicated.
4. To create a new list on the side, you have to consider the second argument of SMALL function: "k". If k=1, SMALL will return the smallest value from this array: {1;"";3;"";5;"";7;"";9;10;11} (these are the array results of rows found for "Red"). If k=2, it will return the second smallest value, (3 in our example)
To indicate the k value, i used COLUMN()-1 in the formula. The formula starts from column B, COLUMN() will return the column B number (which is 2). Substracting 1 from that column number , will result 1 (so, in column B SMALL function will return the smallest value from an array of row numbers), this is the way to create a dinamic argument.
If you want to start the formula from column M, this is column 13, so you have to substract 12 from column number to show the first match:
Change COLUMN()-1 to COLUMN()-12 to start from column M.
You will also need to change this:
From: FIND($A17 to FIND($L2 (according to your example)

L.E.: An error may occur because i used the ";" separator in formula, instead of "," if you tried to paste the formula from my first message and you have a comma separator in your regional settings...make the replacements..
Catalin
Reply With Quote