Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I am setting up a bar inventory sheet.... The information is recorded in the
order it is on the shelf (scotch, vodka, Argentinean reds, California reds.......) however, the shelves are not organized by purveyor. I would like to create a second sheet that separates the data by purveyor, for easier organization for ordering. Currently the Inventory sheet is set up as column A is the purveyor, Column B is the item, and Column C is the amount in inventory. i would like a sheet that contains the information from column B and column C categorized under the appropriate purveyor from column A....I would prefer one sheet with different headings...but individual sheets for each purveyor is okay. Thank You |
#2
![]() |
|||
|
|||
![]()
Here's a formulas approach to play around with ..
Assume the sample table below is in Sheet1, cols A to C, data from row2 down Purveyor Item Amount Name1 Scotch 145 Name2 ArgRed 125 Name2 CalReds 138 Name3 Vodka 140 Name1 Vodka 113 Name1 Scotch 119 Name3 CalReds 105 Name2 ArgRed 124 etc Using empty cols to the right, say from col E onwards, list in E1:G1, the names of the purveyors: Name1, Name2, Name3 .. Put in E2: =IF($A2="","",IF($A2=E$1,ROW(),"")) Copy E2 across to G2, then fill down by the max expected number of rows of data in cols A to C, say down to G200? In a new sheet named: Name1 ------------------------------* Let's reserve cell A1 to pull in the sheetname Put in A1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) (This'll extract the sheetname into A1. But you need to save the file first.) Paste the same col headers from Sheet1 into A2:B2, viz.: Item, Amount Put in A3: =IF(ISERROR(SMALL(OFFSET(Sheet1!$D:$D,,MATCH($A$1, Sheet1!$E$1:$G$1,0)),ROWS( $A$1:A1))),"",INDEX(Sheet1!B:B,MATCH(SMALL(OFFSET( Sheet1!$D:$D,,MATCH($A$1,S heet1!$E$1:$G$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1!$ D:$D,,MATCH($A$1,Sheet1!$E $1:$G$1,0)),0))) (a normal ENTER will do) Note: You'd need to correct / restore the inadvertent line wraps / line breaks when you copy paste the above formula into A3 Copy A3 across to B3, then fill down by as many rows as was done in Sheet1, i.e. down to B201 You'll see that cols A to B (in row3 down) will auto-return the "filtered" rows from Sheet1 for the purveyor: Name1, i.e. for the sample data-set above, it'll appear as: Name1 Item Amount Scotch 145 Vodka 113 Scotch 119 (rest are blank [""] rows) Now just duplicate / make a copy of the sheet: Name1, rename it as: Name2 and you'll get the "filtered" rows for Name2, viz.: Name2 Item Amount ArgRed 125 CalReds 138 ArgRed 124 (rest are blank [""] rows) Repeat the sheet duplication / renaming for Name3, and you'll get: Name3 Item Amount Vodka 140 CalReds 105 (rest are blank [""] rows) And so on .. Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "peets" wrote in message ... I am setting up a bar inventory sheet.... The information is recorded in the order it is on the shelf (scotch, vodka, Argentinean reds, California reds.......) however, the shelves are not organized by purveyor. I would like to create a second sheet that separates the data by purveyor, for easier organization for ordering. Currently the Inventory sheet is set up as column A is the purveyor, Column B is the item, and Column C is the amount in inventory. i would like a sheet that contains the information from column B and column C categorized under the appropriate purveyor from column A....I would prefer one sheet with different headings...but individual sheets for each purveyor is okay. Thank You |
#3
![]() |
|||
|
|||
![]()
Another way which may suffice is simply to use a Pivot Table
Taking the same sample table below in Sheet1, cols A to C, data from row2 down Purveyor Item Amount Name1 Scotch 145 Name2 ArgRed 125 Name2 CalReds 138 Name3 Vodka 140 Name1 Vodka 113 Name1 Scotch 119 Name3 CalReds 105 Name2 ArgRed 124 etc Put the cursor anywhere within the source table Click Data Pivot Table Report Next Next In step 3 of the wizard: Drag Purveyor and drop within ROW area Drag Item and drop within ROW area (below Purveyor) Drag Amount and drop within DATA area (It'll appear as Sum of Amount) Click Finish The PT will be created in a new sheet to the left of Sheet1, and appear as: Sum of Amount Purveyor Item Total Name1 Scotch 264 ...............Vodka 113 Name1 Total 377 Name2 ArgRed 249 ...............CalReds 138 Name2 Total 387 Name3 CalReds 105 ...............Vodka 140 Name3 Total 245 Grand Total 1009 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "peets" wrote in message ... I am setting up a bar inventory sheet.... The information is recorded in the order it is on the shelf (scotch, vodka, Argentinean reds, California reds.......) however, the shelves are not organized by purveyor. I would like to create a second sheet that separates the data by purveyor, for easier organization for ordering. Currently the Inventory sheet is set up as column A is the purveyor, Column B is the item, and Column C is the amount in inventory. i would like a sheet that contains the information from column B and column C categorized under the appropriate purveyor from column A....I would prefer one sheet with different headings...but individual sheets for each purveyor is okay. Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
bar inventory | Excel Worksheet Functions | |||
how do I setup invoicing to deduct from inventory? | New Users to Excel | |||
Days of sales in inventory template | Setting up and Configuration of Excel | |||
Where can I find an inventory template for excell? | Excel Discussion (Misc queries) | |||
Inventory Pricing - running averages | Excel Discussion (Misc queries) |