Issue Using Array Formulas

**apiekar42** · 03-07-2013, 01:36 PM

I hope I can explain my problem in a way that makes sense, so here goes:

I have a list of Text Strings that I have numerically ranked in another column based on a certain value that corresponds to this data. Let's call this sheet Tab 1:

Column A Column B Column C
Text String Value Rank
ABC 10 4
DEF 15 3
GHI 10 4
JKL 5 6
MNO 20 1
PQR 20 1

I simplified it here, but in reality the list comprises thousands of rows and I change the values monthly (ranks are formulaic and change based upon the values).

On a separate tab (tab 2), I want to create a table that shows the top 10 "text strings" (based on ranking) which updates automatically every month when I input the new values. I think I'm almost there, but I'm getting stuck on an issue. I'm using a small() function nested with an if() statement to pull the ranks into a column, let's call this column A in a new tab (tab 2). The formula looks something like:

{=small(if(('Tab 1'!$C$2:$C$7>='Tab 2'!A1)*not('Tab 1'!$A$2:$A$7=transpose('Tab 2'$B$1:B1)),'Tab 1'!$C$2:$C$7),1)}

I am dragging this formula down from 'Tab2'A1 to 'Tab2'A6. I need the formula to look into the output produced from this formula (in the rows above) and make sure it isn't listing the same text string more than once. This is why I included the not() statement in the formula. However, the transpose() function within the not() statement is messing everything up. The transpose function effectively increases the size of that array which doesnt match up with the other arrays within the if statement. In the end, I would want the output in the column to look like:

Tab2
Column A
1
1
3
4
4
6

Hope this made sense, let me know if it didn't. Thanks in advance!

**tigeravatar** · 03-07-2013, 01:57 PM

apiekar42,

Attached is an example workbook based on the criteria you described.
The workbook contains two worksheets, named 'Tab 1' and 'Tab 2'
Both sheets use row 1 as a header row, so actual data will start on row 2.
On sheet 'Tab 1', column A is the Text String, column B is the Value, and column C is the rank.
Sheet 'Tab 1' cell C2 and copied down uses this formula:

Please Login or Register  to view this content.

Then I set up two dynamic named ranges.
The first is listText which is defined by:

Please Login or Register  to view this content.

The second is listRank which is defined by:

Please Login or Register  to view this content.

Then on sheet 'Tab 2' cell A2 and copied down is this formula to get the Text Rank in ascending order:

Please Login or Register  to view this content.

Sheet 'Tab 2' cell B2 and copied down is this formula to get the corresponding text (adjusting so that no duplicate text is shown, even when there are duplicate ranks):

Please Login or Register  to view this content.

None of those are array formulas. Is that what you're looking for?

**ChemistB** · 03-07-2013, 01:59 PM

Here's a way without ARRAYS
first in blank column on Tab 1 (lets say starting in C2)
=(B2&COUNTIF($B$2:B2,B2))+0
copied down

Then on second sheet, Indexing the column you wish to bring over
=INDEX($B$2:$B$7,MATCH(SMALL($C$2:$C$7,ROW(A1)),$C$2:$C$7,0)) copied down
Does that work for you?