Sorted and calculating categories and totals maco

I

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
103
I have excel invoices that may or may not contain all categories listed below. I would like to run a macro that will search for each category and then give me a total extended retail of each category. I used to use they following code but my category list is getting so long that there has to be a simpler code. Please help me.

category list (partial) (I have 210)
Apparel
Athletic Equipment
Bathroom/Kitchen Hardware
Batteries
Bedding


VBA Code: 
Sub Domestics()
'
' Domestics Macro
'
' Keyboard Shortcut: Ctrl+d
'
    
 myRange = ActiveSheet.Range("H1", Range("H1").End(xlDown))
 Range("Q9") = WorksheetFunction.Sum(myRange)
    
 
 Set MyRg1 = Range("K:K")
 Set MyRg2 = Range("H:H")
 Cells(1, "O") = WorksheetFunction.SumIf(MyRg1, "Rugs", MyRg2)
 Range("N1").Value = "Rugs"
 
 Set MyRg1 = Range("K:K")
 Set MyRg2 = Range("H:H")
 Cells(2, "O") = WorksheetFunction.SumIf(MyRg1, "Garage Storage", MyRg2)
 Range("N2").Value = "Garage Storage"
 
     Columns("N:N").ColumnWidth = 14.29
    Range("Q9").Select
    Selection.Cut
    Range("O4").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-12
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/R[3]C[-1]"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/R[2]C[-1]"
    Columns("P:P").Select
    Selection.NumberFormat = "0.00%"
    Selection.NumberFormat = "0%"
    Columns("P:P").Select
    Selection.Cut
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight
    Range("Q9").Select

    
End Sub

