Formula to remove duplicate words
Hi all,
I am trying to write a formula to remove duplicates values but my formula keeps returning the value zero.
INDEX(Sheet2!$F$5:$F$195,MATCH(0,COUNTIF($A$1:A1,Sheet2!$F$2:$F$195),0))
I have a table of words on Sheet 1 in Cells F5 to about F100. I want my formula to return those values (minus the duplicates), & paste them in Column A in Sheet1. Could someone please advise where I am going wrong?
Thanks
Your INDEX range and COUNTIF range are different. Either change F5 to F2 or change F2 to F5.
Also, make sure you array enter the formula.
Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.
When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
unique.xlsx
Another way
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thank for your response Tony.
I changed the F2 to F5 but i am still getting all zeros, do you have any other idea on what I am doing wrong?
{=INDEX(Sheet2!$F$5:$F$195,MATCH(0,COUNTIF($A$1:A2,Sheet2!$F$5:$F$195),0))}
Here's a small sample file that demonstrates this:
ExtractUniques.xlsx
Data in the range Sheet2 F5:F18.
Uniques extracted to Sheet1 starting in cell A2 and downwards.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks