Hi Friends,
I have column D (in Sheet 1) with aprox 1000 records i.e. alphanumeric character. In Sheet 2 , I just need to pull the unique value from column D (in Sheet 1). I know, I can do it by going to DATA tab and then selecting 'Remove Duplicate'. But then this is a manual process.
Is there a formula or function by which the unique values directly show up in Sheet 2.
Thanks,
Rahul
Make sure the range on Sheet1 starts at row 2, if not add a row.
in Sheet2!D2
=IFERROR(INDEX(Sheet1!$D$2:$D$100, MATCH(0, COUNTIF(Sheet2!$D$1:D1,Sheet1!$D$2:$D$100), 0)),"")
Array formula, use Ctrl-Shift-Enter
There's prob a non array way of doing this.
Last edited by Special-K; 03-20-2019 at 11:34 AM.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
How aboutFormula:
Please Login or Register to view this content.
The manual process will probably be quicker than the formula.
Have you considered a pivottable, using column D for row lables?
Enter in Sheet2 A2, Array confirm then fill down
Formula:Please Login or Register to view this content.
...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.
You can use a fairly basic Advanced Filter to do that.
Select the range, click on Advanced filter, select somewhere to copy to, and check "unique records only"
The only disadvantage is that you have to ouput the unique list on the same sheet. Very quick though and you could copy and paste it elsewhere.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks