LinkBack URL
About LinkBacks
Hello,
I am trying to get an Array formula to pull unique values from an ENTIRE table. I was able to write one just for a column but not for an entire table. The unique value column is my desired results - thanks
Hello,
I am trying to get an Array formula to pull unique values from an ENTIRE table. I was able to write one just for a column but not for an entire table. The unique value column is my desired results - thanks
Hi there,
Do you have to do this via a formula? If not, you could use the 'Advanced Filter' option to pull out unique entries (or 'Remove Duplicates in you're using Excel 2007 onward). I'd copy and paste all the columns in a single column, then do the advanced filter.
If you don't know how to do this, just Google something along the lines of 'Excel 2003 remove duplicated advanced filter'. Here's one I found: http://www.wiseowl.co.uk/blog/s177/h..._excel_pt2.htm
HTH,
FermentedR
Say we have values from A1 thru C10.
In D1 enter:
=A1
In D2 enter the array formula:
=IFERROR(INDEX($A$1:$C$10,INT(SMALL(IF(COUNTIF(D$1:D1,$A$1:$C$10)=0,ROW($A$1:$C$10)+(COLUMN($A$1:$C$10)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(D$1:D1,$A$1:$C$10)=0,ROW($A$1:$C$10)+(COLUMN($A$1:$C$10)*0.01)),1),1)),"")
and copy down
Array formulas must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key
Gary's Student
See here how you can do it.
http://www.get-digital-help.com/2009...umns-in-excel/
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi Jakobshavn,
I used your formula but changed the columns to fit my actual spreadsheet but nothing happened. My table runs from AG1 to AO1000. I placed the array in column in AQ. The Array formula reads as below:
{=IFERROR(INDEX($AG$1:$AO$1000,INT(SMALL(IF(COUNTIF(AQ$1:AQ1,$AG$1:$AO$1000)=0,ROW($AG$1:$AO$1000)+(COLUMN($AG$1:$AO$1000)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(AQ$1:AQ1,$AG$1:$AO$1000)=0,ROW($AG$1:$AO$1000)+(COLUMN($AG$1:$AO$1000)*0.01)),1),1)),"")}
perplexed.....
Remember the BIG array formula does not go in the top cell, only the second cell on down.
Here is my source:
http://answers.microsoft.com/en-us/o...5-d8d385dcbb12
Here is an example:
Last edited by Jakobshavn; 03-11-2013 at 01:55 PM.
In your sample file cell A3 contains a space character. This makes the cell appear to be blank but it is not with that space character.
Since you're using Excel 2003...
Enter this array formula** in cell E1. This will return the count of uniques entries in the range.
=SUM(IF(A2:C6<>"",1/COUNTIF(A2:C6,A2:C6)))
Enter this array formula** in D2. This formula references cell D1 which contains the column header. Cell D1 must not contain an entry that is also in the range A2:C6.
=IF(ROWS(D$2:D2)>E$1,"",INDEX(A$2:C$6,MIN(IF(A$2:C$6<>"",IF(ISNA(MATCH(A$2:C$6,D$1:D1,0)),ROW(A$2:C$6)-ROW(A$2)+1))),MOD(MIN(IF(A$2:C$6<>"",IF(ISNA(MATCH(A$2:C$6,D$1:D1,0)),(ROW(A$2:C$6)-ROW(A$2)+1)*10^5+(COLUMN(A$2:C$6)-COLUMN(A$2)+1)))),10^5)))
** 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 down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
What version of Excel are you using?
In your "stat panel" it says Excel 2003.
The IFERROR function was introduced in Excel 2007 and is not available in Excel 2003. So, if you try to use that formula in Excel 2003 you'll get a #NAME? error.
Hi Tony,
I modified your formula to fit my spreadsheet but only get N/A. It is in Excel 2007.
You have too much data for this type of array formula. This array formula is calculation intensive and it takes "forever" for your file to calculate.
I closed the file after 15 mins of calculation time.
Maybe someone can help with a VBA procedure to do this.
Try the attached.
The formula in AP2 had an error, see the attached
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks