hi all,
very simple i am sure but i just don't know the syntax.
attached is a spreadsheet example; on the left hand side is a random list of names, i need to put these names in to a unique list, but i can not use a pivot table, as i need it to auto refresh and work with some other formulas.
i dont need it to count, i literally need it to give me a list of unique names, as per the bottom right of the spreadsheet.
any help would be appreciated.
Try this, copied down...
=IFERROR(INDEX($A$6:$A$35,MATCH(0,INDEX(COUNTIF($C$4:C4,$A$6:$A$35),0,0),0)),"")
However, this will not sort for you, so if you really need it sorted, sort A 1st
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
copy past the data.
Data => remove duplicated
Data => sort A - Z
will give the result.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I need it automated, don't want human interaction involved at all.Originally Posted by oeldere
Thank you FDibbins, however this only returned the first item in the list and none of the others.
Hi.
Put this formula in e.g. F20:
=SUMPRODUCT(1/COUNTIF(A6:A35,A6:A35))
This counts the expected number of returns and so, by referencing this value in the main set of formulas, we ensure that we control what is returned. What's more, it gives a far more efficient and less resource-heavy solution than the equivalent IFERROR set-up.
Then enter this array formula** in E20:
=IF(ROWS($1:1)>$F$20,"",INDEX($A$6:$A$35,MATCH(SMALL(IF(FREQUENCY(MATCH($A$6:$A$35,$A$6:$A$35,0),ROW($A$6:$A$35)-MIN(ROW($A$6:$A$35))+1),COUNTIF($A$6:$A$35,"<"&$A$6:$A$35)),ROWS($1:1)),COUNTIF($A$6:$A$35,"<"&$A$6:$A$35),0)))
Copy this formula down (though not the one in F20, which is a one-off) until you start to get blanks for the results.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
EXCELLENT THANK YOU, however one problem, it stops if the cell is blank on the first formula, is their a way around this, as i can not populate the blanks in anyway?
Otherwise it works correctly, thank you.
Sorry - not sure what you mean? If which cell is blank? Do you mean your original list can potentially contain one or more blank cells?
Regards
Perhaps you didnt copy it in/down correctly?
DRIVER NAME ORANGE ORANGE APPLE APPLE PEAR PEAR GRAPES GRAPES MELON MELON BANNANA BANNANA KIWI KIWI STRAWBERRY STRAWBERRY GRAPEFRUIT GRAPEFRUIT KIWI KIWI GRAPEFRUIT GRAPEFRUIT
C6=IFERROR(INDEX($A$6:$A$35,MATCH(0,INDEX(COUNTIF($C$5:C5,$A$6:$A$35),0,0),0)),"")
yes my original list contains blanks. so the first formula you use for some reason ends up giving me a div error:
STRAWBERRY #DIV/0!
ORANGE #DIV/0!
GRAPES #DIV/0!
MELON #DIV/0!
STRAWBERRY #DIV/0!
ORANGE #DIV/0!
#DIV/0!
GRAPES #DIV/0!
GRAPEFRUIT #DIV/0!
MELON
STRAWBERRY
MELON
STRAWBERRY
ORANGE
as apposed to:
STRAWBERRY 9
ORANGE APPLE
GRAPES BANNANA
MELON GRAPEFRUIT
STRAWBERRY GRAPES
ORANGE KIWI
GRAPEFRUIT MELON
GRAPES ORANGE
GRAPEFRUIT PEAR
MELON
STRAWBERRY
MELON
STRAWBERRY
ORANGE
xor lx,
yes my original list contains blanks. so the first formula you use for some reason ends up giving me a div error:
STRAWBERRY #DIV/0!
ORANGE #DIV/0!
GRAPES #DIV/0!
MELON #DIV/0!
STRAWBERRY #DIV/0!
ORANGE #DIV/0!
#DIV/0!
GRAPES #DIV/0!
GRAPEFRUIT #DIV/0!
MELON
STRAWBERRY
MELON
STRAWBERRY
ORANGE
as apposed to:
STRAWBERRY 9
ORANGE APPLE
GRAPES BANNANA
MELON GRAPEFRUIT
STRAWBERRY GRAPES
ORANGE KIWI
GRAPEFRUIT MELON
GRAPES ORANGE
GRAPEFRUIT PEAR
MELON
STRAWBERRY
MELON
STRAWBERRY
ORANGE
It's not "for some reason": your example included no such cells, so I did not account for that possibility. I can only work with what you give me.
Change the formula in F20 to:
=SUMPRODUCT((A6:A35<>"")/COUNTIF(A6:A35,A6:A35&""))
And that in E20 to:
=IF(ROWS($1:1)>$F$20,"",INDEX($A$6:$A$35,MATCH(SMALL(IF(FREQUENCY(IF($A$6:$A$35<>"",MATCH($A$6:$A$35,$A$6:$A$35,0)),ROW($A$6:$A$35)-MIN(ROW($A$6:$A$35))+1),COUNTIF($A$6:$A$35,"<"&$A$6:$A$35)),ROWS($1:1)),COUNTIF($A$6:$A$35,"<"&$A$6:$A$35),0)))
Regards
Reply to #4.
I need it automated, don't want human interaction involved at all.
Record the manual actions with the macro-recorder and you have your VBA code.
fdippins, yours is working well now, thank you.
is their a way to use your method, that then auto sorts alphabetically as well?
hmm ok try this, from 1 of the other members...
=IFERROR(INDEX($A$6:$A$35,MATCH(0,COUNTIF($A$6:$A$35,"<"&$A$6:$A$35)-SUM(COUNTIF($A$6:$A$35,D$6:D6)),0)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
thank you all, much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks