|
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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. -- Malik "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 |
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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" wrote: 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. -- Malik "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 |
New to Excel/VB and could use any help
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. -- Malik Remember to click "yes" if this post helped you!* "Glen" wrote: 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" wrote: 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. -- Malik "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 |
New to Excel/VB and could use any help
Thanks again Malik!
Could you please send the example? It would really help out. ---------------------------------------------------------- "Malik" wrote: 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. -- Malik Remember to click "yes" if this post helped you!* "Glen" wrote: 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" wrote: 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. -- Malik "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 |
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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 ** -- Malik ***** Remember to click "yes" if this post helped you! ****** "Glen" wrote: Thanks again Malik! Could you please send the example? It would really help out. ---------------------------------------------------------- "Malik" wrote: 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. -- Malik Remember to click "yes" if this post helped you!* "Glen" wrote: 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" wrote: 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. -- Malik "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 |
New to Excel/VB and could use any help
edited.......
"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 by total number, not by location? 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 |
New to Excel/VB and could use any help
--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 |
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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 |
New to Excel/VB and could use any help
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 |
| All times are GMT +1. The time now is 11:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com