Hello- my first post to the Excel group. I'm new on 2003 (SP2).

I have existing records of FirstName, LastName and Company.
I'm planning to upload additional records of FirstName, LastName and Company
and want to make sure that I don't upload any already exisiting records.

How can I compare FirstName, LastName and Company from one file (all 3
together would make the record "unique") against FirstName, LastName and
Company from another file?
Thanks for your help!

Assume first name, last name, and company are in columns A, B, C
respectively.
=IF(ISNUMBER(MATCH(CONCATENATE(A1,B1,C1),'[File2]Sheet1'!$A$1:$A$100,0)),"Matched","not matched")

You have to enter the correct cell references to concatenate (join) above,
as well as the correct file name and sheet name and match range for the
external file.

If you do this correctly, where a match between the concatenated fields in
file 1 and the match range in file 2 occurs, "Match" will be returned in the
cell in which the above formula is entered, else "not matched" will be
returned.

Any questions, post back.

Dave



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Stephanie" wrote:

Hello- my first post to the Excel group. I'm new on 2003 (SP2).

I have existing records of FirstName, LastName and Company.
I'm planning to upload additional records of FirstName, LastName and Company
and want to make sure that I don't upload any already exisiting records.

How can I compare FirstName, LastName and Company from one file (all 3
together would make the record "unique") against FirstName, LastName and
Company from another file?
Thanks for your help!

Dave,
Thanks for the post! I appreciate your help.
I don't seem to have the match working quite right. Every record is "not
matched" which is not a true statement.

Here's my set up:
I have one file called "indb" (this is what is already in the db)
The other file is called "datafile" (this is what I want to load, without
dupes)

To simplify, I created a column d in each file that is the concatenation of
a, b and c. So I just need to compare column d in "indb" to column d in
"datafile".

"datafile" has about 3000 records. "indb" has about 500 records.

In "datafile" I created a column with this formula:
=IF(ISNUMBER(MATCH([Indb.xls]Sheet1!$D:$D,0)),"Matched", "no match")

I believe this formula says for every (3000) record in "datafile", look at
all records in column d of "indb" and if there is a match then the formula
states "Matched", if there is no match (the match value is "0") the formula
states "not matched".

What am I missing?
Thanks for your time!



"Dave F" wrote:

Assume first name, last name, and company are in columns A, B, C
respectively.
=IF(ISNUMBER(MATCH(CONCATENATE(A1,B1,C1),'[File2]Sheet1'!$A$1:$A$100,0)),"Matched","not matched")

You have to enter the correct cell references to concatenate (join) above,
as well as the correct file name and sheet name and match range for the
external file.

If you do this correctly, where a match between the concatenated fields in
file 1 and the match range in file 2 occurs, "Match" will be returned in the
cell in which the above formula is entered, else "not matched" will be
returned.

Any questions, post back.

Dave



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Stephanie" wrote:

Hello- my first post to the Excel group. I'm new on 2003 (SP2).

I have existing records of FirstName, LastName and Company.
I'm planning to upload additional records of FirstName, LastName and Company
and want to make sure that I don't upload any already exisiting records.

How can I compare FirstName, LastName and Company from one file (all 3
together would make the record "unique") against FirstName, LastName and
Company from another file?
Thanks for your help!

Match takes 3 arguments. You only have 2 in this portion:

MATCH([Indb.xls]Sheet1!$D:$D,0)

so maybe...

=IF(ISNUMBER(MATCH(d1,[Indb.xls]Sheet1!$D:$D,0)),"Matched", "no match")



Stephanie wrote:

Dave,
Thanks for the post! I appreciate your help.
I don't seem to have the match working quite right. Every record is "not
matched" which is not a true statement.

Here's my set up:
I have one file called "indb" (this is what is already in the db)
The other file is called "datafile" (this is what I want to load, without
dupes)

To simplify, I created a column d in each file that is the concatenation of
a, b and c. So I just need to compare column d in "indb" to column d in
"datafile".

"datafile" has about 3000 records. "indb" has about 500 records.

In "datafile" I created a column with this formula:
=IF(ISNUMBER(MATCH([Indb.xls]Sheet1!$D:$D,0)),"Matched", "no match")

