Comparing data and updating spreadsheets
 

I have two spreadsheets with Last Name and First Name columns. What I would
like to do is compare the Last Name and First Name fields in Spreadsheet A to
the same fields in Spreadsheet B. If there's a match, I would like to take
the UserID from Spreadsheet A and update the UserID column in Spreadsheet B.
What is the easiest way to accomplish this?

when you say update the User ID on sheet two, is there an existing number
which will be changed. if so, I would add a new column with the old IDs on
sheet two and add a new IDcolumn with
=if(iserror(sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first))),oldid,sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first),Sh1!IDrange))
with the appropriate cells a

"mvhutton" wrote:

I have two spreadsheets with Last Name and First Name columns. What I would
like to do is compare the Last Name and First Name fields in Spreadsheet A to
the same fields in Spreadsheet B. If there's a match, I would like to take
the UserID from Spreadsheet A and update the UserID column in Spreadsheet B.
What is the easiest way to accomplish this?

Whoops! What I want to do is update the UserID column in Spreadsheet B if the
names match and the UserID in Spreadsheet B is blank.

"bj" wrote:

when you say update the User ID on sheet two, is there an existing number
which will be changed. if so, I would add a new column with the old IDs on
sheet two and add a new IDcolumn with
=if(iserror(sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first))),oldid,sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first),Sh1!IDrange))
with the appropriate cells a

"mvhutton" wrote:

I have two spreadsheets with Last Name and First Name columns. What I would
like to do is compare the Last Name and First Name fields in Spreadsheet A to
the same fields in Spreadsheet B. If there's a match, I would like to take
the UserID from Spreadsheet A and update the UserID column in Spreadsheet B.
What is the easiest way to accomplish this?

Trythe previous recommendation and changing the equation to
=if(and(oldid="",not(iserror(sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first)))),sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first),Sh1!IDrange),oldid)

I would add the step of copying the newid and paste special values over the
oldid range before new data is added to Sh1!
"mvhutton" wrote:

Whoops! What I want to do is update the UserID column in Spreadsheet B if the
names match and the UserID in Spreadsheet B is blank.

"bj" wrote:

when you say update the User ID on sheet two, is there an existing number
which will be changed. if so, I would add a new column with the old IDs on
sheet two and add a new IDcolumn with
=if(iserror(sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first))),oldid,sumproduct(--(Sh1!Lastrange=last),--(Sh1!firstrange=first),Sh1!IDrange))
with the appropriate cells a

"mvhutton" wrote:

I have two spreadsheets with Last Name and First Name columns. What I would
like to do is compare the Last Name and First Name fields in Spreadsheet A to
the same fields in Spreadsheet B. If there's a match, I would like to take
the UserID from Spreadsheet A and update the UserID column in Spreadsheet B.
What is the easiest way to accomplish this?