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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
And do you want those times (Please show a single example) to be joined as in "text" or added as in "Number".
Mick
 
Upvote 0
Hi Mick,

I would like them added together

before
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=19 width=64 align=right>00:35</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=19 align=right>00:40</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=19 align=right>00:15</TD></TR></TBODY></TABLE>

after
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19 width=64 align=right>01:30</TD></TR></TBODY></TABLE>

Regards
 
Upvote 0
Add (In Red) as the follows:-
Code "CombineCluster() "
Rich (BB code): 
       .Item(K)(1).Offset(, 5) = .Item(K)(1).Offset(, 5) + .Item(K)(2).Offset(, 5)
        .Item(K)(3).Offset(, 5) = .Item(K)(3).Offset(, 5) + .Item(K)(4).Offset(, 5)
'''''''''''''''''''''
         .Item(K)(1).Offset(, 9) = .Item(K)(1).Offset(, 9) + .Item(K)(2).Offset(, 9)
         .Item(K)(3).Offset(, 9) = .Item(K)(3).Offset(, 9) + .Item(K)(4).Offset(, 9)
'''''''''''''''''''''''''''''''
        .Item(K)(1).Offset(, 14) = .Item(K)(1).Offset(, 14) + .Item(K)(2).Offset(, 14)
        .Item(K)(3).Offset(, 14) = .Item(K)(3).Offset(, 14) + .Item(K)(4).Offset(, 14)
Code :-"CombineClusterSets()"
Rich (BB code): 
        Q(0)(n, 6) = Q(0)(n, 6) + Dic.Item(K)(0)(n, 6)
        Q(0)(n, 10) = Q(0)(n, 10) + Dic.Item(K)(0)(n, 10)
        Q(0)(n, 15) = Q(0)(n, 15) + Dic.Item(K)(0)(n, 15)

Mick
 
Upvote 0
Hi Mick,

That all works great now, thanks so much.

I am hoping that I can ask for something else, I would like when the runs are combined to have a report on a new sheet "Report" that shows what WON's have been combined for each machine?

Example

<TABLE style="WIDTH: 464pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=616><COLGROUP><COL style="WIDTH: 58pt" span=8 width=77><TBODY><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=40 width=77>Machine No</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=77>Primary WON</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 348pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=462 colSpan=6>Combined WON's</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>12568</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>12569</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>12570</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>23451</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>23452</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>23453</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>23454</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>23555</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR></TBODY></TABLE>


Regards Damian
 
Upvote 0
In the example I have, there could be more than one primery number for the same Machine number.
Although My altered Data example may not reflect reality!!!!
For example:- I have M/c 72, at 2 different places in the list, both with their own Primary number and there own set of "Won " Numbers.
Do you want a list in "Results" that combines all Primary and won numbers, under the M/C number and the first primary number, or individual sets of the same Primary Number with their own Won Numbers.
Like this:-
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 id=td_post_2781569 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>M/C</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>P Num</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Won</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>72</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3105211</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3104911</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:num>72</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>31052</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>31049</TD></TR></TBODY></TABLE>
Or this:-

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 id=td_post_2781569 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>M/C</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>P Num</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Won</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Won</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Won</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>72</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3105211</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>3104911</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>31052</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>31049</TD></TR></TBODY></TABLE>
 
Upvote 0
Hi Mick,

Could I have individual sets for each Primary number like your first example.

Regards
 
Upvote 0
Try this :-
Call like below, for results on sheet "Results":-
Code: 
[COLOR=navy]Sub[/COLOR] MG30Nov12
[COLOR=navy]If[/COLOR] Range("A1").Interior.ColorIndex = 6 [COLOR=navy]Then[/COLOR] Call MCTots
Call CombineCluster '[COLOR=green][B]Complete sets Not Combined when "N" Present[/B][/COLOR]
Call CombineRest '[COLOR=green][B] Part sets Combined when "N" present[/B][/COLOR]
Call CombineClusterSets '[COLOR=green][B]Complete sets Not Combined when "N" Present[/B][/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Code: 
[COLOR=navy]Sub[/COLOR] MCTots()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Mult [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]Dim[/COLOR] k
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
c = 1
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A7"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 10)
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Mult = Dn & Dn.Offset(, 2)
        [COLOR=navy]If[/COLOR] Dn <> "" [COLOR=navy]Then[/COLOR]
            [COLOR=navy]If[/COLOR] Not .Exists(Mult) [COLOR=navy]Then[/COLOR]
                n = n + 1
                ray(n, 1) = Dn.Offset(, 2)
                ray(n, 2) = Dn.Offset(, 3)
                .Add Mult, Array(ray, n, 2)
            [COLOR=navy]ElseIf[/COLOR] Dn.Offset(, 1) = "" [COLOR=navy]Then[/COLOR]
                Q = .Item(Mult)
                [COLOR=navy]If[/COLOR] Not Dn.Offset(, 3) = Q(0)(Q(1), Q(2)) [COLOR=navy]Then[/COLOR]
                   Q(2) = Q(2) + 1
                   Q(0)(Q(1), Q(2)) = Dn.Offset(, 3)
                [COLOR=navy]End[/COLOR] If
                .Item(Mult) = Q
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] .Keys
    c = c + 1
    [COLOR=navy]For[/COLOR] Ac = 1 To .Item(k)(2)
        Sheets("Results").Cells(c, Ac) = .Item(k)(0)(.Item(k)(1), Ac)
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,

I have been using the code that you have written for me and it has been working brilliantly, thanks so much for you time with it.

I have started encountering a small issue with the run numbering when there are more than 1 run that needs to be combined per machine and the runs are mixed in with other runs to be combined as it gives it the same number - see example below, is it possible to rectify it so they both have a different number? the combine code works fine it is just the number part.

<TABLE style="WIDTH: 702pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=933><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8557" width=234><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 width=117>Single Set No 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=93></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=86>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 width=61>125871</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 39pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 width=52>06:46</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 width=65>07:37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=57>BSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=57>333</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 176pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=234>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 44pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=59>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; WIDTH: 39pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=52>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75 height=20>Single Set No 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76>125864</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>07:37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>14:37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>1224</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75 height=20>Single Set No 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76>125986</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>14:37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>16:37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>1163</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff6699; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>0</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=xl71 height=20>Single Set No 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>124785</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>16:37</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>16:52</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>BSE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>53</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Natural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>0</TD></TR></TBODY></TABLE>

Here is the lastest code for the macro

Code: 
Sub CombineRest()
Dim rng     As Range
Dim Dn      As Range
Dim nRng    As Range
Dim Tri     As String
Dim Q       As Variant
Dim k
Dim Rw As Range
Dim nnRng   As Range
Dim colRng  As Range
Dim ColRng2 As Range
Dim n       As Long
Set rng = Range(Range("C7"), Range("C" & Rows.count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    For Each Dn In rng
      If Not UCase(Dn.Offset(, -1)) = "N" Then
        Tri = Dn & Dn(, 5) & Dn(, 8) & Dn(, 9)
        If Not .exists(Tri) Then
            .Add Tri, Array(Dn, nRng)
        Else
            Q = .Item(Tri)
            If Q(1) Is Nothing Then
                Set Q(1) = Dn
            Else
                Set Q(1) = Union(Q(1), Dn)
            End If
            .Item(Tri) = Q
       End If
  End If
Next Dn
For Each k In .Keys
    If Not .Item(k)(1) Is Nothing And .Item(k)(0).Offset(, 12) <= 1 Then
    If Range("A2").Interior.ColorIndex = xlNone Then
            If colRng Is Nothing Then
                Set colRng = .Item(k)(1)
            Else
                Set colRng = Union(colRng, .Item(k)(1))
            End If
            If ColRng2 Is Nothing Then
                Set ColRng2 = .Item(k)(0)
            Else
                Set ColRng2 = Union(ColRng2, .Item(k)(0))
            End If
   ElseIf Range("A2").Interior.ColorIndex = 6 Then
     '''''''''''''''''
