--I forgot to mention that you need to have headers assigned to ColA and B in
Sheet2 ..something like Color in cell A1 and Count in cell B1

--Use the modified one which will generate the top two...

Sub AutofilterXX()
Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, arrData As Variant
Set ws1 = Worksheets("Sheet2")
Set ws2 = Worksheets("Sheet1")
ws1.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
lngRow = 2
ws2.Range("B1") = ws1.Range("B1").Value
Set rng1 = ws2.Range("B2:B" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
rng1.Formula = "=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)"
Set rng1 = ws2.Range("A1:B" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
rng1.Sort Key1:=ws2.Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
arrData = ws2.Range("A1:B3")
ws2.Range("A:B").ClearContents
ws2.Range("A1:B3") = arrData
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Glen" wrote:

Hi Jacob,

Thanks for the help, I just saw your post and tried it. It kind of worked,
here is the output that I got:

A B
-----------------
blue 42
orange 12
red 11
blue 5



I am not sure why that last blue is in there with the 5. Also, if it is
possible, is there a way to just show me the top two colors? For example:

A B
-----------
Blue 42
Orange 12


Thanks again!


------------------------------------------------------------

"Jacob Skaria" wrote:

With data in Sheet2 try the below macro which will bring up the summary in
Sheet1.

Sub AutofilterXX()
Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range
Set ws1 = Worksheets("Sheet2")
Set ws2 = Worksheets("Sheet1")
ws1.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
lngRow = 2
ws2.Range("B1") = ws1.Range("B1").Value
Set rng1 = ws2.Range("B2:B" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
rng1.Formula = "=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)"
Set rng1 = ws2.Range("A1:B" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
rng1.Sort Key1:=ws2.Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Glen" wrote:

Thanks in advance to any and all help!!

My problem starts like this, I have data in Sheet2 that looks like:

A B
---------------------
Blue 5
Orange 12
Red 6
Blue 15
Blue 22
Red 5

What I need is a formula that can look at this and sort the top two colors
of column A based on the highest total sum of the numbers associated with
those numbers in column B, and then give me the total number of the sum.

So the output would need to look something like this on sheet 1 for example:

A B
---------------
Blue 42
Orange 12


I apologize for my ignorance.

-glen