M
mariekek5
Hi,
I have discovered how I can compare two columns….with the [Format >
Conditional Formatting > Formula is > COUNTIF function etc.] Doing this I
have an Oldlist and a Newlist with the numbers colorized which do not match.
What I need however 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
I have discovered how I can compare two columns….with the [Format >
Conditional Formatting > Formula is > COUNTIF function etc.] Doing this I
have an Oldlist and a Newlist with the numbers colorized which do not match.
What I need however 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
