Macro required

D

Damo10

Active Member
Joined
Dec 13, 2010
Messages
460
Hi,

I have a workbook that I have created that imports data from 4 other workbooks and lists the data on the "Data" sheet, what I would like to have is a macro that will look through all the rows on this sheet and if it finds matches of data in 4 columns then have a message box pop up asking if the match would like to be comnined, if yes then the values for the matching rows in columns G & K be added together and entered into the first matching row and then the other matching rows deleted, the macro would then need to check if there are any other matches as there may be more than 1

See example of before and after

Excel Workbook
BCDEFGHIJK
1MachineIndexStartCustomerFillerAmmountCode%No Bags
214100:00Fredfudge100fu11010
314200:50Billcream200cr11020
414502:10Ellencream250cr11025
534800:00Bobfruit50fr12010
6341506:00Davemix300mx12060
7341807:00Edchocolate200ch1510
8342008:00Willchocolate500ch1525
Sheet1
Excel Workbook
BCDEFGHIJK
1MachineIndexStartCustomerFillerAmmountCode%No Bags
214100:00Fredfudge100fu11010
314200:50Billcream450cr11045
434800:00Bobfruit50fr12010
5341506:00Davemix300mx12060
6341807:00Edchocolate700ch1535
Excel 2010 Sheet2
Excel 2010
 
Sort by date Sort by votes
I can possibly give you a Msgbox that will still enable you to scroll the window with the msgbox showing.
To actually position the msgbox need a lot more code.
I could also write some code for a Userform that you could position, and also scroll the screen at the same time.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi Mick,

If you could have a userform that coud be positioned and then have the screen scroll that would be great

Regards
 
Upvote 0
Try this:-
You will need to create the Userform (size to suit), with a Textbox (TextBox1) and also Two Command Buttons. CommandButtons 1 & 2
You only have to place the code below in then as follows.
You can adjust position of userform within the code :- See remarks
Code: 
Private Sub CommandButton1_Click()
Call LastCombineCluster
ActiveSheet.Columns("A:A").Interior.ColorIndex = xlNone
Unload Me
End Sub
Code: 
Private Sub CommandButton2_Click()
ActiveSheet.Columns("A:A").Interior.ColorIndex = xlNone
Unload Me
Exit Sub
End Sub