Dim uRng As Range, Du As Range, uTxt
For Tmix = 16 To 24
  Set uRng = Union(.Item(k)(0).Offset(, Tmix), .Item(k)(1).Offset(, Tmix))
 For Each Du In uRng
    If InStr(uTxt, Du) = 0 Then
        uTxt = uTxt & "," & Du
    End If
 Next Du
 .Item(k)(0).Offset(, Tmix) = Mid(uTxt, 2)
 uTxt = ""
Next Tmix
 
'''''''''''''''''''''''''''''
 
     .Item(k)(0).Offset(, 5) = .Item(k)(0).Offset(, 5) + Application.Sum(.Item(k)(1).Offset(, 5))
        .Item(k)(0).Offset(, 9) = .Item(k)(0).Offset(, 9) + Application.Sum(.Item(k)(1).Offset(, 9))
        .Item(k)(0).Offset(, 14) = .Item(k)(0).Offset(, 14) + Application.Sum(.Item(k)(1).Offset(, 14))
              If nnRng Is Nothing Then
                  Set nnRng = .Item(k)(1)
              Else
                   Set nnRng = Union(nnRng, .Item(k)(1))
             End If
      End If
    End If
 Next k
If Not colRng Is Nothing Then
     For n = 1 To colRng.Areas.count
            colRng.Areas(n).Offset(, -2) = "Single Set No " & n
            ColRng2.Areas(n).Offset(, -2) = "Single Set No " & n
        Next n
 
    colRng.Interior.ColorIndex = 35
    Range("A2").Interior.ColorIndex = 6
ElseIf colRng Is Nothing Then
    Range("A2").Interior.ColorIndex = xlNone
End If
If Not ColRng2 Is Nothing Then
    ColRng2.Interior.ColorIndex = 4
    'ColRng2.Offset(, -1) = "Combined"
End If
If Not nnRng Is Nothing Then
   Range("A2").Interior.ColorIndex = xlNone
   With nnRng
   .ClearContents
   .EntireRow.Hidden = True
   End With
End If
End With
rng.Offset(, 16).Resize(, 9).Columns.AutoFit
Set rng = Nothing
Set nRng = Nothing
Set nnRng = Nothing
Set colRng = Nothing
Set ColRng2 = Nothing
End Sub

Regards Damian
 
Upvote 0
Please refresh my memory (it seems a long time) by sending me
a set of data, perhaps the last one in this thread that functions with the current code, and incorporate the extra set of data thats showing the new problem.
It would also help if you could show the current result from the code regarding the new problem data, and expected result when modified to eliminate the problem.
Mick
 
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,627
Messages
6,179,964
Members
452,950
Latest member
bwilliknits

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