LinkBack URL
About LinkBacks
I use the following formula in Google Sheets to find unique values from a column. How could I do the same for a range like A:D?
=ARRAYFORMULA(IFERROR(INDEX($C$2:$C, MATCH(0,IF(ISBLANK($C$2:$C),1,COUNTIF($B$1:B1, $C$2:$C)), 0)),""))
I use the following formula in Google Sheets to find unique values from a column. How could I do the same for a range like A:D?
=ARRAYFORMULA(IFERROR(INDEX($C$2:$C, MATCH(0,IF(ISBLANK($C$2:$C),1,COUNTIF($B$1:B1, $C$2:$C)), 0)),""))
Not sure this would work on an array like that, but you could replace that array formula with this regular formula...
=IFERROR(INDEX($C$2:$C$60,MATCH(0,INDEX(COUNTIF($B$1:B1,$C$2:$C$60),0,0),0)),"")
Adjust ranges as needed
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
not sure about Google sheets, but here's one way in Excel...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks. It works in Google Sheets. Unfortunately my list will be ever expanding and needing that J column would be limiting.Originally Posted by Glenn Kennedy
Correct me if I"m wrong but it seems like this only working when the range is confined to a single column. If I expanded it as follows it didn't work for me.
=IFERROR(INDEX($C$2:$E$10,MATCH(0,INDEX(COUNTIF($B$1:B1,$C$2:$E$10),0,0),0)),"")
Example: https://docs.google.com/a/advantagep...it?usp=sharing
Well, I found this formula at http://www.extendoffice.com/document...e-columns.html
It works over a multi column range but when I try and range the range from $A$2:$C$9 to $A$2:$C$10 it gives me an error
Here's the link (different from the one above): https://docs.google.com/spreadsheets...it?usp=sharing
Hi.
The solution I give here will work for any number of columns, not just 4, though it also returns an alphabetical list, which may not be what you require.
http://excelxor.com/2014/11/08/uniqu...veral-columns/
Regards
Edit: in fact, if your data is purely numerical (you don't say), this solution can be made even more succinct.
Regards
ha... That's maybe 'cos I forgot to delete three un-needed columns. Take a second look at this effort!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks