Merging 2 files

S

shav

Hi all,
I have 2 workbooks that are similar to the structure below.
Workbook 1 Workbook 2
ColA Col B ColC || ColA Col B
ColC
A Desciption of A 1 || A Desciptio
of A 3
B Desciption of B 2 || C Desciptio
of C 2
C Desciption of C 1 || D Desciptio
of D 3

Is there any way that I can combine the two of these to resemble th
following.

ColA Col B ColC ColD
A Desciption of A 1 3
B Desciption of B 2
C Desciption of C 1 2
D Desciption of D 3

As can be seen I want to keep all of the values in seperate columns
I've look on the forum but everything seems a bit over my head. What'
the easiest way of doing this. The files are too big to cut and paste
Any help greatly appreciated. Cheers
Sha
 
M

Max

Perhaps this might help ..

Let's say you have:

In Book1.xls, Sheet1
---------------------------
in cols A, B and C, from row1 down

A Text1 1
B Text2 2
C Text3 1
D Text4 ..
etc

Note: Col B is assumed to contain only unique descriptions,
i.e. Text1, Text2, etc will occur once only (if they do occur) in col B

In Book2.xls, Sheet1
---------------------------
in cols A, B and C, from row1 down

A Text1 3
C Text3 2
D Text4 3
etc

Note: Same caveat on col B as for Book1.xls above

In a new Book3.xls, Sheet1
-------------------------------------
List in cols A and B
from row2 down

A Text1
B Text2
C Text3
D Text4
etc

Put the 2 source filenames into C1 and D1, viz.:

In C1: Book1
In D1: Book2

Put in C2:

=IF(ISNA(MATCH($B2,INDIRECT("["&TRIM(C$1)&".xls]Sheet1!$B:$B"),0)),"",OFFSET
(INDIRECT("["&TRIM(C$1)&".xls]Sheet1!$C$1"),MATCH($B2,INDIRECT("["&TRIM(C$1)
&".xls]Sheet1!$B:$B"),0)-1,))

Copy C2 across to D2, then copy down as many rows as there is data

Cols C and D will return what you're after

For a neater look, extraneous zeros can be suppressed from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

Note: The 2 source files: Book1.xls and Book2.xls have to be open
along with Book3.xls for the above to work
 
Top