extract unique values in an array

**froggeja** · 06-22-2015, 10:15 AM

I have 13,000 rows of customer records. I have matched the data to another source and have appended email address. I now have 7 columns containing email addresses (based on various different match keys). A customer could have more than one email address.

I want to extract the unique emails in each row. So, if Customer ABC in row 1 has 7 email addresses listed in Column B : Column H and only 3 are unique, I want to be able to move those 3 addresses into Column I, Column J, Column K.

Does anyone have a suggestion on a formula that could do this?

thanks

**simarui** · 06-22-2015, 10:46 AM

how 'bout this:

Formula:

Please Login or Register  to view this content.

(array formula, hold ctrl and shift after you type it in and those {}'s should appear automatically)

if that doesn't work please upload a sample of your workbook.

**FDibbins** · 06-22-2015, 11:05 AM

Perhaps consider trying to eliminate the duplicate as you pull them in from the other source?

**froggeja** · 06-22-2015, 11:06 AM

Hi, thank you. it looks right and I'm sure I'm doing something wrong. Please see attached sheet of sample data.

email array uniques.xlsx

Hope you can help

**simarui** · 06-22-2015, 11:27 AM

sorry, that formula needs to go in J1 and then drag. right now it's a circular reference. you can also throw an iferror() and an if(=0) on there to get rid of N/A's and 0's...

Formula:

Please Login or Register  to view this content.

**froggeja** · 06-22-2015, 11:37 AM

That's it. It's perfect. Thank you enormously. Have been fiddling around with this for hours. Will mark as solved and add reputation.

**froggeja** · 06-22-2015, 11:49 AM

Hey Simarui, it's not quite solved for me. How can I amend the formula so that it ignores blank cells (e.g. b1 contains data, c1 doesn't, d1 contains data). Currently, the formula will not proceed beyond the blank. Hope you can help!

**FDibbins** · 06-22-2015, 11:51 AM

did you see post #3?

**froggeja** · 06-22-2015, 11:54 AM

Thanks FDibbins. Not sure if it will work for me as some records can appear in multiple rows.

**simarui** · 06-22-2015, 12:02 PM

hmmmmm that's a problem... if you get rid of my if(=0) piece then it will simply return a 0 for those blank cells...

Formula:

Please Login or Register  to view this content.

or if you put ="" in the blank cells it will skip them. that might seem like a pain but it's not actually that bad... type ="" into any cell, copy it (ctrl+c), press ctrl+G, click special, blanks and then paste (ctrl+v) should do it...

oooorrr another option is to put a 0 in column I. you can then hide that column or make the text white or something to disguise it...

any of those strike your fancy?

**XOR LX** · 06-22-2015, 12:05 PM

Hi.

Would you be able to post a workbook with a very small dataset together with your expected results?

Regards

**froggeja** · 06-22-2015, 12:07 PM

One of those is bound to work! I think filling the blanks is the best solution - I'm going to have a play now.

Thanks enormously for your help.

**froggeja** · 06-22-2015, 12:11 PM

email array uniques v2.xlsx

Sample data attached. Simarui's formula works but when a cell is blank it stops. Any suggestions before I get into a more manual fix ;-)

thanks

**FDibbins** · 06-22-2015, 12:22 PM

Where does this data come from? what is the formula that pulls it in?

**froggeja** · 06-22-2015, 12:26 PM

I'm combining a customer list and a 3rd party source. I'm matching data using multiple look ups - i.e. in column B my match is where the City and Address Line 1 match to City and Address Line 1 in the 3rd party source; in Column C I'm using matching phone numbers. Different matches result in different email addresses coming in. The data is in a poor condition unfortunately. Do you know how to skip blank cells when using an array formula - if I can add that to Simarui's formula I'm in business!

**FDibbins** · 06-22-2015, 01:49 PM

Im actually thinking that maybe we can modify your lookups so they dont pull in duplicates in the 1st place

Can you upload a small sample file of what you have/are doing?

**Czeslaw** · 06-22-2015, 03:28 PM

And if you try that.

**froggeja** · 06-24-2015, 03:58 AM

Works a treat. Thanks. I'm so appreciative of all the help received.

extract unique values in an array

LinkBack

Thread Tools

Rate This Thread

Display

extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Re: extract unique values in an array

Thread Information

Users Browsing this Thread

Similar Threads

Extract Unique Values from VBA array

[SOLVED] Array formula to extract and sort unique values from two worksheets

Extract unique values and their count from a two dimensional Array

Excel 2007 : Array formula to extract unique values from a range

Extract Unique Values from an Array based on Criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions