bar inventory

[Guest]

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

 

[Max]

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 ..

 

[Max]

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