Find data.

Resolved · High Priority · Version 2010

Tue 9 Aug 2016, 12:23 Delegate Susan said...

Susan has attended:
Excel Advanced course

Find data.

Hi,

I have an issue with excel and hoping there's a quick solution.

I have listed about 6000 email address's on an excel spreadsheet to use to send out a series of group emails.
Our outlook has failed for most of these and as a result only 278 emails were sent out.

I have these 278 emails on a seperate spreasheet.

Is there a way of searching for these 278 email address's on the seperate spreadsheet of 6000, to avoid me sending a duplicate email.

Please advise as I urgently need to send it out.

Many thanks,

Susan.

Tue 9 Aug 2016, 15:04 Trainer Marius said...

RE: find data.

Hi Susan,

Thank you for your question. What I would suggest is to create a vlookup formula on the sheet with the 6000 addresses, starting in the same row as the first address. In the vlookup, you use the first address in the sheet with 6000 as your lookup value. The table array will be the list of 278 addresses. The column index number is 1 because your table array only consists of one column. Range lookup is false, as you want to find exact matches.

When you copy the vlookup down the column, the 278 email addresses will show up as results inbetween the #N/A results. Now you can use a filter to hide all #N/A results, and you will be left with the 278 emails which failed to send.

I hope this is helpful.

Kind regards
Marius Barnard
Excel Trainer

Tue 16 Aug 2016: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Changing Excel file and worksheet defaults

The appearance of any new Excel files or any new worksheets that are inserted into a file are controlled by two template files, Book.xlt and Sheet.xlt.

By opening, modifying and saving these templates you can change the default settings for all new files and/or all newly inserted sheets.

Use Book.xlt to make change to defaults for new workbooks; and Sheet.xlt to change defaults for sheets.

If you can't find either of these files on your computer, you can create and save them yourself.

You can do this simply by creating a new workbook with the setting you want to use as your defaults; then saving them with the appropriate name in the XLStart folder. If you are using Office 2003, this is usually found in C:\Program Files\Microsoft Office\Office11\XLStart.

View all Excel hints and tips