Help me save my job

[smarf]

I have to do a simple task...but not simple for me!

I need to take an incoming list of wood parts that have

(1) quantity (2) part number (3) thickness (4) width (5) length (6)
material

and import them into Excel via a csv. The list is generated in
Autocad.

Once in Excel, I need to sort by thickness, width, and length. I have
no problem with that. My trouble starts here:

I need to generate a separate printed report for each type of material
and thickness. Additionally, I need give the sum of the lengths for
all the parts within that separate report.

For example, the csv might have

2, ba1234, .75, 2.25, 72, oak
5, ba1227, .5, 2.5, 50, oak
4, ba1256, 1, 2, 60, 60, pine

where the first line is 2 boards with part number ba1234 that are 3/4
inch thick, 2-1/4 inch wide, and 72 inches long.

I would need to have Excel separate out the oak parts from the total
import and put them in list form longest to shortest under a heading
that said "3/4 inch solid oak" and give me the total inches of 144. I
would need a separate report, preferably on separate pages, for each
wood of a given thickness.

I can actually pay someone for your help if need be via paypal or
check. I am sure I will learn this in time, but not in time to save
my job! Monday is my deadline...Thanks for your help...

Patrick

 

[Zone]

Patrick,
I should be able to help you with this. If you want to proceed, answer
this message, and we'll make arrangements to exchange files. James

 

[franksas]

Hello, Patrick.

I believe you can do this.

Try using a PivotTable with a PivotChart report. (If you can figure out
how to import a .csv, you can do PivotTables.)

Click Data > PivotTable and PivotChart Report...

Then let the Wizard walk you through it. A few hours of experimentation
should get you on the right track.

You can do it. You have the whole weekend to experiment and get it
straight. Go to the nearest bookstore and buy an Excel book. Make sure it
covers PivotTables and PivotCharts. Also, use the Windows Help files.
Also, there are tutorials you can download from Microsoft about PivotTables
and PivotCharts.

I promise you, it is really not that hard.

I assume that the two 60s on the "pine" data are a typo, and that you really
only meant there to be one "60."

If the two 60s are not a typo, you have serious issues that will require
serious help. It would mean that your data are not in records that are
consistent in content from one record to the next and that, therefore, some
special effort will be required. Maybe you would have to insert a "dummy"
field that would just be null for the non-pine records.

I sent you an email containing my email addres and the other contents of
this reply.

 

[smarf]

Hello, Patrick.

I believe you can do this.

Try using a PivotTable with a PivotChart report. (If you can figure out
how to import a .csv, you can do PivotTables.)

Click Data > PivotTable and PivotChart Report...

Then let the Wizard walk you through it. A few hours of experimentation
should get you on the right track.

You can do it. You have the whole weekend to experiment and get it
straight. Go to the nearest bookstore and buy an Excel book. Make sure it
covers PivotTables and PivotCharts. Also, use the Windows Help files.
Also, there are tutorials you can download from Microsoft about PivotTables
and PivotCharts.

I promise you, it is really not that hard.

I assume that the two 60s on the "pine" data are a typo, and that you really
only meant there to be one "60."

If the two 60s are not a typo, you have serious issues that will require
serious help. It would mean that your data are not in records that are
consistent in content from one record to the next and that, therefore, some
special effort will be required. Maybe you would have to insert a "dummy"
field that would just be null for the non-pine records.

I sent you an email containing my email addres and the other contents of
this reply.

Oops...you're right...that was a typo, thank goodness! Thank you for
this reply...off to the bookstore.

 

[Showbear]

Patrick,

Your problem sounds remarkably like one I just wrestled with. I didn't
think that Pivot Tables (which someone else has suggested) were appropriate
for it, but that's maybe my own ignorance and I'll explore them further now.
In case they don't meet your needs, I've done the following to meet my (and
apparently your) needs.

Our data about work done for clients is manually entered in rows into a
GeneralEntry worksheet, yours might be imported there. I created a client
worksheet for each client (or type of wood). The client sheet contains ALL
of the data from GeneralEntry by simply linking the cells (i.e., the formula
for cell A10 in the client sheet is =GeneralEntry!A10 so that it displays
whatever is in GeneralEntry's cell A10). Then the rows are filtered on the
client sheet using Data, Filter, AutoFilter to display just the rows for
that client (or type of wood). Then, to get sums of certain columns, I used
the SUMIF function to add values in the rows matching a criteria (client
name, or type of wood).

I'm not proud of this - it seems kludgy, is prone to breakdown if users
delete rows, and would be a maintenance headache if clients (or types of
wood) change much. But the AutoFilter feature, once set up by you, is
fairly user-friendly. Users can just click on an arrow at the top of a
column, select the criteria for that column from all possible values, and
only the rows matching that criteria are displayed. Having now done all the
work creating separate client sheets for our needs, I'm re-thinking and
wondering if we can just use the one sheet and have the users produce client
reports by selecting the autofilter criteria themselves.

So, if Pivot Tables won't meet your needs, this approach might. Hope this
helps.