#1   Report Post  
peets
 
Posts: n/a
Default bar inventory

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
bar inventory Peets Excel Worksheet Functions 3 April 11th 05 10:10 PM
how do I setup invoicing to deduct from inventory? Splash New Users to Excel 1 March 17th 05 04:31 PM
Days of sales in inventory template jr Setting up and Configuration of Excel 0 March 14th 05 05:11 PM
Where can I find an inventory template for excell? Conan Excel Discussion (Misc queries) 1 March 8th 05 04:33 PM
Inventory Pricing - running averages Inventory Question Excel Discussion (Misc queries) 2 January 3rd 05 05:36 PM


All times are GMT +1. The time now is 05:39 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"