![]() |
#7
|
|||
|
|||
![]()
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 |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
citizenzen | Word | 1 | 01-17-2013 11:43 AM |
![]() |
Fantasytrader | Word VBA | 1 | 03-14-2012 06:30 PM |
Button click event from a noob | jsampson | Outlook | 0 | 10-12-2010 01:48 PM |
Noob questions | punktilend | Word | 0 | 07-24-2009 02:41 AM |
A Noob Question | Techknology | Outlook | 0 | 11-30-2006 07:47 AM |