Then Place this new "LastCobineCluster" code below in standard Module:-
Code: 
[COLOR="Navy"]Sub[/COLOR] LastCombineCluster()
'[COLOR="Green"][B]New test Code[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Tri     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic1 [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Dic2 [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oCrit [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
'[COLOR="Green"][B]With Application[/B][/COLOR]
'[COLOR="Green"][B].ScreenUpdating = False[/B][/COLOR]
'[COLOR="Green"][B].Calculation = xlCalculationManual[/B][/COLOR]
'[COLOR="Green"][B]End With[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C7"), Range("C" & Rows.Count).End(xlUp))
Rng.Interior.ColorIndex = xlNone
[COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Tri = Dn & Dn(, 2) & Dn(, 5) & Dn(, 8) & Dn(, 9)
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 12) = 4 And Not UCase(Dn.Offset(, -1)) = "N" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dic1.Exists(Tri) [COLOR="Navy"]Then[/COLOR]
           Dic1.Add Tri, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic1.Item(Tri) = Union(Dic1.Item(Tri), Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
ReDim Ray(1 To Dic1.Count)
[COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
    Dic2.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic1.keys
 oCrit = Dic1.Item(k)(1) & Dic1.Item(k)(1).Offset(, 4) & Dic1.Item(k)(1).Offset(, 7) & Dic1.Item(k)(1).Offset(, 8)
        [COLOR="Navy"]If[/COLOR] Not Dic2.Exists(oCrit) [COLOR="Navy"]Then[/COLOR]
            Dic2.Add oCrit, Dic1.Item(k)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] Dic1.Item(k).Offset(, -2).Interior.ColorIndex = 34 And Dic2.Item(oCrit).Offset(, -2).Interior.ColorIndex = 35 [COLOR="Navy"]Then[/COLOR]
                    Txt = ""
                    [COLOR="Navy"]For[/COLOR] Rw = 1 To Dic1.Item(k).Count
                        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Array(5, 9, 14)
                            Dic2.Item(oCrit)(Rw).Offset(, Ac) = Dic2.Item(oCrit)(Rw).Offset(, Ac) + Dic1.Item(k)(Rw).Offset(, Ac)
                        [COLOR="Navy"]Next[/COLOR] Ac
                    [COLOR="Navy"]Next[/COLOR] Rw
                    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Dic1.Item(k)
                    [COLOR="Navy"]Else[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dic1.Item(k))
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Else[/COLOR]
                    Dic1.Item(k).Offset(, -2).Interior.ColorIndex = 34
                    Dic2.Item(oCrit).Offset(, -2).Interior.ColorIndex = 35
                    Txt = Dic2.Item(oCrit)(1).Offset(, -2) & " and " & Dic1.Item(k)(1).Offset(, -2) & Chr(10)
            Txt = IIf(Txt = " and " & Chr(10), "", Txt)
            [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] k
    [COLOR="Navy"]If[/COLOR] Txt <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] UserForm1
            .Caption = "Combine Clusters"
            .Show vbModeless
            .Top = 100 '[COLOR="Green"][B]Change for Top position[/B][/COLOR]
            .Left = 200 '[COLOR="Green"][B] Change for Left position[/B][/COLOR]
        [COLOR="Navy"]With[/COLOR] .TextBox1
            .Font.Italic = True
            .Font.Bold = True
            .Font.Size = 8
            .MultiLine = True
            .Text = "Do you want to Combine :=" & Chr(10) & Chr(10) & Txt
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]With[/COLOR] .CommandButton1
          .Caption = "Combine"
          .Font.Bold = True
           .Font.Size = 10
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]With[/COLOR] .CommandButton2
          .Caption = "Cancel"
          .Font.Bold = True
          .Font.Size = 10
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]End[/COLOR] With
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,

I have tested the code with a userform and that works well, the only issue is that if there are more than one match it combines them all if the combine button is pressed. Could it be ammended so that the userform is displayed for each match so that there is the option to combine or cancel each match?

Regards Damian
 
Upvote 0
Replace the old code with the code below:-
Also:-
Remove the TexBox and replace with a Listbox (ListBox1), above the list Box place a Label control (Label1) (Make it quite wide!!)

Change CommandButton2 code to the second code below.
NB:- When the Userform shows first, The Groups for combining will be shown in the list box.
Selected those required and press "Combine" , Groups selected should be combined.



Code: 
[COLOR="Navy"]Sub[/COLOR] LastCombineCluster()
'[COLOR="Green"][B]New test Code[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Tri     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic1    [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Dic2    [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Txt     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oCrit   [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rw      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] k
 
'[COLOR="Green"][B]With Application[/B][/COLOR]
'[COLOR="Green"][B].ScreenUpdating = False[/B][/COLOR]
'[COLOR="Green"][B].Calculation = xlCalculationManual[/B][/COLOR]
'[COLOR="Green"][B]End With[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C7"), Range("C" & Rows.Count).End(xlUp))
ReDim Ray(Rng.Count / 2)
Rng.Interior.ColorIndex = xlNone
[COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Tri = Dn & Dn(, 2) & Dn(, 5) & Dn(, 8) & Dn(, 9)
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 12) = 4 And Not UCase(Dn.Offset(, -1)) = "N" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dic1.Exists(Tri) [COLOR="Navy"]Then[/COLOR]
           Dic1.Add Tri, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic1.Item(Tri) = Union(Dic1.Item(Tri), Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
ReDim Ray(1 To Dic1.Count)
[COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
    Dic2.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic1.Keys
 oCrit = Dic1.Item(k)(1) & Dic1.Item(k)(1).Offset(, 4) & Dic1.Item(k)(1).Offset(, 7) & Dic1.Item(k)(1).Offset(, 8)
        [COLOR="Navy"]If[/COLOR] Not Dic2.Exists(oCrit) [COLOR="Navy"]Then[/COLOR]
            Dic2.Add oCrit, Dic1.Item(k)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] Dic1.Item(k).Offset(, -2).Interior.ColorIndex = 34 And Dic2.Item(oCrit).Offset(, -2).Interior.ColorIndex = 35 [COLOR="Navy"]Then[/COLOR]
               [COLOR="Navy"]With[/COLOR] UserForm1.ListBox1
                   [COLOR="Navy"]For[/COLOR] n = 0 To .ListCount - 1
                    [COLOR="Navy"]If[/COLOR] .Selected(n) And .List(n) = Dic2.Item(oCrit)(1).Offset(, -2) & " and " & Dic1.Item(k)(1).Offset(, -2) [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]For[/COLOR] Rw = 1 To Dic1.Item(k).Count
                            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Array(5, 9, 14)
                                Dic2.Item(oCrit)(Rw).Offset(, Ac) = Dic2.Item(oCrit)(Rw).Offset(, Ac) + Dic1.Item(k)(Rw).Offset(, Ac)
                            [COLOR="Navy"]Next[/COLOR] Ac
                        [COLOR="Navy"]Next[/COLOR] Rw
                        
                        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                            [COLOR="Navy"]Set[/COLOR] nRng = Dic1.Item(k)
                        [COLOR="Navy"]Else[/COLOR]
                            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dic1.Item(k))
                        [COLOR="Navy"]End[/COLOR] If
                    [COLOR="Navy"]End[/COLOR] If
                 [COLOR="Navy"]Next[/COLOR] n
              [COLOR="Navy"]End[/COLOR] With
            [COLOR="Navy"]Else[/COLOR]
                    c = c + 1
                    Dic1.Item(k).Offset(, -2).Interior.ColorIndex = 34
                    Dic2.Item(oCrit).Offset(, -2).Interior.ColorIndex = 35
                    Ray(c) = Dic2.Item(oCrit)(1).Offset(, -2) & " and " & Dic1.Item(k)(1).Offset(, -2)
            [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] k
    [COLOR="Navy"]If[/COLOR] c > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] UserForm1
            .Caption = "Combine Clusters"
            .Show vbModeless
            .Top = 100 '[COLOR="Green"][B]Change for Top position[/B][/COLOR]
            .Left = 200 '[COLOR="Green"][B] Change for Left position[/B][/COLOR]
         [COLOR="Navy"]With[/COLOR] .ListBox1
           .List = Ray
           .MultiSelect = fmMultiSelectMulti
         [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]With[/COLOR] .TextBox1
            .Font.Italic = True
            .Font.Bold = True
            .Font.Size = 8
            .MultiLine = True
            .Text = Txt
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]With[/COLOR] .CommandButton1
           .Caption = "Combine"
           .Font.Bold = True
           .Font.Size = 10
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]With[/COLOR] .CommandButton2
          .Caption = "Cancel"
          .Font.Bold = True
          .Font.Size = 10
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]With[/COLOR] .Label1
           .Caption = "Please select Clusters for Combining "
           .Font.Bold = True
           .Font.Size = 10
        [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]End[/COLOR] With
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
nRng.EntireRow.Delete
[COLOR="Navy"]Set[/COLOR] nRng = Nothing
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Code: 
Private [COLOR="Navy"]Sub[/COLOR] CommandButton2_Click()
ActiveSheet.Columns("A:A").Interior.ColorIndex = xlNone
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] UserForm1
[COLOR="Navy"]For[/COLOR] x = 0 To .ListBox1.ListCount - 1
    [COLOR="Navy"]If[/COLOR] .ListBox1.Selected(x) = True [COLOR="Navy"]Then[/COLOR]
        .ListBox1.Selected(x) = False
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
Unload Me
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,

That works perfectly, your codes are pure genius.

I have also learned something new about list boxes from your code which has also helped me shorten some processes.

Could you let me know a good place to learn about the scripting dictionary that you use in your codes?

Thanks again for you fantastic help

Regards Damian
 
Upvote 0
Hi Mick,

I have run into an issue with the last code you wrote, on certain days when running it the userform is popping up and asking to combine runs that it should not.

In the example below it is asking to combine numbers 1 & 5 and 3 & 8 ( I have manually written in these numbers to show what rows it want to combine).

I am not sure why it would ask these to combine as they should not meet the criteria for combining, can you have a look please?

<TABLE style="WIDTH: 939pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1249><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 4832" width=151><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 2976" width=93><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 1952" width=61><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 1952" width=61><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1664" width=52><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2112" width=66><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 1888" width=59><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 1856" width=58><COL style="WIDTH: 208pt; mso-width-source: userset; mso-width-alt: 8864" width=277><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1920" width=60><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1664" width=52><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1824" width=57><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1632" width=51><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2080" width=65><TBODY><TR style="HEIGHT: 102.75pt" height=137><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 113pt; HEIGHT: 102.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=137 width=151></TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=93>Combine Run</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=61>Mc No</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=61>WON</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=52>Start</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=66>Finish</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=59>Base</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65 width=58>Base KG</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 208pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=277>Flavour</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl64 width=60>Code</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65 width=52>Fruit %</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66 width=57>No Bags</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65 width=51>Bag Size</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65 width=86>Tank No per Set</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65 width=65>No of Tanks</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl88 height=21>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>15:01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>539</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>RHUBARB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT306</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>15.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>15:01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>542</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>STRAWBERRY </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT305</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>16.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl88 height=20>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>15:01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>594</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>VANILLA </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT308</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>9.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl88 height=20>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>15:01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>594</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>VANILLA </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT308</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>9.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87 height=20>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15473</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>17:23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>253</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>RHUBARB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT306</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>7.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83 height=20>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15473</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>17:23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>255</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>TOFFEE </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT301</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>5.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83 height=20>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15473</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>17:23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>240</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>RASPBERRY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT307</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>6.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87 height=20>8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>15473</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>16:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl80>17:23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl79>TRD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>264</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>VANILLA </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl78>FRT308</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl82>4.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81>4</TD></TR></TBODY></TABLE>

Regards Damian
 
Upvote 0
Hi Mick,

I think I have solved the issue, can you have a look to see if I have done it correctly?

The rows that it was asking to combine did not have any data in Col A which is where matching data sets would be identified so I put a check in to see if this was empty or not. The modified part of the code is in red.

Rich (BB code): 
Sub LastCombineCluster()
Dim Rng     As Range
Dim nRng    As Range
Dim Dn      As Range
Dim Tri     As String
Dim Dic1    As Object
Dim Dic2    As Object
Dim Txt     As String
Dim oCrit   As String
Dim C       As Integer
Dim n       As Integer
Dim Rw      As Integer
Dim Ac
Dim Ray
Dim k
 
'With Application
'.ScreenUpdating = False
'.Calculation = xlCalculationManual
'End With
Set Rng = Range(Range("C7"), Range("C" & Rows.Count).End(xlUp))
ReDim Ray(Rng.Count / 2)
Rng.Interior.ColorIndex = xlNone
Set Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
    For Each Dn In Rng
        Tri = Dn & Dn(, 2) & Dn(, 5) & Dn(, 8) & Dn(, 9)
        If Dn.Offset(, 12) = 4 And Not UCase(Dn.Offset(, -1)) = "N" Then
        If Not Dic1.exists(Tri) Then
           Dic1.Add Tri, Dn
        Else
            Set Dic1.Item(Tri) = Union(Dic1.Item(Tri), Dn)
        End If
