Forum Discussion

Helen15's avatar
Helen15
Copper Contributor
Nov 20, 2020

calculation creating unique email address

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 cr...
Formulas and Functions
Helen15's avatar
Helen15
Copper Contributor
Nov 20, 2020
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.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 23, 2020

    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.

  • Helen15's avatar
    Helen15
    Copper Contributor
    Nov 23, 2020

    SergeiBaklan 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 !

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 20, 2020

    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.

Resources