calculation creating unique email address

[Helen15]

Can anyone help me with a formula please. I have about 8 columns of email address for an individual (some are blank  and some are the same email) and want to create a formula in the last column to create a unique list of those email address and leave the blank ones out for that individual. I have about 5,000 rows and so need a formulae to do this. Can anyone help please. Many thanks

[#2]

Hi@Helen15 

You can use =Textjoin

=TEXTJOIN("; ",1,B2:I2)

 the first part "; " is simply a delimiter.

the 1 is set to ignore blank cells

while B2:I2 is the range where your email address are found.

[#3]

@Helen15 

Forgot to select only the unique email address.

Try this instead:

=TEXTJOIN("; ",1,UNIQUE(B2:I2,1,0))

[#4]

The first one works a treat but for some reason when I put unique in the formula it doesn't work for me, saying #name? . Any ideas?
Thank you every so much in replying ☺️

[#5]

@Helen15 

Can you copy your formula with "unique" here so I can see what's going on?

[#6]

@Helen15 

Please check this https://techcommunity.microsoft.com/t5/excel/removing-duplicates-when-using-textjoin/m-p/188950 thread. If you have no UNIQUE() other variants of formula are here.

[#7]

@Bennadeau I have attached the file as its easier to explain i think. I also watched the video and tried to copy the formula but it's not removing the duplicates. Any help would be appreciated. My excel doesn't allow the  "unique" formula. Think its a version thing. Thanks so much again.

[#8]

@Sergei Baklan I have attached the file as its easier to explain i think. I also watched the video and tried to copy the formula but it's not removing the duplicates. Any help would be appreciated. 

Thanks so much again.

[#9]

@Helen15 

Back at you ...

Look at cell B6. Let me know if you need any changes.

[#10]

Also, update your version of Office. If you have 365, the latest update should allow you to use Unique function

[#11]

When i open it up, I just come up with "#NAME?" in cell B6 ?

[#12]

I have office 2019 if that helps?

[#13]

just google and i found this: "Note: The UNIQUE function is part of the new Excel Dynamic Arrays family and at the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. Excel 2019 will not have the Dynamic Array functions." really is not English to me.

[#14]

@Helen15 

You used mixed of ranges (a6:j6 and b6:j6) in the formula. If use the same it works

[#15]

I'm even more confused, when I open up your file it does only show unique email address but at the top of excel it says " Protected view: Be careful - files from the internet can contain viruses. Unless you need to edit, it's safer to stay in protected view" then there is a button to press which says "Enable Editing". As i need to enable editing i click on the button and then the formula doesn't work as they go back to a list and not unique email address. Is there some sort of excel option that i need to tick to let this formula work? Many thanks in advance.

[#16]

@Helen15 

By default every file downloaded from the cloud is opened as Read Only, thus you see "Enable editing" if you'd like to edit this file. 

Not sure why formula doesn't work, perhaps Excel automatically converted for using in pre-dynamic array Excel. Such conversion is not always correct. Formula shall look as

=TEXTJOIN(",",,IF(B4:J4<>"",IF(MATCH(B4:J4,B4:J4,0)=(COLUMN(B4:J4)-COLUMN(B4)+1),B4:J4,""),""))

the only there shall be curve brackets {} around which indicate that is array formula. Be sure no "@" characters are add, if so delete them. Entering the formula use Ctrl+Shift+Enter, not Enter.

[#17]

@Sergei Baklan Hi, just got back into looking at this and I really don't understand why the exact same formula works on your excel and not mine. Can you please open the test file below and does the formula show the unique email addresses on your excel ? as mine does not, its doesn't recognize that they are the same email address and then just adds all of the email addresses together. Thank you so much for your help and i'm completely stuck !

[#18]

@Helen15 

That's some negative impact of converting from modern Excel on previous versions.

In general formula works if enter it correctly,

If you see in formula "@" (in red in first block), remove them all. If you see the formula as regular one, enter it as array formula using Ctrl+Shift+Enter. With that it shall look as {=TEXTJOIN(.... }.

You may download the file from pervious thread, it shall be compatible with your Excel, and compare formula in it and this one.