Classify data by simultaneously validating 3 parameters

[shahishaan]

Hi,

I am facing a live problem and hoping that someone can help me. I have 3 trucks and bunch of vegetables and fruits and I have to distribute these products in each of these trucks. The parameters that I have to check are Type, Quality and Availability.

Explained with the screenshot:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Quality	Item	Type	Availability	Truck A	Truck B	Truck C
6	Apple	Fruit	Yes	Spinach	Broccoli	Cauliflower
8	Broccoli	Vegetable	Yes	Potato	Apple	Watermelon
4	Potato	Vegetable	Yes	Litchi
4	Mango	Fruit
5	Cauliflower	Vegetable	Yes
3	Litchi	Fruit	Yes
7	Watermelon	Fruit	Yes
9	Spinach	Vegetable	Yes

<tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Flow:
1) Truck A,B & C is where the output is required (Have entered the desired output)
2) First check is to consider only those rows where 'Availability' column says 'Yes'
3) Next, Only those Vegetable which are 'Yes' have to be assigned to trucks from A to C in the descending order of the 'Quality'
4) Now Fruits which are 'Yes' have to be assigned to trucks from A to C in the ascending order of the 'Quality'

P.S: The alternating assignment due to quality is to make sure that each truck has almost equal distribution of quality of products.
I hope I could explain it properly. I tried searching for this on google or from existing threads but I am not sure whether I knew the right keywords to search for.

Thanks for help in advance!

 

[MickG]

Try this:-
"Not quite the same result, but " !!!
Results in columns "E to G"

[COLOR="Navy"]Sub[/COLOR] MG18Jun43
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, R [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] St, Sa [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
Ray = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 4)
[COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Ray(n, 4) = "Yes" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
            Dic.Add Ray(n, 1), n
            .Add Ray(n, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
ReDim nray(1 To Dic.Count, 1 To UBound(Ray, 2))
.Sort: .Reverse
R = .toarray
[COLOR="Navy"]For[/COLOR] Sa = 0 To UBound(R)
   c = c + 1
    [COLOR="Navy"]For[/COLOR] ac = 1 To UBound(Ray, 2)
        [COLOR="Navy"]If[/COLOR] ac = 1 [COLOR="Navy"]Then[/COLOR]
            nray(c, ac) = R(Sa)
        [COLOR="Navy"]Else[/COLOR]
            nray(c, ac) = Ray(Dic(R(Sa)), ac)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]Next[/COLOR] Sa
[COLOR="Navy"]End[/COLOR] With
c = 1: p = 0
[COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(nray, 1)
    c = c + 1
    [COLOR="Navy"]For[/COLOR] ac = 1 To 3
        p = p + 1
         [COLOR="Navy"]If[/COLOR] p > UBound(nray, 1) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
           Cells(c, ac + 4) = nray(p, 2)
    [COLOR="Navy"]Next[/COLOR] ac
        c = c + 1
    [COLOR="Navy"]For[/COLOR] ac = 3 To 1 [COLOR="Navy"]Step[/COLOR] -1
        p = p + 1
         [COLOR="Navy"]If[/COLOR] p > UBound(nray, 1) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
           Cells(c, ac + 4) = nray(p, 2)
    [COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]Next[/COLOR] Rw

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[shahishaan]

Hey Mick,

Thanks for this. I did a dry run and it worked, hoping that I do not struggle for a larger data-set, given my not so advance knowledge of excel.
Would there be any option without use of code or something which can be directly extended to larger data-set. Sorry for asking more on this.
A big thank you for this help.

Regards,
Ishaan

 

[MickG]

You're welcome
NB:- The code you have should cover as many rows as you have, for columns "A" to "D".