Classify data by simultaneously validating 3 parameters

shahishaan

New Member
Joined
Jun 18, 2018
Messages
2
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>[TABLE="width: 0"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: center"]Quality[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: center"]Item[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: center"]Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: center"]Availability[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] , align: center"]Truck A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] , align: center"]Truck B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] , align: center"]Truck C[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Apple[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Fruit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Spinach[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Broccoli[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Cauliflower[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Broccoli[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Vegetable[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Potato[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Apple[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Watermelon[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Potato[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Vegetable[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "]Litchi[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Mango[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Fruit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Cauliflower[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Vegetable[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Litchi[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Fruit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Watermelon[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Fruit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Spinach[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Vegetable[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C9DAF8]#C9DAF8[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE599]#FFE599[/URL] "][/TD]
[/TR]
</tbody>[/TABLE]
<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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try this:-
"Not quite the same result, but " !!!
Results in columns "E to G"
Code:
[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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top