About to start a new project & looking for advice

Pivot tables. That's the perfect Excel approach for this kind of data analysis.

I need to work out how to get the "answers" expressed line-by-line, showing the total quantity along with the detail column values of size, length, and grade, so that the "answers" can be exported as a TXT file to produce the final "pick list" for loading the truck.

Hi there
I might be being obtuse but surely the OBVIOUS way to do this is via an EXCEL spreadsheet as others have already posted.

Plenty of examples -- just google.

Should take you all of 90 Secs to finish it.


Excel exports to Text / CSV etc etc easily.

Actually you could be even more sophisticated -- download to say a Mobile smart phone or direct to the truck drivers "Way bill" -- this sort of app is becomming more and more into use.


Cheers
jimbo

Thank you everyone for all the great advice.

I will try to work on this myself in excel, for a lesson if nothing else. For BriceH and anyone else interested I have attached a sample order. I have gone into much greater detail than before. So here it is to the best of my Sunday morning memory. I will post an EXACT .stk file tomorrow night. sampleorder.txt

McP2143 said:

I will try to work on this myself in excel, for a lesson if nothing else.

Google "Excel pivot table" and take one of the many tutorials. This is the right approach to getting the answers (I have that much done).

Haven't had time yet to work on presenting the answers in a user-friendly set of cells to facilitate exporting the answers (with underlying details as qualifiers) to produce the final list.

For BriceH and anyone else interested I have attached a sample order. I have gone into much greater detail than before. So here it is to the best of my Sunday morning memory. I will post an EXACT .stk file tomorrow night.

I'll use this sample as a real prototype.

Take just the pick list section TXT from your sheet... remove blank lines. Import it into an Excel spreadsheet. It is "delimited" using "commas". The rest of the dialog wizard you can just OK/next to.

I'd recommend starting from the prototype, and deleting the old detail data rows so that just the title row remains. When you import the next data it will be placed in the rows following the title. The presence of the title row is crucial to the pivot table concept.

Then, highlight the complete set of cells that should be part of the table, starting from the first title cell (Quantity) and running through the last detail row cell (Grade).



Then, click on the "Insert" item on the menu bar, and push the "PivotTable" button at the left of the "Tables" group on the left side of the button bar. The selected range from your just imported will appear in the table/range area, and the "new worksheet" radio button will be pre-checked. Just push OK.

The pivot table area (shown on the left side of the presentation) will initially be empty. The pivot table field list from which you can select the values to be "spun" are shown in the upper-right. Magically, Excel knows that size, length and grade are "row labels" so if you check those items in the "choose fields to add to report" they will appear down in the "row labels" quadrant area.

And if you then check the "quantity" field, it will be placed in the "sum values" quadrant area.

And magically, on the left side, the pivot table will appear... with all of the detail row values "spun" and "totaled", and it will look like this:



This matches your sample results (except that your 2x4, 6-0-0, #2 is wrong... it should be 4, not 3 as you show):

111024A Pick List
1,2x6,14-0-0,2100
2,2x6,14-0-0,1650
2,2x6,10-0-0,2100
6,2x4,16-0-0,2100
2,2x4,14-0-0,2100
1,2x4,14-0-0,#2
4,2x4,12-0-0,#2
3,2x4,6-0-0,#2

Since the table itself will be of varying length, you should then select the complete table range (starting from the first row label cell to the Grand Total number, push the Page Layout item on the Menu bar, and then push the Print Area -> Set print area item in the Page Setup group of buttons. You can experiment with printing the table, and you can also push the "additional page setup" arrow (at the right side of the "Page Setup" bar) to get additional printing options for titling as you see fit.

Should get you started.

I believe if you save your final table spreadsheet, then the next time you open it up and delete the existing data rows, and import the new data rows onto the data sheet, that the previously established and checked "row labels" and "sum items" will be all set from before, and the pivot table sheet should instantly rebuild itself with the new pick list row data. You should not have to do anything more, other than probably redefine the new print area and print it.

McP2143 said:

I did this in Excel! Thanks dsperber.

Yes, you can drag labels to the "columns" quadrant, as I see you've done, to make the presentation 2-dimensional.

Also, notice that both the "row labels" and "column labels" columns in the table have the dropdown arrow controls on them, indicating that they can be selected for "data filter". In other words, if you wanted to present just a subset of the total pick list, for a particular board size or length or grade, you could click on the dropdown arrow, click on the "select all" item to UNCHECK all of them, and then just check that one (or more) subset value you were looking particularly for.

In other words, you can produce subsets of the table, for specific values in each of the fields... spun and totaled/sub-totaled by just the values you're interested in spinning and printing.

Also, the rows and columns of the presented pivot table are really just an ordinary spreadsheet, like any other spreadsheet. So you can "format" the title heading row, set fonts and colors, set alignment (e.g. "center"), etc. to make it look and print better.

for example, I see that the "#2" grade got left justified in the title, whereas the numeric quantity underneath the column heading got right justified. You might want to CENTER everything in the table, both column headings in the title row as well as contents, just to improve readability.

Any thoughts on how to quickly get dozens of these printed?

File -> Print Preview, to enter the print preview dialog for the table. Then you can get into Page setup, set up headers and footers for printing, center it as you want, etc.

You can then push the PRINT icon button and print as many as you want.

Again, I believe if you SAVE the resulting XLSX workbook then everything you've done using the last data set will be saved, and will be instantly applicable to the next day's set of data... including print setup, etc. Tomorrow, if you first delete the previous set of data rows (under the column heading row), and then select the four columns A-D in the next row (row 2), and then import Data -> from text, then the new data will be imported and placed into rows 2-n, leaving the column widths and separator positions, title row, colorization, etc., just as they were. You're only inserting brand new data, and the generated pivot table on the other sheet will instantly rebuild itself.

Simplicity is the key to making this happen for me on a daily occurrence.

You'll eventually figure out a daily workflow to get each day's data imported, and then print out whatever you want from that day's pivot table.

Excel. It's your friend, for lots more than you might imagine.

Here are some examples of full data files that I need to pull the picklist from.

Note: The original file extension was .stk I changed it to .txt for upload

Sample Picklists :110007G.txt

110007D.txt

110087k.txt

Ps. I have gotten quite a bit out of excel and am now examining BriceH's python script closer. I like it! It has great potential to do all that I want very efficiently.

McP2143 said:

110087k.txt - 110087k.txt

Or, 2-dimensional:

I made a couple adjustments to briceh's program.

Drag & Drop

Still working on it but this works perfectly fine.

Picklist Counter Drag And Drop File.rar