End If
Next Dn
ReDim Ray(1 To Dic1.Count)
Set Dic2 = CreateObject("scripting.dictionary")
    Dic2.CompareMode = vbTextCompare
For Each k In Dic1.Keys
 oCrit = Dic1.Item(k)(1) & Dic1.Item(k)(1).Offset(, 4) & Dic1.Item(k)(1).Offset(, 7) & Dic1.Item(k)(1).Offset(, 8)
        If Not Dic2.exists(oCrit) Then
            Dic2.Add oCrit, Dic1.Item(k)
        Else
            If Dic1.Item(k).Offset(, -2).Interior.ColorIndex = 34 And Dic2.Item(oCrit).Offset(, -2).Interior.ColorIndex = 35 Then
               With CombineClusterForm.ListBox1
                   For n = 0 To .ListCount - 1
                    If .Selected(n) And .List(n) = Dic2.Item(oCrit)(1).Offset(, -2) & " and " & Dic1.Item(k)(1).Offset(, -2) Then
                        For Rw = 1 To Dic1.Item(k).Count
                            For Each Ac In Array(5, 9, 14)
                                Dic2.Item(oCrit)(Rw).Offset(, Ac) = Dic2.Item(oCrit)(Rw).Offset(, Ac) + Dic1.Item(k)(Rw).Offset(, Ac)
                            Next Ac
                        Next Rw
 
                        If nRng Is Nothing Then
                            Set nRng = Dic1.Item(k)
                        Else
                            Set nRng = Union(nRng, Dic1.Item(k))
                        End If
                    End If
                 Next n
              End With
            Else
                    If Not Dic1.Item(k).Offset(, -2).Text = "" Then
                        C = C + 1
                        Dic1.Item(k).Offset(, -2).Interior.ColorIndex = 34
                        Dic2.Item(oCrit).Offset(, -2).Interior.ColorIndex = 35
                        Ray(C) = Dic2.Item(oCrit)(1).Offset(, -2) & " and " & Dic1.Item(k)(1).Offset(, -2)
                    End If
            End If
End If
Next k
    If C > 0 Then
        With CombineClusterForm
            .Caption = "Combine Clusters"
            .Show vbModeless
            .Top = 100 'Change for Top position
            .Left = 200 ' Change for Left position
         With .ListBox1
           .List = Ray
           .MultiSelect = fmMultiSelectMulti
         End With
        With .TextBox1
            .Font.Italic = True
            .Font.Bold = True
            .Font.Size = 8
            .MultiLine = True
            .Text = Txt
        End With
        With .Confirm
           .Caption = "Combine"
           .Font.Bold = True
           .Font.Size = 10
        End With
        With .Cancel
          .Caption = "Cancel"
          .Font.Bold = True
          .Font.Size = 10
        End With
        With .Label1
           .Caption = "Please select all the runs that you want to combine"
           .Font.Bold = True
           .Font.Size = 10
        End With
        End With
   End If
If Not nRng Is Nothing Then
nRng.EntireRow.Delete
Set nRng = Nothing
Call ColourClusterSets
End If
End Sub

Regards Damian
 
Upvote 0
The List box shows "1-5" & "3-8", apart from thinking this should be "1-5" & "3,4 & 8", Although I may have the logic wrong, what is wrong with the answer ???
 
Upvote 0
Hi Mick,

It should not be asking to combine any of these rows as the criteria for combining has not been met.

Regards
Damian
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Looking for help or guidance on a potential Macro project
Replies
1
Views
90
Rijnsent
Rijnsent
F
  • Solved
Search & Find String of Words
2
Replies
10
Views
157
Peter_SSs
Peter_SSs
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S
A
  • Solved
VBA code to extract a table
Replies
5
Views
289
Anthony47
A
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
110
Kaper
Kaper

Forum statistics

Threads
1,224,618
Messages
6,179,916
Members
452,949
Latest member
beartooth91

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