cstSUM-3800477.csv
ABCDEFGHIJK
1Lot #Item #Dept. CodeDepartmentItem DescriptionQtyUnit RetailExt. RetailCategory CodeBrandCategory
23800477262106026SPORTING GOODSSEALY ALWAYZAIRE TOUGH7149.991049.93DADBESTWAY HNG KNG INTL LTDCamping Goods
33800477135530023HARDWAREFEIT FLOODLIGHT CAMERA7129.99909.93GVCFEIT ELECTRIC COMPANYHome Safety
43800477154965123HARDWAREMOEN 5-SERIES ELONGATED2399.99799.98MAMMOEN INCORPORATEDBathroom/Kitchen Hardware
53800477131264220HEALTH & BEAUTY AIDSHAIRMAX LASERBAND 82 BUND1679.99679.99HRRLEXINGTON INTERNATNL LLCHair Care
63800477167726920HEALTH & BEAUTY AIDSIRESTORE PROFESSIONAL1649.99649.99HRRFREEDOM LASER THERAPY INCHair Care
73800477243759933SMALL APPLIANCESSHARPER IMAGE PRO879.99639.92UCAMERCHSOURCE LLCMassage/Relaxation
83800477152986934DOMESTICSNOVAFORM COMFORT GRANDE2129.99629.79DAFFXI INCPillows
93800477168929520HEALTH & BEAUTY AIDSCOLGATE TOOTHBRUSH ADV3716.99628.63DCBCOLGATE PALMOLIVE COOral Care
103800477119381723HARDWAREBARRISTER COACH LIGHT1539.99599.85ADVALTAIR LIGHTINGLighting
113800477597808233SMALL APPLIANCESWATERPIK WATERFLOSSER689.99539.94VDAWATER PIK INCOral Care
123800477151041523HARDWAREFEIT LED 7.5" FLAT PANEL764.99454.93UGBFEIT ELECTRIC COMPANYLight Bulbs
133800477262219926SPORTING GOODSSAMSONITE RENEW SOFTSIDE2224.99449.98GLSSAMSONITE LLCLuggage
143800477200051226SPORTING GOODSAEROBED COMFORTLOCK AIR3149.99449.97DADCOLEMAN COMPANY INCCamping Goods
153800477110856934DOMESTICSSERENITY MEM FOAM PILLOW1529.99449.85DAFCOMFORT REVOLUTION LLCPillows
163800477164431220HEALTH & BEAUTY AIDSSKIN INC OPTIMIZER VOYAGE2194.99389.98KLDNATIONAL DISTRIBUTORSFacial Care
173800477163566134DOMESTICSPENDLETON 5-PIECE KING3109.99329.97GCABHF INTERNATIONAL LTDBedding
183800477232200723HARDWARETOMMY BAHAMA BOLLARD559.99299.95NAAALPAN LIGHTING PRDTS INCLandscape Lighting
193800477262207126SPORTING GOODSSWINGING HAMMOCK CHAIR649.99299.94DDCRIO BRANDS LLCCamping Goods
203800477130609034DOMESTICSKS 680TC QUEEN SHEET SET469.99279.96AAAHIMATSINGKA AMERICA INCBedding
213800477166212728TOYS/SEASONALAMAX ASC-2600 DRONE469.99279.96AHAAMAX GROUP USA LLCToys
223800477239869733SMALL APPLIANCESWAHL DELUXE HAIRCUT KIT739.99279.93WDAWAHL CLIPPER CORPShavers
233800477151509434DOMESTICSGA HENLEY RUNNER 26"X96"1124.99274.89JACGA GERTMENIAN AND SONSRugs
243800477163709926SPORTING GOODSSAMSONITE RENEW 2PC S/S1229.99229.99GLSSAMSONITE LLCLuggage
253800477150067334DOMESTICSPURE EARTH 300TC QUEEN544.99224.95AADINDO COUNT INDUSTRIES LTDBedding
263800477126158823HARDWARERING ALARM SECURITY KIT1219.99219.99GYCRING LLCHome Safety
273800477161762423HARDWAREMOEN LISO BATHROOM FAUCET369.99209.97MALMOEN INCORPORATEDBathroom/Kitchen Hardware
283800477157500321OFFICE SUPPLIESFGX PLASTIC 3PK READING1118.99208.89WRAFGX INTERNATIONAL INCReading Glasses
293800477487410334DOMESTICSAMTEX SEALY DOWNALT KING825.99207.92DABAMERICAN TEXTILE CO INCPillows
303800477123280523HARDWAREPANASONIC HOMEHAWK FRONT1199.99199.99GVAPANASONIC WESTERN GROUPHome Safety
313800477155249326SPORTING GOODSFROGG TOGGS MENS299.99199.98NECNATIONAL DISTRIBUTORSFishing/Hunting
323800477163566034DOMESTICSPENDLETON 5-PIECE QUEEN299.99199.98GCABHF INTERNATIONAL LTDBedding
333800477160033423HARDWARELED SOLAR STRING LIGHT539.99199.95BBESUNFORCE PRODUCTS INCLighting
343800477160081934DOMESTICSECO BLANKET QN P601019.99199.9IAABERKSHIRE BLANKET ANDBlankets
353800477156030134DOMESTICSBROOKSTONE HEATED THROW632.99197.94IEALONDON LUXURYBlankets
363800477262215826SPORTING GOODSRICARDO WINDSOR 2PC1179.99179.99GLHRICARDO BEVERLY HILLSLuggage
37380047782992323HARDWAREFIRST ALERT 10YR PE SMOKE629.99179.94GBBBRK BRANDS INCHome Safety
383800477156270234DOMESTICSCHARISMA MF SHEETS KING919.99179.91AACJIANGSU ROYAL HME USA INCBedding
393800477487410234DOMESTICSAMTEX SEALY DOWNALT ST/QN919.99179.91DABAMERICAN TEXTILE CO INCPillows
403800477160032723HARDWAREKOHLER LAYNE TOILET SEAT724.99174.93MAMKOHLER COMPANYBathroom/Kitchen Hardware
413800477154467823HARDWAREFIRST ALERT 10YR COMBO354.99164.97GBBBRK BRANDS INCHome Safety
423800477162349934DOMESTICSPNDLTN SHERPA KING P60532.99164.95IAABHF INTERNATIONAL LTDBlankets
433800477160024623HARDWAREENERGETIC LIGHTING 14"626.99161.94ADAYANKON LIGHTING INCLighting
443800477130609134DOMESTICSKS 680TC KING SHEET SET279.99159.98AAAHIMATSINGKA AMERICA INCBedding
453800477144690432HOUSEWARESSIMPLEHUMAN SOAP279.99159.98SBZSIMPLEHUMAN LLCBathroom Essentials
463800477262203726SPORTING GOODSCMT HIGH BACK CHAIR438.99155.96DDCCASCADE MOUNTAINCamping Goods
473800477158309823HARDWAREKOHLER PROSECCO HANDHELD349.99149.97MAKKOHLER COMPANYBathroom/Kitchen Hardware
483800477202089823HARDWAREOVE WATERFALL STRING349.99149.97BBEOVE DECORS ULCLighting
493800477153953534DOMESTICS5PC ROSLYN BATH SET434.99139.96CACSILK HOME INCLinens
503800477162514923HARDWAREDURACELL+ AA 40PK718.99132.93CABDURACELL DISTRIBUTING INCBatteries
513800477162349734DOMESTICSPNDLTN SHERPA TWIN P90621.99131.94IAABHF INTERNATIONAL LTDBlankets
523800477113719723HARDWARE3M 2200 SERIES 14X25X1264.99129.98OGE3M COMPANYFilters
533800477143057723HARDWARE3M 2200 SERIES 14X30X1264.99129.98OGE3M COMPANYFilters
543800477151190234DOMESTICSBRENTORG MODERN FARMHOUSE1012.99129.9EAABRENTWOOD ORIGINALSPillows
553800477164897023HARDWAREFEIT WIFI SMART BULB 4PK524.99124.95UYGFEIT ELECTRIC COMPANYLight Bulbs
563800477140146826SPORTING GOODSTRAVELER'S CHOICE CREEK1119.99119.99GLHTRAVELERS CHOICE TRVLWARELuggage
573800477163959934DOMESTICSRETRO CHIC 3PC QUILT KG259.99119.98GDAPEM AMERICA INCBedding
583800477165288034DOMESTICSWOVEN STRIPE SHEET Q259.99119.98AAAINDO COUNT INDUSTRIES LTDBedding
593800477333300433SMALL APPLIANCESOMNI BREEZE TOWER FAN339.99119.97MEDNINGBO SINGFUN ELECTRICFans
603800477152897723HARDWAREFEIT 2PK SMART DIMMER429.99119.96BYPFEIT ELECTRIC COMPANYLighting/Electrical
613800477148778221OFFICE SUPPLIESROYAL SHREDDER 10 SHEET256.99113.98KFDROYAL CONSUMEROffice Supplies
623800477262216726SPORTING GOODSTITAN 50 CAN COLLAPSIBLE336.99110.97AAFCALIFORNIA INNOVATIONSCoolers
633800477150067434DOMESTICSPURE EARTH 300TC KING254.99109.98AADINDO COUNT INDUSTRIES LTDBedding
643800477137222234DOMESTICSCASPER THE ESSENTIAL PILL334.99104.97DABCASPER SLEEP INCPillows
653800477143154326SPORTING GOODSALPTREK OUTDOOR BLANKET425.99103.96DGDCASCADE MOUNTAINCamping Goods
66380047772165334DOMESTICSGLORY WISDOM SHEEPSKIN199.9999.99JABGLORY WISDOM CORPRugs
673800477103620031WOMENS APPAREL30S2GHMS3L MK HAMILTON EW199.9999.99HHMQ E COMMERCEHandbags
683800477262206126SPORTING GOODSCORE 6P 400 LUMEN199.9999.99DEAELEVATE LLCCamping Goods
693800477138939626SPORTING GOODSWILSON TENNIS RACKET P56249.9999.98HCAWILSON SPORTING GOODS COAthletic Equipment
703800477158388333SMALL APPLIANCESSHARPER IMAGE COMPACT249.9999.98UCAMERCHSOURCE LLCMassage/Relaxation
713800477159021934DOMESTICSNIGHT SILK CASE GRAY QN249.9999.98AABDISCOVER NIGHT LLCBedding
723800477164924023HARDWAREKS FURNACE FILTER 14X24X1249.9999.98OGEKIMBERLY CLARK CORPFilters
733800477160082034DOMESTICSECO BLANKET KG P48424.9999.96IAABERKSHIRE BLANKET ANDBlankets
743800477152897523HARDWAREFEIT WIFI SMART BULB 2PK519.9999.95UYGFEIT ELECTRIC COMPANYLight Bulbs
753800477400810027GARDENRAPID FLO GARDEN HOSE248.9997.98JAATEKNOR APEX COMPANYGardening
763800477104876534DOMESTICSAH RDS BLANKET WHITE QN194.9994.99IADALLIED HOME LLCBlankets
773800477143474834DOMESTICSAH RDS BLANKET GREEN QN194.9994.99IAAALLIED HOME LLCBlankets
783800477157500121OFFICE SUPPLIESFGX LIMITED 3PK READING518.9994.95WRAFGX INTERNATIONAL INCReading Glasses
793800477159197232HOUSEWARESMIKASA WOOD LANTERN 2PK246.9993.98HFELIFETIME BRANDS INCHome Decor
803800477145081334DOMESTICSJA 4PC SHEET SET KG BEIGE189.9989.99AABJENNIFER ADAMS MFG INCBedding
813800477145082634DOMESTICSJA ETERNAL 3P DUV K BEIGE189.9989.99GAAJENNIFER ADAMS MFG INCBedding
823800477160820126SPORTING GOODSTIMBER RIDGE CAMP PANTRY189.9989.99DDJWESTFIELD OUTDOOR INCCamping Goods
833800477262221126SPORTING GOODSCMT HIGHBACK CHAIR189.9989.99DDCCASCADE MOUNTAINCamping Goods
cstSUM-3800477
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
I cheated and used my list while pasting the following in the next cell: =SUMIF($K$2:$K$1000, M2, $H$2:$H$1000)

