Classify data by simultaneously validating 3 parameters

S

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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

G
  • Question Question
Data validation drop down list source to lookup a value and list unused items ?
Replies
0
Views
1K
gaftalik
G
J
  • Question Question
Extract 1st, 2nd, and 3rd instances of text between Parentheses
Replies
4
Views
1K
jeffcoleky
J
L
  • Question Question
Index Match of partial data then date drive results
Replies
2
Views
637
lndspdr
L
D
  • Question Question
Auto sum numbers based on previous number and a given number
Replies
3
Views
673
jtakw
jtakw
H
  • Question Question
Struggling on Criteria to INDEX Match correct Authorization number for tracking and billing
Replies
0
Views
535
HMM2019
H

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

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