Unifying data from various Excel files

[Barak Turovsky]

Hi all,

I need to unify data from three different excel files.

All files contain only one field - telephone number.

The unification of the 1st and the 2nd fiels should be in a way that the
data will be unified completely with filter on the phone number to avoid the
situation that the same phone number will be stored twice.
To make the situation worse, the data from the 3rd file will be unified with
the data on the first 2 files in a way that in case that the phone number
stored in the 3rd file exists in the unified file of first 2 files - than
this phone number should b REMOVED from the completely unified file (of all
3 files).

Is that possible to do it in excel? If it is, can somebody send me an
example or guidance how to do it?

Thanks in advance,

Barak

 

[Dave Peterson]

I think I'd do this:

Create a new workbook.

Copy each of the sheets from the 3 workbooks into this new workbook.

Now on an empty worksheet, copy all the phone numbers from each of the key
columns in each of the sheets.

Now you have a single column list (with duplicates) in A1.

Now use Data|filter|Advanced Filter ("unique records only") to obtain a single
column list with no duplicates. Put them in column B.

Debra Dalgleish has some notes with pictures at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Now delete column A.

The single column list that was in B is now in A.

Now you can use =vlookup()'s to retrieve the values from each of the sheets.
(make sure each of the sheets has the phone number in Column A. If it's not
there, move it there.)

=vlookup($a1,sheet2!$a:$e,5,false)
Will return the 5th column for the first row that has a match in column A of
Sheet1 for A1 (of that master list).

Do as many =vlookup()'s as you need. remember to point to the other worksheets
to grab that info.

And make sure you include a =vlookup() for the 3rd file. You can use that to
delete the phone numbers later.

If there is no match between A1 and the lookup table's first column, you'll see
#n/a. If you want to hide that, you can modify your formula to look like:

=if(iserror(vlookup($a1,sheet2!$a:$e,5,false)),"missing",
vlookup($a1,sheet2!$a:$e,5,false))

(one cell)

After you're satisfied that you retrieved the correct data (check twice!), you
can convert all the formulas to values and delete those 3 other worksheets.

Then select your range and do
Data|Filter|Autofilter

Filter by that column that represents the 3rd worksheet.
Use the dropdown to do "custom"
filter on:
does not equal "missing"
(the rows that are shown are the ones that appeared in your 3rd workbook).

Select those visible rows and delete them.

Remember to save your work every so often. When I do things like this, I'll
save them under intermediate names. Then when I screw up step 49, I can reopen
the workbook after step 48 and continue without too many tears.