How can I join 2 spreadsheets if I want both to update the fields of the main
file
by the fielda of the update file and to add the records of the update file
which aren't in the main file (I vannot use VLOOKUP in this case, or I don't
know how):
In my case I have the main file with records:
F1 F2 F3 ....
1 2 31
2 3 32
3 4 33
7 7 37
and I have to update it from the update file with fields
F1 F3
1 51
3 53
5 55
where F1 is the key field
as a result I must receive:
F1 F2 F3 ....
1 2 51
2 3 32
3 4 53
5 55
7 7 37

How can I do it?

I'd create a new worksheet.
Copy the F1 columns from both worksheets to column A of that new worksheet
(but only one header row)

Then I'd use data|filter|advanced filter to get a list of unique dates
(eliminating duplicates)
Debra Dalgleish has a nice instruction page at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then delete column A (we'll have the unique values in the new column A)

Then use a bunch of =vlookup()'s to retrieve the values from the first sheet and
the second sheet.

For the F2 column (in B2 and drag down)

=if(iserror(vlookup(a2,sheet1!a:c,2,false)),"",
if(vlookup(a2,sheet1!a:c,2,false)="","",
vlookup(a2,sheet1!a:c,2,false)))

Then for the first F3 (from the original list) (in C2 and drag down)

=if(iserror(vlookup(a2,sheet1!a:c,3,false)),"",
if(vlookup(a2,sheet1!a:c,3,false)="","",
vlookup(a2,sheet1!a:c,3,false)))

Then for the second F3 (from the other sheet) (in D2 and drag down)

=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",
if(vlookup(a2,sheet2!a:b,2,false)="","",
vlookup(a2,sheet2!a:b,2,false)))

Now you've got all the data in one spot.

You can add a formula in E2 (and drag down).

=if(d2<"",d2,c2)
(If you have something in D2, keep it. Else use what's in C2.)

Then select column E and
edit|copy
edit|paste special|values

Delete (or hide) columns C and D.



Then in

Again, Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html

For the F2 values

Raya wrote:

How can I join 2 spreadsheets if I want both to update the fields of the main
file
by the fielda of the update file and to add the records of the update file
which aren't in the main file (I vannot use VLOOKUP in this case, or I don't
know how):
In my case I have the main file with records:
F1 F2 F3 ....
1 2 31
2 3 32
3 4 33
7 7 37
and I have to update it from the update file with fields
F1 F3
1 51
3 53
5 55
where F1 is the key field
as a result I must receive:
F1 F2 F3 ....
1 2 51
2 3 32
3 4 53
5 55
7 7 37

How can I do it?

--

Dave Peterson