Complicated array formula

richardjshaffer

Board Regular
Joined
Oct 9, 2008
Messages
84
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
{=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!!
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top