C
chrisn
I did a quick search on this topic and to be honest found that I know s
little about this topic, that I was lost trying to understand what
saw. So to be honest, I am looking for someone to basically hand hol
me through this problem.
A coworker has a report that she files once a month, this report is
culmination of four Excel reports into one. The four reports we ar
combining consist of a beginning of month inventory, end of mont
inventory, cost of inventory, and product ordered and shipped.
The problem I am running into is that none of the reports she is abl
to pull from our database has all the "products" on it. In essence th
"beginning inv" report might have products "A, B, D," while "endin
inv" might have products "A, C, D" and "ordered" report might hav
products "A, B, C" the cost of inventory report might have products "B
C, D".
In the three months I have been assisting her in this report, I hav
found that the beginning of the month report is most often the mos
complete and having 98%+ of the products being reported on within it.
am using the beginning of the month report as my basis for making th
final report, and running a "vlookup" on the cells of that report t
get the information from the three remaining reports.
Where I am running into a problem (as I hope I have been descriptiv
enough at this point that you can see) is that when the "vlookup
encounters a report that does not have a cell equal to the cell I a
looking for, I get "N/A" as a result. Then as the formula runs throug
the rest of the column, it continues to get no valid search result fo
the "vlookup" and the remainder of the column is "#N/A".
I have thought of a few possible solutions to this, yet have run into
brick wall every time I come up with a new idea. The main problem
have is with the "#N/A", and that it appears to not be searchable.
have tried to write an "IF" statement looking for "#N/A", "<1", an
">1" in an attempt to input a value of "0(zero)" in cells that have n
valid result. Yet that seems to be a dead end and yields an "#N/A" as
result, regardless of how I write it.
The easy solution is to have the database program put out a repor
which consists of all products that it has within the criteria o
beginning of month to end of month, regardless of whether an order ha
been filed, or whether there is a cost associated with inventory o
not. This is beyond the capabilities of the program we are using.
Now that I have told the story, this is what I hope to find here, a wa
to write a macro on a separate worksheet within the book, that "o
click" of a button that will do the following. (I have attached a
Excel sheet that is a small replication of what I am working with):
1. Scan "beginning" column A.
2. Scan "end" column A.
a. Insert a line in the "beginning" worksheet to missing cell
from "end" worksheet. I need to add column "A", "B", and "C" from "end
with Column "C" value equal to "0".
b. Insert a line in the "end" worksheet and add any cells tha
appear in the "beginning" worksheet that are missing. I need to ad
column "A", "B", and "C" from "end" with Column "C" value equal t
"0".
3. Scan "order history" column A.
a. Insert a line in the "order history" worksheet that adds an
cells that appear on the now complete "end" worksheet. I need to ad
Column "C" and "D" with both columns equal to "0".
4. Scan "cost of inventory" column A.
a. Insert a line in the "cost of inventory" worksheet that add
any cells that appear on the now complete "order history" worksheet.
need to add Column "C", "D" and "E" with all column values equal t
"0".
If this cannot be done, can someone provide a better formula to us
that does not return an "#N/A" value.
I have tried the following:
=If(vlookup(a2,end!a2:c24,3,false)<1,"0",(vlookup(a2,end!a2:c24,3,false))
And
=If(vlookup(a2,end!a2:c24,3,false)="N/A","0",(vlookup(a2,end!a2:c24,3,false))
Both yield "#N/A" on the cells that the vlookup fails to find a match.
Either solution will make me a hero, if anyone can offer some help,
will be more than grateful.
Chris
Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=455287
little about this topic, that I was lost trying to understand what
saw. So to be honest, I am looking for someone to basically hand hol
me through this problem.
A coworker has a report that she files once a month, this report is
culmination of four Excel reports into one. The four reports we ar
combining consist of a beginning of month inventory, end of mont
inventory, cost of inventory, and product ordered and shipped.
The problem I am running into is that none of the reports she is abl
to pull from our database has all the "products" on it. In essence th
"beginning inv" report might have products "A, B, D," while "endin
inv" might have products "A, C, D" and "ordered" report might hav
products "A, B, C" the cost of inventory report might have products "B
C, D".
In the three months I have been assisting her in this report, I hav
found that the beginning of the month report is most often the mos
complete and having 98%+ of the products being reported on within it.
am using the beginning of the month report as my basis for making th
final report, and running a "vlookup" on the cells of that report t
get the information from the three remaining reports.
Where I am running into a problem (as I hope I have been descriptiv
enough at this point that you can see) is that when the "vlookup
encounters a report that does not have a cell equal to the cell I a
looking for, I get "N/A" as a result. Then as the formula runs throug
the rest of the column, it continues to get no valid search result fo
the "vlookup" and the remainder of the column is "#N/A".
I have thought of a few possible solutions to this, yet have run into
brick wall every time I come up with a new idea. The main problem
have is with the "#N/A", and that it appears to not be searchable.
have tried to write an "IF" statement looking for "#N/A", "<1", an
">1" in an attempt to input a value of "0(zero)" in cells that have n
valid result. Yet that seems to be a dead end and yields an "#N/A" as
result, regardless of how I write it.
The easy solution is to have the database program put out a repor
which consists of all products that it has within the criteria o
beginning of month to end of month, regardless of whether an order ha
been filed, or whether there is a cost associated with inventory o
not. This is beyond the capabilities of the program we are using.
Now that I have told the story, this is what I hope to find here, a wa
to write a macro on a separate worksheet within the book, that "o
click" of a button that will do the following. (I have attached a
Excel sheet that is a small replication of what I am working with):
1. Scan "beginning" column A.
2. Scan "end" column A.
a. Insert a line in the "beginning" worksheet to missing cell
from "end" worksheet. I need to add column "A", "B", and "C" from "end
with Column "C" value equal to "0".
b. Insert a line in the "end" worksheet and add any cells tha
appear in the "beginning" worksheet that are missing. I need to ad
column "A", "B", and "C" from "end" with Column "C" value equal t
"0".
3. Scan "order history" column A.
a. Insert a line in the "order history" worksheet that adds an
cells that appear on the now complete "end" worksheet. I need to ad
Column "C" and "D" with both columns equal to "0".
4. Scan "cost of inventory" column A.
a. Insert a line in the "cost of inventory" worksheet that add
any cells that appear on the now complete "order history" worksheet.
need to add Column "C", "D" and "E" with all column values equal t
"0".
If this cannot be done, can someone provide a better formula to us
that does not return an "#N/A" value.
I have tried the following:
=If(vlookup(a2,end!a2:c24,3,false)<1,"0",(vlookup(a2,end!a2:c24,3,false))
And
=If(vlookup(a2,end!a2:c24,3,false)="N/A","0",(vlookup(a2,end!a2:c24,3,false))
Both yield "#N/A" on the cells that the vlookup fails to find a match.
Either solution will make me a hero, if anyone can offer some help,
will be more than grateful.
Chris
Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=455287