Macro required

Damo10 · Jul 4, 2011

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

B

C

D

E

F

G

H

I

J

K

1

Machine

Index

Start

Customer

Filler

Ammount

Code

%

No Bags

2

14

1

00:00

Fred

fudge

100

fu1

10

3

14

2

00:50

Bill

cream

200

cr1

10

20

4

14

5

02:10

Ellen

cream

250

cr1

10

25

5

34

8

00:00

Bob

fruit

50

fr1

20

10

6

34

15

06:00

Dave

mix

300

mx1

20

60

7

34

18

07:00

Ed

chocolate

200

ch1

5

10

8

34

20

08:00

Will

chocolate

500

ch1

5

25

Sheet1

Excel Workbook

B

C

D

E

F

G

H

I

J

K

1

Machine

Index

Start

Customer

Filler

Ammount

Code

%

No Bags

2

14

1

00:00

Fred

fudge

100

fu1

10

3

14

2

00:50

Bill

cream

450

cr1

10

45

4

34

8

00:00

Bob

fruit

50

fr1

20

10

5

34

15

06:00

Dave

mix

300

mx1

20

60

6

34

18

07:00

Ed

chocolate

700

ch1

5

35

Excel 2010 Sheet2

Excel 2010

MickG · Jan 31, 2012

Try this:-
NB:- On the first run only column "A" updates.
On the second run the remaining Additions and concatenations take place.

Code:

[COLOR="Navy"]Sub[/COLOR] MG31Jan10
[COLOR="Navy"]Dim[/COLOR] rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Tri     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] rw      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Tmix    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rxt     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Txt     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("C7"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
      [COLOR="Navy"]If[/COLOR] Not UCase(Dn.Offset(, -1)) = "N" [COLOR="Navy"]Then[/COLOR]
        Tri = Dn & Dn(, 5) & Dn(, 8) & Dn(, 9)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Tri) [COLOR="Navy"]Then[/COLOR]
            .Add Tri, Dn
        [COLOR="Navy"]Else[/COLOR]
           [COLOR="Navy"]Set[/COLOR] .Item(Tri) = Union(.Item(Tri), Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
  
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .Keys
[COLOR="Navy"]If[/COLOR] Not .Item(k) [COLOR="Navy"]Is[/COLOR] Nothing And .Item(k)(1).Offset(, 12) <= 1 And .Item(k).Count > 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Range("A2").Interior.ColorIndex = xlNone [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        .Item(k).Offset(, -2) = "Single [COLOR="Navy"]Set[/COLOR] No " & c
    [COLOR="Navy"]Else[/COLOR]
        .Item(k)(1).Offset(, 5) = Application.Sum(.Item(k).Offset(, 5))
        .Item(k)(1).Offset(, 9) = Application.Sum(.Item(k).Offset(, 9))
        .Item(k)(1).Offset(, 14) = Application.Sum(.Item(k).Offset(, 14))
        [COLOR="Navy"]For[/COLOR] Tmix = 16 To 24
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rxt [COLOR="Navy"]In[/COLOR] .Item(k).Offset(, Tmix)
                Txt = Txt & Rxt & ", "
            [COLOR="Navy"]Next[/COLOR] Rxt
                .Item(k)(1).Offset(, Tmix) = Left(Txt, Len(Txt) - 1)
                Txt = vbNullString
        [COLOR="Navy"]Next[/COLOR] Tmix
        
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] rw [COLOR="Navy"]In[/COLOR] .Item(k)
            [COLOR="Navy"]If[/COLOR] Not rw.Address = .Item(k)(1).Address [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] nRng = rw
                [COLOR="Navy"]Else[/COLOR]
                     [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, rw)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] rw
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    Range("A2").Interior.ColorIndex = 6
[COLOR="Navy"]Else[/COLOR]
    nRng.EntireRow.Hidden = True
    Range("A2").Interior.ColorIndex = xlNone
    rng.Offset(, 16).Resize(, 9).Columns.AutoFit
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Damo10 · Jan 31, 2012

Hi Mick,

The numbering and combining seems to be working fine, with columns 16 to 24 being concatenated I am getting a lot of cells that have either 1, 2, or 3 ","'s in them. Is it possible if there is no data then to just have a blank cell?

Thanks for your help so far

Regards

