I am trying to create an array of unique types/colours from data which contains numerous duplicates. I have tried pivot tables but that didn't work so I am resorting to formula. I am trying to avoid VBA. Any assistance is much appreciated. I have uploaded a sample file.
Put in F8 and array ENTERED and copied down and cross
=IFERROR(INDEX($B$8:$B$39,AGGREGATE(15,6,IF(FREQUENCY(IFERROR(MATCH(IF($A$8:$A$39=F$7,$B$8:$B$39),$B$8:$B$39,0),""),ROW($A$8:$A$39)-ROW($A$8)+1),ROW($A$8:$A$39)-ROW($A$8)+1),ROWS($A$1:A1))),"")
Try this...
This array formula** entered in F8:
=IFERROR(INDEX($B$8:$B$39,MATCH(0,IF($A$8:$A$39=F$7,COUNTIF(F$7:F7,$B$8:$B$39)),0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy across to K8 then down until you get a row full of blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Azumi
I can sort of understand most of it but how does the "Aggregate(15,6if(Frequency" part work
Hi Bob,
See the attached where I believe in shorter steps. *First you should do an Advanced Filter with your data to only leave unique data. Then create a Counter column and to make it easy, a helper column. Then create your matrix with a simple index match formula. See the attached.
Index Match with help for Bob.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Here's your file with the formula implemented.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks