# Complicated array formula

#### richardjshaffer

##### Board Regular
Hi,

hope someone can help, I'm really frustrated.

I found this great array formula seaching online that gathers all the unique items in a range A1:A10.

I wish I understood what's happening, can anyone explain this in layman terms?

{=INDEX(\$A\$1:\$A\$10,SMALL(IF(MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0)=ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10))),MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0),""),ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10)))))}

Even just to understand the last bit ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10) would be great - isn't the row of the indirectly chosen range impossible, it's range not a cell?

many thanks,

Richard

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
This:

ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10)

creates an array like {1,2,3,4,5,6,7,8,9,10}

{=INDEX(\$A\$1:\$A\$10,SMALL(IF(MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0)=ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10))),MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0),""),ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10)))))}
Here's how it works:

1. MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0) creates a virtual array the same size as the \$A\$1:\$A\$10 array, listing for each entry on the list the array-row number on which it FIRST appears. What I mean by "virtual array" is an array that exists only in Excel's memory, never appearing on the spreadsheet anywhere, but which Excel can search and manipulate it as if it did. "Array-row number" means that it's NOT neccasarily the spreadsheet row number, although in this example the first row of the array IS the first row of the spreadsheet. If your data lived in cells \$A\$206:\$A\$215, then array-row #1 would be spreadsheet row #206.

2. ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10))) creates another virtual array, again the same size as the \$A\$1:\$A\$10 array, containing the array-row numbers, starting with 1 and ending with however many rows are in the data array. As Andrew stated below, in your example this array contains the numbers 1 through 10.

Repeating the formula with new color coding:

{=INDEX(\$A\$1:\$A\$10,SMALL(IF(MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0)=ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10))),MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0),""),ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10)))))}

3. IF(MATCH.........=ROW................,MATCH(\$A\$1:\$A\$10,\$A\$1:\$A\$10,0),"") creates a third virtual array. It compares the first virtual array to the second virtual array, line by line. For each line where the two arrays contain the same number, it puts that number in that line of the third virtual array, with all the non-matched lines getting a blank ""

4. INDEX(\$A\$1:\$A\$10,SMALL(IF(............),ROW(INDIRECT("1:"&ROWS(\$A\$1:\$A\$10)))))looks through the third virtual array, grabbing the smallest array-row number first and indexing through the \$A\$1:\$A\$10 array to that position and returning the data in that cell.

Hope this helped!!

Here's another way, which should be more efficient. Assuming that A2:A10 contains the data, try...

Code:
``````C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))``````

Code:
``````D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D\$2:D2)<=\$C\$2,INDEX(\$A\$2:\$A\$10,SMALL(IF(FREQUENCY(IF(\$A\$2:\$A\$10<>"",MATCH("~"&\$A\$2:\$A\$10,\$A\$2:\$A\$10&"",0)),ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1),ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1),ROWS(D\$2:D2))),"")``````

Hope this helps!

many thanks all, think I've got it now.

so it produces this third array, eg 1,2,3,4,5,blank,7 - the blank arises where a repeated item is found...

then the indexing looks for the 1st, 2nd, 3rd, 4th, 5th and then 6th smallest values, so this 6th smallest skips the blank and returns the item in position 7.

On the alternative solution, couldn't make this work - the first formula is copied down, should all the references change or should a2 and a10 be fixed? Bit confused how to make it work?

Richard,

If you're open to alternative solutions, maybe non-formula might be OK? Such as a query table or, if using VBA, Excel's built in advanced filter or ADO+SQL or dictionary objects, etc.

Regards, Fazza

On the alternative solution, couldn't make this work - the first formula is copied down, should all the references change or should a2 and a10 be fixed? Bit confused how to make it work?

The first formula is entered in C2 and confirmed with CONTROL+SHIFT+ENTER. It's not copied down. It simply returns a count of unique values.

The second formula is entered in D2, confirmed with CONTROL+SHIFT+ENTER, and copied down. It returns a list of unique values.

thanks everyone, this other technique works now.

Replies
4
Views
523
Replies
23
Views
943
Replies
13
Views
459
Replies
0
Views
226
Replies
1
Views
103

1,196,056
Messages
6,013,156
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back