MickG · Jan 31, 2012

Modify code as shown in red:-

Code:

For Tmix = 16 To 24
            For Each Rxt In .Item(k).Offset(, Tmix)
                [COLOR=red][B]If Not Rxt = vbNullString Then
[/B][/COLOR]                    Txt = Txt & Rxt & ", "
               [B][COLOR=red] End If
[/COLOR][/B]            Next Rxt
               [COLOR=red][B]If Not Txt = vbNullString Then[/B][/COLOR] .Item(k)(1).Offset(, Tmix) = Left(Txt, Len(Txt) - 1)
                Txt = vbNullString
        Next Tmix

Damo10 · Jan 31, 2012

Mick,

That helped and there are no cells with more than 1 , but I am still getting a lot of cells with 1 , in them and nothing else.

Regards

MickG · Feb 1, 2012

The columns that are suppose to catenate, are columns "S" to "AA".
If those columns are not concatenting then, can you send an example of the sheet with all the columns. (Just a sample)
Mick

Damo10 · Feb 1, 2012

Hi Mick,

I have just noticed that it was the other 2 codes that are putting the ,'s in the cells, can you tell me how to modify those codes to check for VbNullString.

Could you also make ammend the code you wrote yesterday to highlight column C to show the combinations and also when concatenting to only include unique items as the two codes below do.

Regards

Code:

   Dim Tmix As Integer, uRng As Range, Du As Range, uTxt
  For n = 1 To Q(0).count
        For Tmix = 16 To 24
  Set uRng = Union(Q(0)(n, Tmix), Dic.Item(k)(0)(n, Tmix))
 For Each Du In uRng
    If InStr(uTxt, Du) = 0 Then
        uTxt = uTxt & "," & Du
    End If
 Next Du
 Q(0)(n, Tmix) = Mid(uTxt, 2)
 uTxt = ""
Next Tmix

Code:

For Tmix = 16 To 24
        If InStr(.Item(k)(1).Offset(, Tmix), .Item(k)(2).Offset(, Tmix)) = 0 Then
            .Item(k)(1).Offset(, Tmix) = .Item(k)(1).Offset(, Tmix) & ", " & .Item(k)(2).Offset(, Tmix)
        End If
        If InStr(.Item(k)(3).Offset(, Tmix), .Item(k)(4).Offset(, Tmix)) = 0 Then
            .Item(k)(3).Offset(, Tmix) = .Item(k)(3).Offset(, Tmix) & ", " & .Item(k)(4).Offset(, Tmix)
        End If
         Next Tmix

MickG · Feb 1, 2012

Try this:-
NB:- Can you sent the other codes that input ",s".
Q1, If you have this new code why do you need to use the other codes ???

Code:

