Hi guys,

I have a list of text values that I need to copy across to another worksheet, however I only want one of each value to be copied. I have created a new column besides the original with only the unique values. I need to move this list to another worksheet, ignoring the blank cells so they all line up below each cell. I have tried the IF formula however it needs an answer false, thus giving me a blank cell on several occasions which I do not want. I realise I can use filters and manually copy the data, but I need to use a formula as the data is going to greatly increase over time! Any ideas, please?

• Moved by Ehren - MSFTMicrosoft employee Thursday, July 09, 2015 6:16 PM This question pertains to Excel only, not Power Query.

Are you really using Power Query here because you could create another query that accesses the same text data and does a "remove duplicates" on that column and it would import it the way you want it. Or, does this have nothing to do with Power Query and you are completely within Excel?

It is completely withing Excel, I have never used power query. 

• Edited by PleaseHelp2015 Tuesday, July 07, 2015 2:35 PM

Hi,

As far as I know, we can use several formulas to create a List of Unique Records in Excel.

For example:

=IF(COUNTIF($B$2:B3,B3)=1,A2+1,A2)

OR =IFERROR(VLOOKUP(ROW()-1,$A$2:$B$16,2,FALSE),"")

For more detail information, please refer to the following link:

http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=97:remove-duplicates-or-create-a-list-of-unique-records-using-excel-formula&catid=77&Itemid=473

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Regards,

George Zhao
TechNet Community Support