Need Excel Help

M

melissad

:eek: am not sure if this in the right forum, but I need help.

OK I have two spreadsheets. One spreadsheet (out of stock) shows model
numbers that have been taken out of stock. The other spreadsheet
(stock) shows the same model numbers and a quantity field.

I need to be able to make the model numbers from the out of stock
spreadsheet to be filtered (?) in to my stock spreadsheet and recognize
which items are out of stock and change the quanity field to zero.

right now the stock spreadsheet has a model # column and a product
quantity column, the model # column has numbers in the the product
quantity has the number 20 in it all the way down. I should mention
there are 6000 rows in the stock spreadsheet.

How can I accomplish this??

Thank you. If I didnt explain it right, please ask questions.

Melissa
 
S

SteveG

Melissa,

Does your Stock sheet have a formula pulling in the quantity or is it
entered manually? If it is a formula, could you post it? If not, you
could use a helper column to the right of the quantity in the Stock
sheet. If your data range is A1:B6000 with column headings in both
Stock and out of stock, in Stock!C2 enter the following formula.

=IF(ISNA(MATCH(A2,'Out of Stock'!$A$2:$A$6000,0)),Stock!B2,0)

This will populate C2 with a zero if the product code is found in Out
of Stock, if not, it populates with the quantity from the Stock
worksheet. Copy this down your range. Copy column C and Paste
Special>Values over B.

Does that help?

Steve
 
M

melissad

SteveG said:
Melissa,

Does your Stock sheet have a formula pulling in the quantity or is i
entered manually? If it is a formula, could you post it? If not, yo
could use a helper column to the right of the quantity in the Stoc
sheet. If your data range is A1:B6000 with column headings in bot
Stock and out of stock, in Stock!C2 enter the following formula.

=IF(ISNA(MATCH(A2,'Out of Stock'!$A$2:$A$6000,0)),Stock!B2,0)

This will populate C2 with a zero if the product code is found in Ou
of Stock, if not, it populates with the quantity from the Stoc
worksheet. Copy this down your range. Copy column C and Past
Special>Values over B.

Does that help?

Steve
Click to expand...

There is no function. I am going to try it now. Thank you
 
M

melissad

I guess I dont understand it...

My out of stock spreadsheet data is Model numbers A1:A1479 -spreadsheet
name is oos
My in stock spreadsheet is Model numbers A2:6044 -- spreadsheet name
is instock
My in stock spreadsheet Quantity is f2:6044 -- spreadsheet
name is instock


Can you please help me rewrite what you wrote to match?

thank you !
 
H

Harlan Grove

melissad wrote...
....
OK I have two spreadsheets. One spreadsheet (out of stock) shows model
numbers that have been taken out of stock. The other spreadsheet
(stock) shows the same model numbers and a quantity field.
Click to expand...

Presumably these are separate files. If so, they should both be open.
I'll assume the out of stock file is names OOS.xls and the range
containing the model numbers is named MNs, so references to this range
from the stock file would look like 'OOS.xls'!MNs.
I need to be able to make the model numbers from the out of stock
spreadsheet to be filtered (?) in to my stock spreadsheet and recognize
which items are out of stock and change the quanity field to zero.
Click to expand...
....

The easiest way to do this would be to use an extra column as the
'actual' stock quantity with your original quantity column being
treated as a 'initial' stock quantity. If the topmost model number in
the stock file were in cell A2, and the corresponding initial quantity
were in cell B2, and the corresponding actual quantity formula were to
be entered in cell C2, that formula should be

C2:
=B2*ISNA(MATCH(A2,'OOS.xls'!MNs,0))

Fill C2 down as needed.

If you have to change the initial quantity column entries, use the same
C2 formula to expand the stock table to 3 columns, select the entire
stock table including this added column, and run Data > Filter >
Autofilter. Then filter the added column so it shows all rows with zero
values, i.e., select 0 from the AutoFilter drop-down list for the
column of formulas. Then move to the topmost quantity cell in the
initial quantity column, hold down [Shift] and [Ctrl] keys and press
the [down arrow] key. This should select all visible initial quantity
entries. Type 0, hold down a [Ctrl] key, and press the [Enter] key to
enter 0 in all the selected cells. Run Data > Filter > AutoFilter to
remove the AutoFilter, and clear the column of formulas.
 
