I can't thank you enough! You don't know how much this has helped me.

-glen

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

"Jacob Skaria" wrote:

Try this

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("B:B").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
lngRow = 2
ws2.Range("B1") = ws1.Range("F1").Value
Set rng1 = ws2.Range("B2:B" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
rng1.Formula = "=SUMIF(Sheet2!B:B,A2,Sheet2!F:F)"
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:

Jacob, you sir are a genius, thank you so much!

One final question if you don't mind?

What do I have to change in the macro if the color column was in column b
and the count was in f for example?

Once I know that, I swear I will not bother you any longer!!!

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

"Jacob Skaria" wrote:

I tried with this sample from A1:B7 ...Copy this to sheet2 and try the macro

ColA ColB
Color Count
Orange 12
Red 6
Blue 15
Blue 22
Red 5
Blue 5

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


"Glen" wrote:

Thanks so much Jacob, I am almost there...

When I put in Column Headers I get this error:

Run-time error '10044':
The extract range has a missing or illegal field name.


I am also not getting the top two colors by count, but all three colors still.


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

"Jacob Skaria" wrote:

--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