Hi,

I asked this question before, and got the advise to use macro's. Is there
another way? And if not, how should I build up my macro so that this goes
right?

What I need is the following:

x-code z-code Product name z-date
111 Z523x A 1-1-2009
123 Z234b B 1-2-2008
321 Z354s C 1-3-2009
555 Z567k D 5-6-2007
664 Z324l F 1-3-2009
545 Z543k G 3-8-2009
234 X
124 W
761 K



The table above should be the result from combining two source documents.

The information is linked from 2 data sources:
Source 1) with the x-code and the product name
Source 2) with the z-code and the z-date and the x-code (only from products
with a z-code)

The x-code and the z-code are both unique numbers for one certain product
(as such, they need to appear in the same row).

In the lifecycle of a product, each product will first get a unique x-code,
and later in time it will get its unique z-code (in most cases, because some
products will never get a z-code).

This means that products with their unique z-code, always also have their
unique x-code. However, when a product has an x-code, it does not necessarily
have a z-code.

Also, every certain period of time products can be added to or deleted from
the source files. Plus a product with an x-code can later get a z-code (as
explained above). In this case both unique numbers will appear in the z-code
file (but not in the x-code file).

Now, what I want is that the table above automatically updates the
information as it can be found in the 2 source files. However, when a product
is added, I also want this to appear automatically in my table above. This I
can do using for example: =z-code!A1 and then copy paste this to the entire
column. As such, the entire column will always appear exactly as it can be
found in the source file. The rows not having a number will show a zero (or
with the IF function I could make them blank). Then I will always
automatically get all the z-codes as they appear in the z-code source
file/sheet.

Here follows my problem. If I use the z-code file as the basis, then I know
that all the z-codes will appear in my file (also added ones), and with INDEX
I can search for the right x-code, or even by also copy paste the exact
x-code column form the z-code file, I can get the matching x-code in my file.

But how then can I get the other x-codes in my file?? I can search for the
ones not yet in my table from the x-code file, and them add them maybe,€¦.but
then when a new z-code is added to the z-code file€¦the rows in my combined
table will not match anymore,€¦

Well, I hope my explanation is clear and that someone can help me out.

Many thanks in advance!

Marieke

In your new workbook, copy over the list of x-codes. For the rest of the
columns, you can use the VLOOKUP function, with the x-code being your lookup
value. (see XL help file for detailed info). Since it sounds like there are
some z-codes without a corresponding x-value, create an error trap on those
formulas like this:

=IF(ISERROR(VLOOKUP(A1,Lookup_Range,Column_Number, FALSE)),"",VLOOKUP(A1,Lookup_Range,Column_Number,F ALSE))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mariekek5" wrote:

Hi,

I asked this question before, and got the advise to use macro's. Is there
another way? And if not, how should I build up my macro so that this goes
right?

What I need is the following:

x-code z-code Product name z-date
111 Z523x A 1-1-2009
123 Z234b B 1-2-2008
321 Z354s C 1-3-2009
555 Z567k D 5-6-2007
664 Z324l F 1-3-2009
545 Z543k G 3-8-2009
234 X
124 W
761 K



The table above should be the result from combining two source documents.

The information is linked from 2 data sources:
Source 1) with the x-code and the product name
Source 2) with the z-code and the z-date and the x-code (only from products
with a z-code)

The x-code and the z-code are both unique numbers for one certain product
(as such, they need to appear in the same row).

In the lifecycle of a product, each product will first get a unique x-code,
and later in time it will get its unique z-code (in most cases, because some
products will never get a z-code).

This means that products with their unique z-code, always also have their
unique x-code. However, when a product has an x-code, it does not necessarily
have a z-code.

Also, every certain period of time products can be added to or deleted from
the source files. Plus a product with an x-code can later get a z-code (as
explained above). In this case both unique numbers will appear in the z-code
file (but not in the x-code file).

Now, what I want is that the table above automatically updates the
information as it can be found in the 2 source files. However, when a product
is added, I also want this to appear automatically in my table above. This I
can do using for example: =z-code!A1 and then copy paste this to the entire
column. As such, the entire column will always appear exactly as it can be
found in the source file. The rows not having a number will show a zero (or
with the IF function I could make them blank). Then I will always
automatically get all the z-codes as they appear in the z-code source
file/sheet.

Here follows my problem. If I use the z-code file as the basis, then I know
that all the z-codes will appear in my file (also added ones), and with INDEX
I can search for the right x-code, or even by also copy paste the exact
x-code column form the z-code file, I can get the matching x-code in my file.

But how then can I get the other x-codes in my file?? I can search for the
ones not yet in my table from the x-code file, and them add them maybe,€¦.but
then when a new z-code is added to the z-code file€¦the rows in my combined
table will not match anymore,€¦

Well, I hope my explanation is clear and that someone can help me out.

Many thanks in advance!

Marieke