Stumped Again

A

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
151
Office Version
  1. 2011
Platform
  1. MacOS
I can do a join/transpose with one column but nothing I have tried will do a join/transpose with the condition being in two columns. Below is a sample of what I am trying to do. Thanks for any help you may give.

[TABLE="width: 260"]
<colgroup><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65, align: center"][TABLE="width: 260"]
<colgroup><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]A[/TD]
[TD="class: xl63, width: 65"]B[/TD]
[TD="class: xl63, width: 65"]C[/TD]
[TD="class: xl63, width: 65"]D[/TD]
[/TR]
[TR]
[TD="class: xl63"]Fruit[/TD]
[TD="class: xl63"]Number[/TD]
[TD="class: xl63"]Sequence[/TD]
[TD="class: xl63"]Shown[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63"]#3[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63"]#1 [/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Apples [/TD]
[TD="class: xl63"]#1 [/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]Plums[/TD]
[TD="class: xl63"]#1 [/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, colspan: 4, align: center"]FROM ABOVE TO BELOW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63"]D[/TD]
[/TR]
[TR]
[TD="class: xl63"]Fruit[/TD]
[TD="class: xl63"]Number[/TD]
[TD="class: xl63"]Sequence[/TD]
[TD="class: xl63"]Shown[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]7, 9, 11[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63"]#3[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]4, 6[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]2, 5[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63"]#2[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]Plums[/TD]
[TD="class: xl63"]#1[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try this:-
NB:- This code will alter your data
Code: 
[COLOR="Navy"]Sub[/COLOR] MG08Jan15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & 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
Txt = Dn.Value & Dn.Offset(, 1).Value
    [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
        .Add Txt, Dn.Offset(, 2)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
      .Item(Txt).Value = .Item(Txt).Value & ", " & Dn.Offset(, 2).Value
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
Rng.Resize(, 4).Sort key1:=[A1], order1:=xlAscending, Header:=xlYes

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
I am not sure why but I get the message "ActiveX component can't create Object. The line now highlighted in the macro is "With CreateObject ("scripting dictionary")".
Thank you.
 
Last edited:
Upvote 0
I just found out that since I am on Excel for Mac, that I don't have access to ActiveX. Is there maybe another way around this? Thanks again.
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG08Jan10
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Rng.Resize(, 4).Sort key1:=[A1], order1:=xlAscending, Header:=xlNo
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn = Dn.Value & "," & Dn.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] temp = Dn
    [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not temp = Dn [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] temp = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]If[/COLOR] temp = Dn [COLOR="Navy"]Then[/COLOR]
                    temp.Offset(, 2).Value = temp.Offset(, 2).Value & "," & Dn.Offset(, 2).Value
                [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: Dn = Split(Dn.Value, ",")(0): [COLOR="Navy"]Next[/COLOR] Dn
[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
Thanks again Mick. The macro works but doesn't do exactly what I need it to do. The group on the left is what your macro returned for me. Either one of the two on the left is what I really need. I have a much bigger data base that I need to reduce. Thanks again to you and all the members on this forum. It has been a big help to me in the past and is really appreciated.[TABLE="width: 910"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 910"]
<colgroup><col width="65" span="14" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65, align: center"]Fruit[/TD]
[TD="class: xl63, width: 65, align: center"]Number[/TD]
[TD="class: xl63, width: 65, align: center"]Sequence[/TD]
[TD="class: xl63, width: 65, align: center"]Shown[/TD]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65, align: center"]Fruit[/TD]
[TD="class: xl63, width: 65, align: center"]Number[/TD]
[TD="class: xl63, width: 65, align: center"]Sequence[/TD]
[TD="class: xl63, width: 65, align: center"]Shown[/TD]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65, align: center"]Fruit[/TD]
[TD="class: xl63, width: 65, align: center"]Number[/TD]
[TD="class: xl63, width: 65, align: center"]Sequence[/TD]
[TD="class: xl63, width: 65, align: center"]Shown[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"][/TD]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]7, 9, 11[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]7, 9, 11[/TD]
[TD="class: xl63, align: center"]3[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]9,11[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD][/TD]
[TD]Lemons[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]4, 6[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Apples [/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]7[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]2, 5[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD]Lemons[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]4, 6[/TD]
[TD="class: xl63, align: center"]2[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]4,6[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl64, align: center"][/TD]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"][/TD]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]2, 5[/TD]
[TD="class: xl63, align: center"]2[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]12[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]5[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]12[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]2[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]12[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]10[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]10[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Plums[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]8[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Plums[/TD]
[TD="class: xl63, align: center"]#1[/TD]
[TD="class: xl63, align: center"]8[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]10[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Plums[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]8[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you all so much
Larry
 
Last edited:
Upvote 0
Running my last code on your initial data returned the below to me !!!!
Was your data different ????
[TABLE="width: 225"]
<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 108, bgcolor: white"]Fruit[/TD]
[TD="width: 64, bgcolor: white"]Number[/TD]
[TD="width: 64, bgcolor: white"]Sequence[/TD]
[TD="width: 64, bgcolor: white"]Shown[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Apples[/TD]
[TD="bgcolor: white"]#3[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Apples[/TD]
[TD="bgcolor: white"]#1[/TD]
[TD="bgcolor: white"]7,9,11[/TD]
[TD="bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Lemons[/TD]
[TD="bgcolor: white"]#2[/TD]
[TD="bgcolor: white"]4,6[/TD]
[TD="bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Oranges[/TD]
[TD="bgcolor: white"]#1[/TD]
[TD="bgcolor: white"]2,5[/TD]
[TD="bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Oranges[/TD]
[TD="bgcolor: white"]#2[/TD]
[TD="bgcolor: white"]12[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Pears[/TD]
[TD="bgcolor: white"]#1[/TD]
[TD="bgcolor: white"]3[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Pears[/TD]
[TD="bgcolor: white"]#2[/TD]
[TD="bgcolor: white"]10[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Plums[/TD]
[TD="bgcolor: white"]#1[/TD]
[TD="bgcolor: white"]8[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Sorry Mick, I thought I posted this sooner but just saw my reply did not post. My data was the same as I posted before. Below is my data and on the left is your macro result. Everything seems to work except for the "Apples #1 " which didn't group together. Thanks again to you and all of the ones on the forum for all the help you have given me in the past. Again below is my list and on the right is the result of your macro.

[TABLE="width: 585"]
<colgroup><col width="65" span="9" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65, align: center"]Fruit[/TD]
[TD="class: xl63, width: 65, align: center"]Number[/TD]
[TD="class: xl63, width: 65, align: center"]Sequence[/TD]
[TD="class: xl63, width: 65, align: center"]Shown[/TD]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65, align: center"]Fruit[/TD]
[TD="class: xl63, width: 65, align: center"]Number[/TD]
[TD="class: xl63, width: 65, align: center"]Sequence[/TD]
[TD="class: xl63, width: 65, align: center"]Shown[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]9,11[/TD]
[TD="class: xl63, align: center"]3[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Apples [/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]7[/TD]
[TD="class: xl63, align: center"]3[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]4[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD]Lemons[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]4,6[/TD]
[TD="class: xl63, align: center"]2[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]6[/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Apples [/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]7[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]12[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Plums[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]8[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]9[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]10[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]10[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD]Plums[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]8[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="class: xl63, align: center"]#1 [/TD]
[TD="class: xl63, align: center"]11[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="class: xl63, align: center"]#2[/TD]
[TD="class: xl63, align: center"]12[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Just out of curiosity, does this macro work for you (I don't have a Mac, so I cannot test it)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub SequenceNumbersPerFruitNumber()
  Dim R As Long, LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  ReDim Arr(2 To LastRow, 1 To 1)
  For R = 2 To LastRow
    Arr(R, 1) = Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF((A" & R & "=A2:A#)*(B" & R & "=B2:B#),C2:C#,"""")", "#", LastRow)))))
  Next
  Range("C2:C" & LastRow) = Arr
  Columns("A:D").RemoveDuplicates Array(1, 2, 3, 4), xlYes
  Columns("A:D").Sort [A1], xlAscending, Header:=xlYes
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Sorry, I wish I could say yes but I get the exact same results as Mick's macro except this macro runs for a really long time before it gives the results. I ran it twice to make sure. Thank you for trying, i really appreciate it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Textjoin and Filter using VBA
Replies
7
Views
234
BritsBlitz
B
P
  • Solved
Copy Values to an Adjacent Column per unique Customer
Replies
3
Views
393
Joe4
Joe4
S
  • Solved
Lookup results in a table based on multiple criteria and set number of occurrences
Replies
4
Views
333
kevin9999
K
T
  • Solved
Match with multiple columns
2
Replies
14
Views
754
Peter_SSs
Peter_SSs
ExcelNewbie2020
  • Question Question
lookup and distribute the qty based on capacity
Replies
1
Views
270
bobaol
B

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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