Merge worksheets

[Greg]

Hello,

I have two worksheets in "list" format. I would like to merge these two
list into a third worksheet if possible.. If not possible merging into one
of the original worksheets is ok.

Both of these tables "lists" are sequenced by the first column (a text field
containing a telephone number). List one is the master list because all rows
i.e. telephone numbers will be on this list(sorted in ascending order based
on telephone number). The second list(called the update list) will be
identical to the first also sorted into ascending order. However it may only
have only a subset of the master list telephone numbers.

Both lists will have identical columns. I would like to get a result list
reflecting all rows with column values updated to the Master from the update
list if there is a value other than a default value in the update list.

I will try to give an example.

Master List

nnnnnnnnnn1,a,b,null,2,4
nnnnnnnnnn2,a,b,7,null,2
nnnnnnnnnn3,null,b,5,2,6

Update List

nnnnnnnnnn2,s,b,7,g,2

Result List

nnnnnnnnnn1,a,b,null,2,4
nnnnnnnnnn2,s,b,7,g,2
nnnnnnnnnn3,null,b,5,2,6

Is this possible to do in Excel 2000?? How is it done? I am new at Excel and
beg forgiveness if the answer to this question is obvious but I could find
nothing in the "help" section that describes this capability. If anyone
knows a solution with more flexibility than I described in my example please
share with me!!

 

[Nick Hodge]

Greg

I think I understand. If so I would use a VLOOKUP formula on a third sheet,
copy your master phone numbers to the new sheet (Say A1:A200) and then in B1
enter

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$F$200,2,FALSE)),VLOOKUP(A1,Sheet2!$A$1:$F$200,2,FALSE),VLOOKUP(A1,Sheet1!$A$1:$F$200,2,FALSE))

This presumes that your master sheet is called Sheet1, your update sheet is
sheet2.

You can copy this formula down and across but when copying across change the
,2, to ,3, for each column you move, in each function to 'offset' the data
by one column to return the next data

You could also highlight B1:F1 and enter a similar formula

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$F$200,{2,3,4,5,6},FALSE)),VLOOKUP(A1,Sheet2!$A$1:$F$200,{2,3,4,5,6},FALSE),VLOOKUP(A1,Sheet1!$A$1:$F$200,{2,3,4,5,6},FALSE))

Only this time enter the formula with Ctrl+Shift+Enter. This way you set an
array of offsets and only need copy the formula down


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Ragdyer]

You could also increment the column index while copying across, without
going into arrays, by using the Columns() function.

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$F$200,COLUMNS($A:B),FALSE)),VLOOKUP(A1,S
heet2!$A$1:$F$200,COLUMNS($A:B),FALSE),VLOOKUP(A1,Sheet1!$A$1:$F$200,COLUMNS
($A:B),FALSE))

 

[John13]

I also need to merge worksheets and tried your suggestion but can't get
any Sheet2 changes to show up on Sheet3.
Here is my sample database: (Columns A through F)
Phone Number Name Data1 Data2 Data3 Data4
555-478-3141 123 1235063 1235079 190,011 1,045,052
555-478-3142 126 1234828 1234844 189,975 1,044,853
555-478-3143 129 1234593 1234609 189,939 1,044,654
555-478-3144 132 1234358 1234374 189,902 1,044,456
555-478-3145 135 1234123 1234139 189,866 1,044,257
555-478-3146 138 1233888 1233904 189,830 1,044,058
555-478-3147 141 1233653 1233669 189,794 1,043,859
555-478-3148 144 1233418 1233434 189,758 1,043,660
When I go to Sheet2 and put this:
Phone Number Name Data1 Data2 Data3 Data4
765-478-3143 ABC Updated 1232259 189577 1042666
765-478-3147 BCD Updated 1230614 189324 1041274
Sheet3 doesn't pick the change up. My formula's are exactly what you
wrote. Any ideas?

Thank you.

John13

 

[RagDyer]

Since your phone numbers don't match, how do you expect the formula to
return non-existent matches?

555-478-3143 <> 765-478-3143
555-478-3147 <> 765-478-3147

 

[RagDyer]

On another note, I only inserted the Columns() function in the original
formula, without really looking at it.

I would now suggest a slight set of revisions to look at Sheet2 *first*,
since that sheet would contain the primary change (update) to the main list
in Sheet1.

=IF(ISNA(VLOOKUP($A2,Sheet2!$A$1:$F$200,COLUMNS($A:B),0)),VLOOKUP($A2,Sheet1!$A$1:$F$200,COLUMNS($A:B),0),VLOOKUP($A2,Sheet2!$A$1:$F$200,COLUMNS($A:B),0))

 

[John13]

RD,

The 765 / 555 thing was a cut & paste error to the Reply to Topic web
page. I tried your re-thought version (of the Sheet2 logic) and it
works fine now. Thank you very much for your help in this.

John13

 

[RagDyer]

You're welcome, and appreciate the feed-back.

 

[Greg]

Thank You everyone for your help and suggestions. I think I understand and I
am presently implementing your excellent suggestions!!

Thank you again,

Greg