Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to look up a column and paste results in another column
I've tried to use Vlookup for this but was unable to get it working.
Here's what I need: In column 3 I have: dog dog1 cat cat2 I'm trying to get an inventory management tool by looking at column 3 and subtracting 1 from the previous row, so I would have: Column 3 Column 4 for dog Column 5 for dog1... 100 dog 99 dog1 0 cat 0 cat2 0 dog 98 So, if I have "dog" in row 3 then I get a formula to look up "dog" then subtract 1 from the previous row (or previous valid number, maybe not 0) and give me the result, and if it is not "dog" it'd show a 0. Hope I was clear enough... PLEASE HELP! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to look up a column and paste results in another column
I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and
down is: =$D$2-COUNTIF($C$3:C3, "dog") Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1 etc, you can use the following formula in D3 and copy accross and down: =$D$2-COUNTIF($C$3:C3, D$1) HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to look up a column and paste results in another column
If having a zero (0) was not important, you could set it up like this:
Criteria Formula Result 100 100 dog =IF(A3="Dog",B2-1,0) 99 Dog1 =IF(A4="Dog",MIN($B$2:B3)-1,"0") 0 Cat =IF(A5="Dog",MIN($B$2:B4)-1,"0") 0 Cat2 =IF(A6="Dog",MIN($B$2:B5)-1,"0") 0 dog =IF(A7="Dog",MIN($B$2:B6)-1,"0") 98 "DM" wrote: I've tried to use Vlookup for this but was unable to get it working. Here's what I need: In column 3 I have: dog dog1 cat cat2 I'm trying to get an inventory management tool by looking at column 3 and subtracting 1 from the previous row, so I would have: Column 3 Column 4 for dog Column 5 for dog1... 100 dog 99 dog1 0 cat 0 cat2 0 dog 98 So, if I have "dog" in row 3 then I get a formula to look up "dog" then subtract 1 from the previous row (or previous valid number, maybe not 0) and give me the result, and if it is not "dog" it'd show a 0. Hope I was clear enough... PLEASE HELP! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to look up a column and paste results in another colum
Is it possible to add a second variable to the formula to also look for
"dog1" at the same time? How do I do that? Something like =$D$2-COUNTIF($C$3:C3, "dog"; "dog1") Thank you! "vezerid" wrote: I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and down is: =$D$2-COUNTIF($C$3:C3, "dog") Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1 etc, you can use the following formula in D3 and copy accross and down: =$D$2-COUNTIF($C$3:C3, D$1) HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to look up a column and paste results in another colum
If your list only has "Dog" or "Dog1" then try:
=IF(OR(D3="Dog",D3="Dog1"),($F$2-COUNTIF($D$3:D3,"dog*")),0) Otherwise is you have more types of "Dog" then use: =IF(OR(A3="Dog",A3="Dog1"),($B$2-(COUNTIF($A$3:A3,"dog")+COUNTIF($A$3:A3,"dog1"))), 0) "DM" wrote: Is it possible to add a second variable to the formula to also look for "dog1" at the same time? How do I do that? Something like =$D$2-COUNTIF($C$3:C3, "dog"; "dog1") Thank you! "vezerid" wrote: I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and down is: =$D$2-COUNTIF($C$3:C3, "dog") Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1 etc, you can use the following formula in D3 and copy accross and down: =$D$2-COUNTIF($C$3:C3, D$1) HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to find text string in a column and paste data in another | Excel Discussion (Misc queries) | |||
Paste results consecutively within column (based off other worksheet) | Excel Worksheet Functions | |||
Effective method to paste array formula | Excel Worksheet Functions | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
Entering Same Formula to A Full Staring from Say Row 5 Column..... | Excel Worksheet Functions |