I believe this formula says for every (3000) record in "datafile", look at
all records in column d of "indb" and if there is a match then the formula
states "Matched", if there is no match (the match value is "0") the formula
states "not matched".

What am I missing?
Thanks for your time!

"Dave F" wrote:

Assume first name, last name, and company are in columns A, B, C
respectively.
=IF(ISNUMBER(MATCH(CONCATENATE(A1,B1,C1),'[File2]Sheet1'!$A$1:$A$100,0)),"Matched","not matched")

You have to enter the correct cell references to concatenate (join) above,
as well as the correct file name and sheet name and match range for the
external file.

If you do this correctly, where a match between the concatenated fields in
file 1 and the match range in file 2 occurs, "Match" will be returned in the
cell in which the above formula is entered, else "not matched" will be
returned.

Any questions, post back.

Dave



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Stephanie" wrote:

Hello- my first post to the Excel group. I'm new on 2003 (SP2).

I have existing records of FirstName, LastName and Company.
I'm planning to upload additional records of FirstName, LastName and Company
and want to make sure that I don't upload any already exisiting records.

How can I compare FirstName, LastName and Company from one file (all 3
together would make the record "unique") against FirstName, LastName and
Company from another file?
Thanks for your help!

--

Dave Peterson

Interesting- I actually used:

=IF(ISNUMBER(MATCH($d:$d,[Indb.xls]Sheet1!$D:$D,0)),"Matched", "no match")
and had a return of "matched" where there was one!

Thanks for the help!

"Dave Peterson" wrote:

Match takes 3 arguments. You only have 2 in this portion:

MATCH([Indb.xls]Sheet1!$D:$D,0)

so maybe...

=IF(ISNUMBER(MATCH(d1,[Indb.xls]Sheet1!$D:$D,0)),"Matched", "no match")



Stephanie wrote:

Dave,
Thanks for the post! I appreciate your help.
I don't seem to have the match working quite right. Every record is "not
matched" which is not a true statement.

Here's my set up:
I have one file called "indb" (this is what is already in the db)
The other file is called "datafile" (this is what I want to load, without
dupes)

To simplify, I created a column d in each file that is the concatenation of
a, b and c. So I just need to compare column d in "indb" to column d in
"datafile".

"datafile" has about 3000 records. "indb" has about 500 records.

In "datafile" I created a column with this formula:
=IF(ISNUMBER(MATCH([Indb.xls]Sheet1!$D:$D,0)),"Matched", "no match")

I believe this formula says for every (3000) record in "datafile", look at
all records in column d of "indb" and if there is a match then the formula
states "Matched", if there is no match (the match value is "0") the formula
states "not matched".

What am I missing?
Thanks for your time!

"Dave F" wrote:

Assume first name, last name, and company are in columns A, B, C
respectively.
=IF(ISNUMBER(MATCH(CONCATENATE(A1,B1,C1),'[File2]Sheet1'!$A$1:$A$100,0)),"Matched","not matched")

You have to enter the correct cell references to concatenate (join) above,
as well as the correct file name and sheet name and match range for the
external file.

If you do this correctly, where a match between the concatenated fields in
file 1 and the match range in file 2 occurs, "Match" will be returned in the
cell in which the above formula is entered, else "not matched" will be
returned.

Any questions, post back.

Dave



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Stephanie" wrote:

Hello- my first post to the Excel group. I'm new on 2003 (SP2).

I have existing records of FirstName, LastName and Company.
I'm planning to upload additional records of FirstName, LastName and Company
and want to make sure that I don't upload any already exisiting records.

How can I compare FirstName, LastName and Company from one file (all 3
together would make the record "unique") against FirstName, LastName and
Company from another file?
Thanks for your help!

--

Dave Peterson

Using Advance filtering technique

so that all the column information are retrieved even if the typed cells are
misspelled, which normally is not the case when you do a simple filtering
technique. The filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu


"Stephanie" wrote:

Hello- my first post to the Excel group. I'm new on 2003 (SP2).

I have existing records of FirstName, LastName and Company.
I'm planning to upload additional records of FirstName, LastName and Company
and want to make sure that I don't upload any already exisiting records.

How can I compare FirstName, LastName and Company from one file (all 3
together would make the record "unique") against FirstName, LastName and
Company from another file?
Thanks for your help!