Solved: Thinking outside the Square

[Aussiebear]

Those of you, who have been following my erratic postings would remember the Grain Sampling & Residue Testing workbook concept that I've been working on. ( Yes Yes I know, its a bit like watching "Days of our Lives" - absolutely riveting stuff, I hear you say).

Some of you, might even recall me saying that the Company has decided to "borrow" ( Steal is such a harsh word isnt it), the concept and transpose it into a database. Boo, hiss, Thumbs down etc for the bad guys!!!

I'd like to put one last effort into the workbook and was wondering if the following is possible.

On the first sheet of the example attached is copy of the worksheet "Whiteboard" which belongs to the workbook "Grain Sampling & Residue Testing". A the end of each day, I manually transpose onto a piece of paper all those non sampled Vendors who need to have samples sent away for analysis. Examples are those vendors in Buckets 1, 3, 4, 10, 11, 19 etc.
I then close this workbook and open another workbook SD.xlt, which has the single unnamed sheet which is shown in the attached workbook as "Sample Dispatch". I then manually enter in the details, grouping graintypes and test types and where possible no less than 3 vendors in any one group, as per the Company's "Thou shall not disobey this rule" request.

On a side note, the Company I work for, uses highly motivational techniques, like "daily whippings" & "No. 9 Boots", if you breech any of these "Thou shall not disobey this rule" guidelines.... but I digress somewhat.

So here comes the outside the square bit...

Is it possible therefore to have a filtered list transferrable from one workbook to another? If not, is it then possible for me to have the Sample dispatch worksheet attached to the initial workbook, so that when I have completed the sheet I can save the sheet as a seperate workbook as SD ( sequential number).xls?

[Bob Phillips]

Of course it is ... so get on with it.

[Bob Phillips]

Oops ... I was forgetting that I don't work for your company. Isn't it just a simple matter of running a macro that reads the Whiteboard sheet, looks for the N's and copies the details into Sample Dispatch sheet?

[Paul_Hossler]

Q: what is the selection criteria? Doesn't seem to be just "N" in Col E --buckets 10, 11 have "Y"

Q: what would the SD sheet look like with data filled in from Whiteboard? Don't understand the "No less than 3 ..."

[Aussiebear]

Bob, ROFL.... Macro..... How? For the first question or the Second?


Paul, Please find attached what the Sample Dispatch would look like. The vendors requiring a sample dispatch are as you suggested, those who have a "Y" in any test which is unaccompanied by an "S" value. Those who have a "Y" with any other value have already been tested and the value represents a result.

[johnske]

Of course it is ... so get on with it.

Sun - get on with it
Mon - get on with it
Tues - get on with it
Wed - get on with it
Thur - get on with it
Fri - get on with it
Sat - get on with it and... for good measure

[Paul_Hossler]

Aussiebear, Here's at least a start.

The SD template sheet is in a hidden sheet that is copied to make a new WB
The required data is copied from Whiteboard to the new WB
Named ranges in Whiteboard WB keep track of Dispatch number


Paul


[VBA]
Option Explicit
Const gsTitle As String = "Make an Sample Dispatch Sheet"
Const gsVer As String = "ver 1.00, 2007/05/09"
Dim aOutputRows As Variant

Sub MakeSD()
Dim wbWhiteboard As Workbook, wbSD As Workbook
Dim wsWhiteboard As Worksheet, wsSD As Worksheet, wsSDH As Worksheet
Dim rWhiteboard As Range, rRow As Range
Dim rCompNum As Range, rDispatchNum As Range
Dim iWhiteBoard As Long, iSD As Long

'ask user
If MsgBox("Do you really want to make a sample dispatch sheet?", _
vbQuestion + vbOKCancel, gsTitle & " (" & gsVer & ")") = vbCancel Then
Exit Sub
End If

'setup
Application.ScreenUpdating = False

Set wbWhiteboard = ThisWorkbook
Set wsWhiteboard = wbWhiteboard.Worksheets("WhiteBoard")
Set wsSDH = wbWhiteboard.Worksheets("Sample Dispatch")

'unhide SD and copy to make new WB
With wsSDH
.Visible = xlSheetVisible
.Copy
.Visible = xlSheetHidden

Set rCompNum = [CompNum]
Set rDispatchNum = [DispatchNum]

Set wbSD = ActiveWorkbook
Set wsSD = ActiveSheet

wsSD.Cells(3, 3).Value = Format(Now, "mm/dd/yyyy")
wsSD.Cells(1, 1).Select
End With

'not elegant, but faster
aOutputRows = Array( _
7, 8, 9, 10, 11, _
14, 15, 16, 17, 18, _
21, 22, 23, 24, 25, _
28, 29, 30, 31, 32, _
35, 36, 37, 38, 39, _
42, 43, 44, 45, 46, _
49, 50, 51, 52, 53)

'look for data to move
Set rWhiteboard = wsWhiteboard.Cells(1, 1).CurrentRegion
Set rWhiteboard = rWhiteboard.Cells(2, 1).Resize(rWhiteboard.Rows.Count - 1, rWhiteboard.Columns.Count)

iSD = 0
For Each rRow In rWhiteboard.EntireRow.Rows
With rRow
If .Cells(1, 5).Value = "N" And .Cells(1, 11).Value = "" Then
wsSD.Cells(aOutputRows(iSD), 3).Value = .Cells(1, 2).Value
wsSD.Cells(aOutputRows(iSD), 4).Value = .Cells(1, 3).Value
wsSD.Cells(aOutputRows(iSD), 5).Value = .Cells(1, 4).Value
wsSD.Cells(aOutputRows(iSD), 6).Value = .Cells(1, 1).Value

'see if this is a starter row
If iSD Mod 5 = 0 Then
'update the Whiteboard stored value
wsWhiteboard.[DispatchNum].Value = wsWhiteboard.[DispatchNum].Value + 1
wsSD.Cells(aOutputRows(iSD), 2).Value = wsWhiteboard.[DispatchNum].Value
End If

iSD = iSD + 1

If iSD = 35 Then
Call MsgBox("SORRY!!! You can only do 35", vbCritical + vbOKOnly, _
gsTitle & " (" & gsVer & ")")
Exit Sub
End If
End If
End With
Next
End Sub
[/VBA]

[Bob Phillips]

Bob, ROFL.... Macro..... How? For the first question or the Second?

It would be for the firt.

It would be a simple loop that tests the identifier column, and when it is the target value, just copy the details across to the Dispatch sheet, next free item.

[Aussiebear]

Sun - get on with it
Mon - get on with it
Tues - get on with it
Wed - get on with it
Thur - get on with it
Fri - get on with it
Sat - get on with it and... for good measure

Stone the flaming crows... Just where abouts in Brisbane do you work John?

Not near Dinmore I hope!

[Aussiebear]

Aussiebear, Here's at least a start.

The SD template sheet is in a hidden sheet that is copied to make a new WB
The required data is copied from Whiteboard to the new WB
Named ranges in Whiteboard WB keep track of Dispatch number

Excellant.




[vba]

'look for data to move
Set rWhiteboard = wsWhiteboard.Cells(1, 1).CurrentRegion
Set rWhiteboard = rWhiteboard.Cells(2, 1).Resize(rWhiteboard.Rows.Count - 1, rWhiteboard.Columns.Count)

iSD = 0
For Each rRow In rWhiteboard.EntireRow.Rows
With rRow
If .Cells(1, 5).Value = "N" And .Cells(1, 11).Value = "" Then
wsSD.Cells(aOutputRows(iSD), 3).Value = .Cells(1, 2).Value
wsSD.Cells(aOutputRows(iSD), 4).Value = .Cells(1, 3).Value
wsSD.Cells(aOutputRows(iSD), 5).Value = .Cells(1, 4).Value
wsSD.Cells(aOutputRows(iSD), 6).Value = .Cells(1, 1).Value
[/VBA]

Here's where it needs to be different. The actual determing factor of whether a vendor needs to be sampled becomes a little more complex.
Rather than using the "N" value we need to be looking at any "Y" which is accompanied by a "" in the next cell of the same row. Whilst the code works for those requiring a first test, there will be times when a Vendor needs to be sampled for 1st (Col K), 250 tons (Col M), 500 tons(Col O), 1000(Col Q), 1500 tons (Col S) and 2000tons (Col U).

Further more there is a requirement to keep same grain types together ( All Sorghum, All Barley, All Wheat, All Corn and All Triticale), as well as keeping similar tests in similar groups.

This is why last night at 1.30, when all good employees should have been in bed, snoring their heads off, I had this "vision".

Of a filtered list which appeared as a floating form on top of the Sample Dispatch sheet. I was then able to pick and choose off the list by dragging the preferred row of the filtered data onto the Sample Dispatch sheet in a preferred position. By being able to do this "Whizz Bang piece of magic" I could overcome all the rules set by the Company, when determing who, what and when, had their grain sampled.

See, I did drop a hint that this was going to be somewhat "Outside the Square".

[johnske]

...On a side note, the Company I work for, uses highly motivational techniques, like "daily whippings" ...

Brisbane? You need to drive north from there for a day or so to get to paradise...

[Aussiebear]

Firt??? Is that American for First?

[Paul_Hossler]

Boy, you're lucky this is a boring meeting

Added VBA in 3 different places -- sort by grain type, go across, sort by bucket number

BTW, some of your data is a little messy -- "Y " -- so I had to add vba to cover that also Trim( ...) usually

[VBA]
'sort by Grain type
wsWhiteboard.Cells(1, 1).CurrentRegion.Sort Key1:="Grain Type", _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


For iWhiteBoard = 10 To 20 Step 2

If Trim(.Cells(1, iWhiteBoard).Value) = "Y" And Trim(.Cells(1, iWhiteBoard + 1).Value) = "" Then



'sort by Bucket Number
wsWhiteboard.Cells(1, 1).CurrentRegion.Sort Key1:="Bucket Number", _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


[/VBA]

[Aussiebear]

G'day Paul,

When I tried your code, it still puts more than one grain type in a composite group. I am not allowed to mix grains or test types.

There should be 4 groups on the dispatch. There are two Barleys on 1st test (Buckets 10 & 11). There are ten Sorghums on 1st tests ( Buckets 1, 3, 19, 41, 65, 66, 73, 81, 116 & 130 - Which will account for two groups) and finally there is 1 Sorghum on a 250 test (Bucket 4).

The other issue is the Vnedor Dec number is missing its true length.

It needs to read (for 1st tests ) the vendor dec number plus "-01", and for 250 tons tests, vendor dec number plus "-250" and so on for each of the required tests.

[Paul_Hossler]

Didn't understand some of the things you were looking for

I'll work in it soon

I'm thinking "6 pack of Fosters"

(Just kidding)

[Aussiebear]

I'm thinking "6 pack of Fosters"

(Just kidding)

Fosters?? That's another name for sheep dip.

[Bob Phillips]

We call it urine over here in the land of real beer!

[Aussiebear]

Tell me Bob, which beer shares a common thread with urine....


English beer cause its body warm. So you leave our "sheep dip" alone.

[Paul_Hossler]

Actually, I'm sorry I brought the subject of beer up --

Here's a updated version

Basically the same, added some sorts, and -01, -250's etc.

Broke the groupings by grain

Formatting of the output sheet is barebones

Been an interesting little project, let me know what you think

Paul

[johnske]

Of course you'd know why Queenslanders named their beer XXXX (Fourex) don't you? - Cos they didn't know how to spell "beer"