Hey guys, I have a database that want to collect information from but only from certain places.
The database has a Run No (Runs 1 - 10 for example) and within each run number is a stand size (we install stands into stores). What we want to do is collect a summary of information based on this info. So we want to know how many stand size 1, 2, 3 and 4 there are in Run 1 and do the same for all the runs within the table.
Can anyone help me trying to figure this one out?
Perhaps a Pivot Table?
Example:
With this data in A1:C9
Then....from the Excel Main Menu...
RUN SIZE QTY 1 1 2 1 2 3 1 3 3 1 4 5 2 2 2 2 2 3 2 3 1 2 5 1
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button
ROW:
-Drag the RUN field here
-Drag the SIZE field here, too
DATA:
-Drag the QTY field here
If it doesn't list as Sum of QTY...dbl-click it and set it to SUM
Click [OK]
Select where you want the Pivot Table...and you're done!
That will list the sum of each QTY for each SIZE within a RUM.
The end result of my example is this:
To refresh the Pivot Table, just right click it and select Refresh DataSum of Qty RUN SIZE TOTAL 1 1 2 2 3 3 3 4 5 2 2 5 3 1 5 1
Is that something you can work with?
(Post back if you have more questions)
Hi thanks for the reply but in our table there will not be a quantity. The table will be set up in the following kind of format.
Run:Standsize
1:1
1:3
1:2
2:3
2:1
2:1
3:2
3:2
3:2
So in run 1 there is a size 1 x size 1 stand, 1 x size 3 stand and 1 x size 2 stand.
Run 2 has 1 x size 3 stand and 2 x size 1 stand
Run 3 has 3 x size 2 stand.
Obviously this list will be a lot bigger (from 7 - 60 mixed stands per run) and we just need a summary of how many of each stand are in each run.
Hi,
Ron's solution is what you need.
Apply everything described except the QTY part.
Instead, drag your " Size" field to the Data Field
This will do the trick
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks