warehouse- sales problem -statistics.

allen71828 · Oct 26th 2011

There are 2 kind of things to sale and to srorage, of course, so the warehouse is divide in two sides, electronics (probills numbers are evens) and clothes (are odds). Some products have excellent sales rank (from 67% to 100), normal (34 to 66) and poor (1 to 33).
Sales department problem. Show the list of numbers ( from 59), taken 6 at the time that have the rank and type, means two excellent, 2 normal, 2 poor, and 3odds, 3 evens, this list create balance sales. They have to sale everything not only the electronics (evens) or clothes (odds) and the excellents and poors.
example:
probill # 2 - 4- 6---3---5---7
rank 67 100 34 66 1 33 easy first look 2 high,average,low rank
and also odds and even. that' balance
in the market. first I don't know how to write a vba code, and I post only here and here only nowhere-else.

allen71828 · Oct 27th 2011

Re: warehouse- sales problem -statistics.

with all respect, somebody can answer if I can post my problem, and maybe, got some brief answer. thank you,

smuzoen · Oct 27th 2011

Re: warehouse- sales problem -statistics.

I think you could do this with formulas however your explanation of what you want is really really difficult to understand. Perhaps you could upload a sample workbook with some non-sensitive data as well as the desired output that you need. I have read your question a few times and I think you may need to explain exactly what you require very specifically - a sample workbook with sample output/report format would be invaluable.

allen71828 · Oct 27th 2011

Re: warehouse- sales problem -statistics.

Thank you, so much for your attention. With all respect, I would like to modify the code I am working, but I am stumped, days and days, and.... so I am sending what I need; and the workbook I rather by private message. (it's to long here).
[TABLE="class: MsoNormalTable, width: 439"]

[tr]

[TD="width: 66, bgcolor: transparent"]numbers
[/TD]
[TD="width: 63, bgcolor: transparent, colspan: 2"]ranking
[/TD]
[TD="width: 91, bgcolor: transparent"]
[/TD]
[TD="width: 90, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
[/TD]
[TD="width: 44, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

58

[/TD]
[TD="width: 48, bgcolor: transparent"]

92

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 402, bgcolor: transparent, colspan: 6"]rank high from 67 to 100/ middle 34 to 66 and low 1 to 33
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

41

[/TD]
[TD="width: 48, bgcolor: transparent"]

30

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]
[/TD]
[TD="width: 90, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
[/TD]
[TD="width: 44, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

49

[/TD]
[TD="width: 48, bgcolor: transparent"]

71

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 402, bgcolor: transparent, colspan: 6"]the code must generate the combinations of 6 numbers
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

24

[/TD]
[TD="width: 48, bgcolor: transparent"]

65

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 354, bgcolor: transparent, colspan: 5"]with 2 high, 2middle, 2 low, and 3evens, 3 odds.
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

20

[/TD]
[TD="width: 48, bgcolor: transparent"]

50

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]sum range
[/TD]
[TD="width: 164, bgcolor: transparent, colspan: 2"]from 141 to 247
[/TD]
[TD="width: 44, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

21

[/TD]
[TD="width: 48, bgcolor: transparent"]

67

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 299, bgcolor: transparent, colspan: 4"]this range is part of the inputs also.
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

16

[/TD]
[TD="width: 48, bgcolor: transparent"]

98

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]
[/TD]
[TD="width: 90, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
[/TD]
[TD="width: 44, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

17

[/TD]
[TD="width: 48, bgcolor: transparent"]

70

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 457, bgcolor: transparent, colspan: 7"] there are two high rank two middle rank and two low rank totals
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

19

[/TD]
[TD="width: 48, bgcolor: transparent"]

20

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]

12

[/TD]
[TD="width: 90, bgcolor: transparent"]

16

[/TD]
[TD="width: 75, bgcolor: transparent"]

20

[/TD]
[TD="width: 44, bgcolor: transparent"]

17

[/TD]
[TD="width: 55, bgcolor: transparent"]

25

[/TD]
[TD="width: 47, bgcolor: transparent"]

51

[/TD]
[TD="width: 55, bgcolor: transparent"]

141

[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

12

[/TD]
[TD="width: 48, bgcolor: transparent"]

100

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]

12

[/TD]
[TD="width: 90, bgcolor: transparent"]

46

[/TD]
[TD="width: 75, bgcolor: transparent"]

52

[/TD]
[TD="width: 44, bgcolor: transparent"]

49

[/TD]
[TD="width: 55, bgcolor: transparent"]

51

[/TD]
[TD="width: 47, bgcolor: transparent"]

25

[/TD]
[TD="width: 55, bgcolor: transparent"]

235

[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

23

[/TD]
[TD="width: 48, bgcolor: transparent"]

67

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]

46

[/TD]
[TD="width: 90, bgcolor: transparent"]

58

[/TD]
[TD="width: 75, bgcolor: transparent"]

52

[/TD]
[TD="width: 44, bgcolor: transparent"]

21

[/TD]
[TD="width: 55, bgcolor: transparent"]

51

[/TD]
[TD="width: 47, bgcolor: transparent"]

19

[/TD]
[TD="width: 55, bgcolor: transparent"]

247

[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

32

[/TD]
[TD="width: 48, bgcolor: transparent"]

80

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]

32

[/TD]
[TD="width: 90, bgcolor: transparent"]

58

[/TD]
[TD="width: 75, bgcolor: transparent"]

24

[/TD]
[TD="width: 44, bgcolor: transparent"]

23

[/TD]
[TD="width: 55, bgcolor: transparent"]

41

[/TD]
[TD="width: 47, bgcolor: transparent"]

51

[/TD]
[TD="width: 55, bgcolor: transparent"]

229

[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

25

[/TD]
[TD="width: 48, bgcolor: transparent"]

1

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]
[/TD]
[TD="width: 90, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
[/TD]
[TD="width: 44, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

52

[/TD]
[TD="width: 48, bgcolor: transparent"]

60

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 402, bgcolor: transparent, colspan: 6"] there are 3 evens and three odds
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

51

[/TD]
[TD="width: 48, bgcolor: transparent"]

10

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 91, bgcolor: transparent"]
[/TD]
[TD="width: 90, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
[/TD]
[TD="width: 44, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[tr]

[TD="width: 66, bgcolor: transparent"]

46

[/TD]
[TD="width: 48, bgcolor: transparent"]

90

[/TD]
[TD="width: 16, bgcolor: transparent"]
[/TD]
[TD="width: 255, bgcolor: transparent, colspan: 3"]the numbers and ranking I have to input manually.
[/TD]
[TD="width: 99, bgcolor: transparent, colspan: 2"]
[/TD]
[TD="width: 47, bgcolor: transparent"]
[/TD]
[TD="width: 55, bgcolor: transparent"]
[/TD]

[/tr]

[/TABLE]
sorry for any mistake. and thank you again for your time. You are the first person who respond.

smuzoen · Nov 11th 2011

Re: warehouse- sales problem -statistics.

You can get the Binomial co-efficients by using the following - place the number set in Column A

Code

Option Explicit
'categories->High and odd, high and even, mid and odd, mid and even, low and odd, low and even
'define high->67 to 100, mid 34 to 66, low 1 to 33 inclusive range
'Successful output ->sum range ->141 to 247 ->only those sum to these inclusive include in output - NOT INCLUDED
'Output must have 2 high, 2 mid, 2 low and must be 3 even and 3 odd - ONLY CODED HIGH MID LOW
'=================================================================================================
'DEFINE OUTPUT
'=> "Binomial Coefficient" : where n is the number of things to choose from, and you choose r of them
'(No repetition, order doesn't matter)=> n!/r!(n-r)!
'C(n,r)->24 to choose from and we choose 6




Public sumArr As Long, evenNo As Integer, highNo As Integer, midNo As Integer, lowNo As Integer, t As Long
Public testRow As Long
Sub Combinations()
testRow = 0
Dim rRng As Range, p As Integer
Dim vElements, lRow As Long, vresult As Variant, lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of numbers
rRng.Select
p = Range("B1").Value ' How many are picked
'calculate the number of Binomial Coeff
 Dim q As Integer
          Dim b As Double
          
          b = 1
          For q = 0 To p - 1
                    b = b * (lastRow - q) / (p - q)
          Next q
MsgBox "No of Non repeating combinations is - " & b
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Columns("C").Resize(, p + 15).Clear
Call CombinationsNP(vElements, p, vresult, lRow, 1, 1)
End Sub




Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Integer, k As Integer
On Error GoTo errHandler
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        'test array vresult for conditions - try 2 High, 2 mid 2 low
        For k = LBound(vresult) To UBound(vresult)
        If vresult(k) >= 67 And vresult(k) <= 100 Then highNo = highNo + 1
        If vresult(k) >= 34 And vresult(k) <= 66 Then midNo = midNo + 1
        If vresult(k) >= 1 And vresult(k) <= 33 Then lowNo = lowNo + 1
       ' If vresult(k) Mod 2 = 0 Then evenNo = evenNo + 1
        sumArr = sumArr + vresult(k)
        Next
        If highNo = 2 And midNo = 2 And lowNo = 2 And sumArr > 300 And sumArr < 320 Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
       End If
       testRow = testRow + 1
       Range("k" & testRow).Resize(, p) = vresult
       End If
       If iIndex <> p Then
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
        End If
 
        sumArr = 0
        evenNo = 0
        highNo = 0
        midNo = 0
        lowNo = 0
Next i




Exit Sub
errHandler:
MsgBox "Error has occured - error no " & Err.Number & " - " & Err.Description
End Sub

Display More

montecarlo2012 · Apr 2nd 2012

Re: warehouse- sales problem -statistics.

how can I attach a file like this. I tried and I couldn't. where is the instruction for that.

Wigi · Apr 10th 2012

Re: warehouse- sales problem -statistics.

"Go advanced" when writing a reply.
Scroll down the page.
There you manage (add) attachments.

warehouse- sales problem -statistics.

Participate now!

Share