[COLOR=navy]Sub[/COLOR] MG01Feb13
[COLOR=navy]Dim[/COLOR] rng     [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn      [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nRng    [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Tri     [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] k
[COLOR=navy]Dim[/COLOR] rw      [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c       [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Tmix    [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rxt     [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Txt     [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] rng = Range(Range("C7"), Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng
      [COLOR=navy]If[/COLOR] Not UCase(Dn.Offset(, -1)) = "N" [COLOR=navy]Then[/COLOR]
        Tri = Dn & Dn(, 5) & Dn(, 8) & Dn(, 9)
        [COLOR=navy]If[/COLOR] Not .Exists(Tri) [COLOR=navy]Then[/COLOR]
            .Add Tri, Dn
        [COLOR=navy]Else[/COLOR]
           [COLOR=navy]Set[/COLOR] .Item(Tri) = Union(.Item(Tri), Dn)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
 
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] .Keys
[COLOR=navy]If[/COLOR] Not .Item(k) [COLOR=navy]Is[/COLOR] Nothing And .Item(k)(1).Offset(, 12) <= 1 And .Item(k).Count > 1 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] Range("A2").Interior.ColorIndex = xlNone [COLOR=navy]Then[/COLOR]
        c = c + 1
        .Item(k).Offset(, -2) = "Single [COLOR=navy]Set[/COLOR] No " & c
        .Item(k).Interior.ColorIndex = 35
        .Item(k)(1).Interior.ColorIndex = 4
 
    [COLOR=navy]Else[/COLOR]
        .Item(k)(1).Offset(, 5) = Application.Sum(.Item(k).Offset(, 5))
        .Item(k)(1).Offset(, 9) = Application.Sum(.Item(k).Offset(, 9))
        .Item(k)(1).Offset(, 14) = Application.Sum(.Item(k).Offset(, 14))
        [COLOR=navy]For[/COLOR] Tmix = 16 To 24
            [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Rxt [COLOR=navy]In[/COLOR] .Item(k).Offset(, Tmix)
                [COLOR=navy]If[/COLOR] Not Rxt = vbNullString [COLOR=navy]Then[/COLOR]
                   [COLOR=navy]If[/COLOR] InStr(Txt, Rxt) = 0 [COLOR=navy]Then[/COLOR]
                        Txt = Txt & Rxt & ", "
                    [COLOR=navy]End[/COLOR] If
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] Rxt
               [COLOR=navy]If[/COLOR] Not Txt = vbNullString [COLOR=navy]Then[/COLOR] .Item(k)(1).Offset(, Tmix) = Left(Txt, Len(Txt) - 1)
                Txt = vbNullString
        [COLOR=navy]Next[/COLOR] Tmix
 
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] rw [COLOR=navy]In[/COLOR] .Item(k)
            [COLOR=navy]If[/COLOR] Not rw.Address = .Item(k)(1).Address [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]Set[/COLOR] nRng = rw
                [COLOR=navy]Else[/COLOR]
                     [COLOR=navy]Set[/COLOR] nRng = Union(nRng, rw)
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] rw
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
    Range("A2").Interior.ColorIndex = 6
[COLOR=navy]Else[/COLOR]
    nRng.EntireRow.Hidden = True
    Range("A2").Interior.ColorIndex = xlNone
    rng.Offset(, 16).Resize(, 9).Columns.AutoFit
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Damo10 · Feb 1, 2012

Hi Mick,

The code you wrote yesterday just deals with single sets where as the other 2 codes deal with cluster sets and have different criteria for combining.

Regards

Code:

Sub CombineCluster()
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 Integer
Dim Txt     As String
Dim Ray(1 To 4)
Dim Fd As Boolean
Dim n As Long
Dim Del As String
Dim Title As String
Dim colRng As Range
Dim ColRng2 As Range
Dim c As Integer
Dim Tmix As Integer
Set rng = Range(Range("C7"), Range("C" & Rows.count).End(xlUp))
rng.Interior.ColorIndex = xlNone
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    For Each Dn In rng
        Tri = Dn & Dn.Offset(, 1)
        If Dn.Offset(, 12) = 4 And Not UCase(Dn.Offset(, -1)) = "N" Then
        If Not .exists(Tri) Then
 
             Set Ray(Dn.Offset(, 11)) = Dn
                .Add Tri, Ray
        Else
            Q = .Item(Tri)
            Set Q(Dn.Offset(, 11)) = Dn
           .Item(Tri) = Q
        End If
End If
Next Dn
For Each k In .Keys
    For n = 1 To UBound(Ray)
        If IsEmpty(.Item(k)(n)) Then
            Fd = True
        End If
    Next n
If Fd = False Then
    If .Item(k)(1).Offset(, 7) = .Item(k)(2).Offset(, 7) And _
        .Item(k)(1).Offset(, 8) = .Item(k)(2).Offset(, 8) And _
            .Item(k)(3).Offset(, 7) = .Item(k)(4).Offset(, 7) And _
                .Item(k)(3).Offset(, 8) = .Item(k)(4).Offset(, 8) Then
Dim oRng As Range
      If Range("A1").Interior.ColorIndex = xlNone Then
         Set oRng = Union(.Item(k)(1), .Item(k)(2), .Item(k)(3), .Item(k)(4))
           c = c + 1
           oRng.Offset(, -2) = "Cluster Set No " & c
            If colRng Is Nothing Then
                  Set colRng = Union(.Item(k)(2), .Item(k)(4))
              Else
                   Set colRng = Union(colRng, .Item(k)(2), .Item(k)(4))
             End If
            If ColRng2 Is Nothing Then
                  Set ColRng2 = Union(.Item(k)(1), .Item(k)(3))
              Else
                   Set ColRng2 = Union(ColRng2, .Item(k)(1), .Item(k)(3))
             End If
 
    ElseIf Range("A1").Interior.ColorIndex = 6 Then
      .Item(k)(1).Offset(, 5) = .Item(k)(1).Offset(, 5) + .Item(k)(2).Offset(, 5)
    '''''''''''''''''
        For Tmix = 16 To 24
        If InStr(.Item(k)(1).Offset(, Tmix), .Item(k)(2).Offset(, Tmix)) = 0 Then
            .Item(k)(1).Offset(, Tmix) = .Item(k)(1).Offset(, Tmix) & ", " & .Item(k)(2).Offset(, Tmix)
        End If
        If InStr(.Item(k)(3).Offset(, Tmix), .Item(k)(4).Offset(, Tmix)) = 0 Then
            .Item(k)(3).Offset(, Tmix) = .Item(k)(3).Offset(, Tmix) & ", " & .Item(k)(4).Offset(, Tmix)
        End If
         Next Tmix
'''''''''''''''''''''''''''''
         .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)
              If nRng Is Nothing Then
                  Set nRng = Union(.Item(k)(2), .Item(k)(4))
              Else
                   Set nRng = Union(nRng, .Item(k)(2), .Item(k)(4))
             End If
      End If
   End If
 End If
 Fd = False
 Next k
Dim num
If Not colRng Is Nothing Then
    colRng.Interior.ColorIndex = 36
    Range("A1").Interior.ColorIndex = 6
ElseIf colRng Is Nothing Then
    Range("A1").Interior.ColorIndex = xlNone
End If
If Not ColRng2 Is Nothing Then
    ColRng2.Interior.ColorIndex = 6
    'ColRng2.Offset(, -1) = "Combined"
End If
If Not nRng Is Nothing Then
   Range("A1").Interior.ColorIndex = xlNone
   With nRng
   .ClearContents
   .EntireRow.Hidden = True
   End With
End If
End With
Set rng = Nothing
Set nRng = Nothing
Set colRng = Nothing
Set ColRng2 = Nothing
End Sub

Code:

Sub CombineClusterSets()
Dim rng     As Range
Dim Dn      As Range
Dim n       As Long
Dim Cols    As String
Dim nRng    As Range
Dim Q       As Variant
Dim Dic     As Object
Dim Doc     As Object
Dim k
Dim Dup
Dim Tri     As String
Dim Frt     As Range
Dim DelRng  As Range
Dim colRng  As Range
Dim ColRng2 As Range
Dim oCrits  As String
Set rng = Range(Range("C7"), Range("C" & Rows.count).End(xlUp))
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
    For Each Dn In rng
        Cols = Dn.Offset(, 1)
            If Not UCase(Dn.Offset(, -1)) = "N" Then
 
            If Not Dic.exists(Cols) Then
                Dic.Add Cols, Array(Dn, Dn, Dn.Offset(, 4), Dn.Offset(, 7), Dn.Offset(, 8))
            Else
                Q = Dic.Item(Cols)
                    Set Q(0) = Union(Q(0), Dn)
                    Q(1) = Q(1) & Dn
                    Q(2) = Q(2) & ", " & Dn.Offset(, 4)
                    Q(3) = Q(3) & ", " & Dn.Offset(, 7)
                    Q(4) = Q(4) & ", " & Dn.Offset(, 8)
                Dic.Item(Cols) = Q
        End If
    End If
    Next Dn
Dim c As Integer
Set Doc = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
        For Each k In Dic.Keys
            If Dic.Item(k)(0).count >= 3 Then
               oCrits = Dic.Item(k)(1) & Dic.Item(k)(2) & Dic.Item(k)(3) & Dic.Item(k)(4)
               If Not Doc.exists(oCrits) Then
                    Doc.Add oCrits, Dic.Item(k)
                Else
                    If Range("A3").Interior.ColorIndex = xlNone Then
                        If colRng Is Nothing Then
                            Set colRng = Doc.Item(oCrits)(0)
                        Else
                            Set colRng = Union(colRng, Doc.Item(oCrits)(0))
                        End If
                        If ColRng2 Is Nothing Then
                            Set ColRng2 = Dic.Item(k)(0)
                        Else
                            Set ColRng2 = Union(ColRng2, Dic.Item(k)(0))
                        End If
                    ElseIf Range("A3").Interior.ColorIndex = 6 Then
                    Q = Doc.Item(oCrits)
 
                 '''''''''''''''''''''''''''''''''''''''''''
   Dim Tmix As Integer, uRng As Range, Du As Range, uTxt
  For n = 1 To Q(0).count
        For Tmix = 16 To 24
  Set uRng = Union(Q(0)(n, Tmix), Dic.Item(k)(0)(n, Tmix))
 For Each Du In uRng
    If InStr(uTxt, Du) = 0 Then
        uTxt = uTxt & "," & Du
    End If
 Next Du
 Q(0)(n, Tmix) = Mid(uTxt, 2)
 uTxt = ""
Next Tmix
 
                  ''''''''''''''''''''''''''
                          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)
                            Next n
                        If DelRng Is Nothing Then
                            Set DelRng = Dic.Item(k)(0)
                        Else
                            Set DelRng = Union(DelRng, Dic.Item(k)(0))
                        End If
 
              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) = "Cluster Set No " & n
             ColRng2.Areas(n).Offset(, -2) = "Cluster Set No " & n
        Next n
    'colRng.Offset(, -1) = "Combined"
    colRng.Interior.ColorIndex = 8
    Range("A3").Interior.ColorIndex = 6
ElseIf colRng Is Nothing Then
    Range("A3").Interior.ColorIndex = xlNone
End If
If Not ColRng2 Is Nothing Then ColRng2.Interior.ColorIndex = 34
If Not DelRng Is Nothing Then
   Range("A3").Interior.ColorIndex = xlNone
   With DelRng
   .ClearContents
   .EntireRow.Hidden = True
   End With
End If
rng.Offset(, 16).Resize(, 9).Columns.AutoFit
End Sub

MickG · Feb 1, 2012

Have you got some data I can run them on to give the faulty result.

Damo10 · Feb 1, 2012

Here is some data you can use, the data that gets concatented in columns S:AA are empty in this data and when the code runs it puts a , in them all

<TABLE style="WIDTH: 713pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=948><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: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 184pt; mso-width-source: userset; mso-width-alt: 8960" width=245><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><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: #99ccff; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20 width=117>Cluster Set No 2</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: yellow; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=86>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 width=61>49582</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 39pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=52>11:25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=66>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 44pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=59>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75 width=57>216</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 184pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=245>STRAWBERRY </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 45pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=60>FRT011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; WIDTH: 39pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75 width=52>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20>Cluster Set No 2</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: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>49582</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11:25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>216</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>STRAWBERRY </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>FRT011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20>Cluster Set No 2</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: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>49582</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11:25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>200</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>BANANA </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>FRT012</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=20>Cluster Set No 2</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: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>49582</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11:25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>200</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>BANANA </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>FRT012</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 height=20>Cluster Set No 3</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: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>49585</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>273</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>CHERRY </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>FRT015</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 height=20>Cluster Set No 3</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: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>49585</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>273</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>CHERRY </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>FRT015</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 height=20>Cluster Set No 3</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: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>49585</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>273</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>REDFRUIT</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>FRT020</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 height=20>Cluster Set No 3</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: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>49585</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>12:56</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>273</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>REDFRUIT</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>FRT020</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 height=20>Cluster Set No 4</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: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl82>49435</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>16:53</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>793</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>TOFFEE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>FRT025</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 height=20>Cluster Set No 4</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: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl82>49435</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>16:53</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>793</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>TOFFEE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>FRT025</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 height=20>Cluster Set No 4</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: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl82>49435</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>16:53</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>793</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>TOFFEE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>FRT025</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 height=20>Cluster Set No 4</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: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl82>49435</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>14:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl83>16:53</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>GHY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>793</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>TOFFEE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl84>FRT025</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85>15</TD></TR></TBODY></TABLE>

Macro required

Damo10

Active Member

MickG

MrExcel MVP

Excel Facts

Damo10

Active Member

MickG

MrExcel MVP

Damo10

Active Member

MickG

MrExcel MVP

Damo10

Active Member

MickG

MrExcel MVP

Damo10

Active Member

MickG

MrExcel MVP

Damo10

Active Member

Similar threads

Forum statistics

Share this page

Macro required

Active Member

MrExcel MVP

Excel Facts

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock