New to Excel/VB and could use any help

[Glen]

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

 

[Malik]

Quick and Dirty Solution is:

Original Data
A B
Blue 5
Orange 12
Red 6
Blue 15
Blue 22
Red 5


Processed Data
A Sum of Colors Rank
Blue 42 3
Orange 12 2
Red 11 1

Sum Of Colors Rank
Formulas =SUMIF($C$16:$D$21,G16,$D$16:$D$21) =RANK(G16,$F$16:$G$18,1)

* Get Unique Color List
* Use SUMIF to get total of each color
* Use Rang Function to Get the ranking for each colors Sum - You might get
the same rank for some colors if there sum is same.

 

[Jacob Skaria]

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

 

[Glen]

Thanks Malik...

That does help if I wanted to be able to order all the colors in this
example, but I need it to figure out for me what the two colors are that have
the most aggregate numbers next to it. Essentially I need it to tell me that
Blue and Orange are the two colors with the most numbers

Blue 42
Orange 12

I know it is easy with just a few colors, but my real world example could
have hundreds of different variables.

Thanks again!

-glen


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

 

[Malik]

Hi,

You can use then Pivot Table. It is much easier way to do this.

If you want me to send the excel sample. Let me know.

You can find the pivot table under "Data > Pivot Table Chart.."

If you do have fixed range then it is easy for you but you can create a
dynamic range which can take care of the extra rows.

 

[Glen]

Thanks again Malik!

Could you please send the example? It would really help out.

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

 

[Glen]

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!


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

 

[Malik]

Glen,

I can't figure out your email but you can try this:

Source Data:
Color Count
Blue 15
Red 25
Black 30
Blue 45
Red 20
Blue 30

Steps to use Pivot Table Wizard:
1) Goto Data -> Tools --> Pivot Table and Pivot Chart Report
2) Select Microsoft Excel list or database.
3) Select Pivot Table
4) Click Next
5) Select Range
6) Click Next
7) Click LayOut
8) Drag Color Column as Row
9) Drag Count as Data.
10) Click Ok
11) Click Finish
12) Right Click on "Color" on Pivot Table.
13) Select "Field Settings"
14) Click on Advanced
15) In TOP10 AutoShow section, click on "On" radio button
16) In Show section: Select "TOP" and number change to 2.
17) You can use "AutoSort Option" section to sost your data

Expected Output
Sum of Count
Color Total
Blue 90
Red 45
Grand Total 135

** You can use TableOptions or FieldSetting to fix the display as per your
requirements **

 

[Glen]

edited.......

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 by total number, not by location? For example:

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


Thanks again!

 

[Jacob Skaria]

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

 

[Glen]

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]

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

 

[Glen]

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]

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

 

[Glen]

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

-glen

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