works but not as pretty as a macro :(
 
Upvote 0
instanceoftime said:
I cheated and used my list while pasting the following in the next cell: =SUMIF($K$2:$K$1000, M2, $H$2:$H$1000)

works but not as pretty as a macro :(
Click to expand...
cat.xlsx
MN
2Adhesives0
3Air Treatment Appliances0
4Aluminum Foil0
5Apparel0
6Appliances0
7Arts & Crafts Supplies0
8Athletic Equipment0
9Automotive Tools0
10Baby Wipes0
11Backpacks0
12Bakeware0
13Baking Essentials0
14Baking Mixes0
15Bathroom Essentials0
16Bathroom/Kitchen Hardware0
17Batteries0
18Beach Gear0
19Beach Towels0
20Beauty Tools0
21Bedding0
22Beverage Coolers0
23Beverages0
24Blankets0
25Boards0
26Body Wash0
27Business Domestics0
28Camping Goods0
29Candy0
30Canned Meats0
31Canopies0
32Car Accessories0
33Car Care0
34Car Cleaners0
35Car Electronics0
36Chef's Apparel0
37Chest Freezers0
38Children's Footwear0
39Cleaning Products0
40Coffee0
41Coffee Creamers0
42Coffee/Sweeteners0
43Collapsible Wagons0
44Collectibles0
45Commercial Kitchen Items0
46Condiments0
47Consumer Electronics0
48Cooking Oils0
49Cookware0
50Coolers0
51Cooling Appliances0
52Cosmetics0
53Curtains0
54Cutlery0
55Deodorant/Anti-Perspirant0
56Diabetic Care0
57Diapers0
58Dining Furniture0
59Dinnerware0
60Dishwashers0
61Display/Demo0
62Displays0
63Disposable Bags0
64Disposable Dinnerware0
65Disposable Wraps0
66Domestics0
67Down Comforters0
68Drink Mixes0
69Drinkware0
70Dryers0
71Exercise Equipment0
72Eye Care0
73Facial Care0
74Fans0
75Feminine Hygiene0
76Filters0
77First Aid0
78Fishing/Hunting0
79Flatware0
80Flavored Syrup0
81Floorcare0
82Flooring0
83Food Prep0
84Food Storage0
85Fragrances0
86Fruit Snacks0
87Fruits/Veggies0
88Garage Flooring0
89Garage Storage0
90Garden / Patio0
91Gardening0
92Garment Care0
93Generators0
94Grill Accessories0
95Grills0
96Hair Care0
97Handbags0
98Hardware0
99Health & Beauty0
100Health Monitors0
101Health/Medicine0
102Heating Appliances0
103Heating/Cooling Appliances0
104Home Automation0
105Home Decor0
106Home Furnishings0
107Home Organization0
108Home Safety0
109Hygiene Products0
110Ice Makers0
111Ice Melt0
112Indoor Games0
113International Food0
114Janitorial Supplies0
115Kitchen Essentials0
116Kitchen Storage0
117Lamps0
118Landscape Lighting0
119Laundry Products0
120Laundry Suites0
121Lawn Equipment0
122Lawn Mowers0
123Light Bulbs0
124Lighting0
125Lighting/Electrical0
126Linens0
127Luggage0
128Magnifying Optics0
129Massage/Relaxation0
130Mayonnaise0
131Meat Snacks0
132Media0
133Medical Equipment0
134Men's Footwear0
135Microwaves0
136Mini Fridges0
137Mints/Gum0
138Modems0
139Moisturizers0
140Musical Instruments0
141Network Equipment0
142Nutrition Bars0
143Nutrition Supplements0
144Nuts0
145Office Decor0
146Office Furniture0
147Office Supplies0
148Oil/Filters0
149Oils/Dressings0
150Oral Care0
151Outdoor Chairs0
152Outdoor Decor0
153Outdoor Games0
154Outdoor Goods0
155Outdoor Storage0
156Pain Relievers0
157Pantry Goods0
158Paper Products0
159Patio Accessories0
160Patio Furniture0
161Patio Heaters0
162Patio Umbrellas0
163Personal Care0
164Pet Supplies0
165Pillows0
166Pool Items0
167Power Equipment0
168Power Tools0
169PPE/Medical Equipment0
170Prepared Foods0
171Pressure Washers0
172Printers0
173Range Hoods0
174Reading Glasses0
175Refrigerators0
176Rugs0
177Safes0
178Sauces0
179Seasonal Food0
180Seasonal Items0
181Serveware0
182Sewing0
183Shavers0
184Shaving Needs0
185Shelving/Racks0
186Small Kitchen Appliances0
187Snacks0
188Snow Goods0
189Snow Removal0
190Soaps0
191Software0
192Soups0
193Special Events0
194Spices & Seasonings0
195Sporting Goods0
196Stationery0
197Storage0
198Storage/Laundry0
199Suncare0
200Sweeteners0
201Syrup0
202Tailgating Canopies0
203Tires0
204Toilets0
205Toner0
206Tool Storage0
207Tools0
208Toys0
209Trash Cans0
210Travel Pillows0
211TV Mounts0
212Umbrellas0
213Upholstered Furniture0
214Vanities0
215Vanity Mirrors0
216Vitamin/Supplements0
217Washers0
218Water Coolers0
219Water Filtration0
220Water Sports0
221Water Treatment0
222Wearables0
223Wet/Dry Vacs0
224Windshield Wipers0
225Wine Accessories0
226Wine Fridges0
227Winter Sports0
228Women's Footwear0
229Work Apparel0
230Writing Instruments0
Sheet1
Cell Formulas
RangeFormula
N2N2=SUMIF($K$2:$K$1000, M2, $H$2:$H$1000)
N3:N230N3=SUMIF($K$2:$K$345, M3, $H$2:$H$345)
 
Upvote 0
VBA Code: 
Sub cooler()
        Dim k As Integer
        Dim lr, lc As Long
        Dim wk1, wk2 As Worksheet
        Set wk1 = Sheets("input") 'change the sheet name
        Set wk2 = Sheets("output") 'change the sheet name
        lr = wk1.Cells(Rows.Count, 1).End(xlUp).Row
        lc = wk1.Cells(1, Columns.Count).End(xlToLeft).Column
               
            For k = 2 To lr
                    If WorksheetFunction.CountIf(Range(Range("K2"), Range("K" & k)), Range("K" & k)) = 1 Then
                        wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Range("K" & k)
                            wk2.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = WorksheetFunction.SumIf(Range("K2:K" & lr), Range("K" & k), Range("H2:H" & lr))
                    End If
            Next k
End Sub
 

Attachments

  • 1671286828835.png
    1671286828835.png
    18.7 KB · Views: 21
Upvote 0
Solution
shinigamilight said:
VBA Code: 
Sub cooler()
        Dim k As Integer
        Dim lr, lc As Long
        Dim wk1, wk2 As Worksheet
        Set wk1 = Sheets("input") 'change the sheet name
        Set wk2 = Sheets("output") 'change the sheet name
        lr = wk1.Cells(Rows.Count, 1).End(xlUp).Row
        lc = wk1.Cells(1, Columns.Count).End(xlToLeft).Column
              
            For k = 2 To lr
                    If WorksheetFunction.CountIf(Range(Range("K2"), Range("K" & k)), Range("K" & k)) = 1 Then
                        wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Range("K" & k)
                            wk2.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = WorksheetFunction.SumIf(Range("K2:K" & lr), Range("K" & k), Range("H2:H" & lr))
                    End If
            Next k
End Sub
Click to expand...
works brilliantly (and so little code ... I am so out of touch with vb these days) ... could it be coded to use active worksheet and maybe post results in column M of the same sheet?
 
Upvote 0
instanceoftime said:
works brilliantly (and so little code ... I am so out of touch with vb these days) ... could it be coded to use active worksheet and maybe post results in column M of the same sheet?
Click to expand...
VBA Code: 
Sub cooler()
        Dim k As Integer
        Dim lr As Long

        lr = Cells(Rows.Count, 1).End(xlUp).Row
            For k = 2 To lr
                    If WorksheetFunction.CountIf(Range(Range("K2"), Range("K" & k)), Range("K" & k)) = 1 Then
                        Cells(Rows.Count, 13).End(xlUp).Offset(1, 0) = Range("K" & k)
                            Cells(Rows.Count, 13).End(xlUp).Offset(0, 1) = WorksheetFunction.SumIf(Range("K2:K" & lr), Range("K" & k), Range("H2:H" & lr))
                    End If
            Next k
            Range("M1:N1").EntireColumn.AutoFit
            
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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