S

SteveG

Melissa,

Just alter the ranges and placement of the formula to meet your need
so in column G of your instock worksheet use,

=IF(ISNA(MATCH(A2,oos!$A$2:$A$1479,0)),instock!F2,0)

Copy this down to G6044. The cells in column G will now be populate
with a zero if the model number in column A is found in the "oos
spreadsheet, otherwise, it will be populated with the actual quantit
in column F. You can then copy column F and Paste it's values ove
those in Column G. Then you can sort ascending by column G or filte
to group the "0" values together. I assumed these sheets are in th
same workbook but if they are not, you should have both open and you
references should include the Workbook name, worksheet name an
corresponding cell range within.


HTH

Stev
 
M

melissad

Hey Okay I tried steves way first, Steve all seems good but maybe I
didnt explain what I needed correctly.

Ok this is a sample

OUT OF STOCK SPREADSHEET REPRESENTS JUST THE MODEL
NUMBERS THAT ARE OUT OF STOCK, IT DOESNT GIVE ME HOW MANY THERE ARE, I
COULD ADD A COLUMN B FOR THE AMOUNT IN STOCK, IF THATS NEEDED, SAY ITS
20 IN COLUMN B

MODEL NUMBER
A2 | B0001
A3 | B0008
A4 | B4455
...
A1490|Z2222

Instock Spreadsheet
This represents what Is in my database already and shows what needs to
be changed if any from the out of stock spreadshet

Model Number | Product Quantity
A2 | Z7777 | 20
A3 | b4455 | 20
...
A1490|b0001 |20

Now I need to reflect the model number versus the product quanity that
instock spreadsheet shows versus the out of stock spreadsheet, the
unique identifier is the Model number, question is do i need to add the
field in the Out of stock spreadsheet to have a column for Product
quanity to correspond with the instock spreadsheet?

I hope I made more sense this time.
melissa
 
M

melissad

I meant to type...

Out of stock spreadsheet represents Just the model numbers that are out
of stock, it doesnt give me how many there are, i could add a column B
for the amount in stock, if thats needed, S a y i t s 0 i n c
o l u m n B
 
M

melissad

Steve, Okay I am noticing that when excel doesnt find the model number
on oos it puts the quantity in instock to zero. This isnt good. How do
i fix it?
 
S

SteveG

Melissa,

Could you post the formula as it appears in your workbook? I just
re-created your scenario using the formula I posted and it is working
fine. Only returning zeros if the model number is found, if not the
quantity in the instock.xls.

Did you possibly reverse the "value if true" with the "value if false"
in the IF statement?


Steve
 
S

SteveG

Melissa,

Could you post the formula as it appears in your workbook? I just
re-created your scenario using the formula I posted and it is working
fine. Only returning zeros if the model number is found, if not the
quantity in the instock.xls.

Did you possibly reverse the "value if true" with the "value if false"
in the IF statement?


Steve
 
M

melissad

I guess i cant PM you, How do I send you the spreadsheets with out
posting them here in a public area/
 
S

SteveG

Melissa,

If you post your formula, it would be easier to determine what may be
causing the issue. For starters, let's make sure I am completely
understanding what you are trying to do.

1. oos.xls contains a list of out of stock model #'s
2. instock.xls is your master inventory list
3. Objective - You need to compare your instock.xls or master
inventory with the oos.xls or out of stock list and change the quantity
within the instock.xls of any models that appear in the oos.xls with a
"0".

I assume you are using this to determine your purchasing needs. One
question, is there a reason that your data is in two separate
workbooks? It doesn't seem to need to be but I don't know all of the
underlying factors. Again, try posting the formula exactly as it
appears in your workbook and I can take a look.



Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help required on VBA Coding for Access 2013 0
Finding Last Entry in a Column 3
Need Help with Macro Security 1
VBA to create stock analytics display 1
Word VBA - Automatic change content of a cell in table using dropdown list 5
Import a CSV file HELP 1
Quick/efficient way to VBA subtotal 2
Need Help with a VBA subroutine 0

Top