Split Unique Formula

0
votes

I don't think I could explain better the just show with good example. I want to SPLIT() UNIQUE() in one formula...

There are 2 tables:

Table1:                     Table2:
ID                          Name | Phone | Email
Jhon,111,email1   Formula->[here]     
Adam,222,email2
Adam,222,email2
Kris,333,email3

Here is what formulas I use: =TRANSPOSE(ArrayFormula(unique(split(CONCATENATE(Orders!H2:H&" ");", "))))

And this is what it does

ID                         Name | Phone | Email
Jhon,111,email1   Formula->Jhon     
Adam,222,email2            111
Adam,222,email2            email1
Kris,333,email3            Adam
                           222
                           email2
                           Kris
                           333
                           email3

Another formula is: =ArrayFormula(unique(split(CONCATENATE(Orders!H2:H&" ");", ")))

ID                         Name | Phone | Email  |      |     |
Jhon,111,email1   Formula->Jhon   111     email1   Adam   222   etc...
Adam,222,email2            
Adam,222,email2            
Kris,333,email3

Here is what I am trying to do

ID                         Name | Phone | Email
Jhon,111,email1   Formula->Jhon    111    email1 
Adam,222,email2            Adam    222    email2
Adam,222,email2            Kris    333    email3 
Kris,333,email3
3
google-sheets

3 Answers

1
votes

Split does not work with arrayformula. Try this instead:

=unique(ArrayFormula(IFERROR(REGEXEXTRACT(","&Orders!H2:H,"^"&REPT(",+[^,]+",COLUMN(OFFSET(A1,,,1,3))-1)&",+([^,]+)"))))

enter image description here

1
votes

enter image description hereIf all of your data has the same structure (name, number, email), a simpler version would be

=ArrayFormula(iferror(regexextract(Orders!H2:H, {"^(.+?),",",(.+),","[^,]+$"})))

Note if the data is separated with a comma AND a space add a trim to the formula

=ArrayFormula(iferror(trim(regexextract(A1:A, {"^(.+?),",",(.+),","[^,]+$"}))))
0
votes

Just the one function is sufficient. Copy the body of Table1 into a spare range, apply Data > Split text into columns... and then where you show the formula is required:

=unique( ' s p a r e   r a n g e ' )