Filtering different brands of items into new sheet

alvin97

New Member
Joined
Sep 26, 2024
Messages
23
Office Version
  1. 365
Platform
  1. Windows
I needs help on with filter different brand of item, as current VBA code was all using range cell to filter into different column. This make a lot of code needed to amend when there is a new product. Is there a way to filter each brand with unique SKU like (BrandA, BrandB, BrandC)? Thanks

Before
1727343546089.png

After
1727343617999.png


VBA Code:
Sub Report()

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Main"

Dim ResultCell As Range
Dim Sheet As Worksheet

Set Sheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
Sheet.Name = "Report"

Sheets("Main").Range("$A$4:$A$21").Copy
Sheets("Report").Activate
Range("A4").Select
ActiveSheet.Paste

Sheets("Main").Range("$A$22:$A$35").Copy
Sheets("Report").Activate
Range("D4").Select
ActiveSheet.Paste

Sheets("Main").Range("$A$36:$A$45").Copy
Sheets("Report").Activate
Range("G4").Select
ActiveSheet.Paste


    Dim LookupValueCell As Range
    Dim LookupVector As Range
    Dim ResultVector As Range

    
    Set ResultCell = Sheets("Report").Range("$B$4:$B$21")
    Set LookupValueCell = Sheets("Report").Range("$A$4:$A$400")
    Set LookupVector = Sheets("Main").Range("$A$4:$A$400")
    Set ResultVector = Sheets("Main").Range("$B$4:$B$23")

ResultCell = WorksheetFunction.Lookup(LookupValueCell, LookupVector, ResultVector)

    Dim LookupValueCell1 As Range
    Dim LookupVector1 As Range
    Dim ResultVector1 As Range
    Dim ResultCell1 As Range
    
    
    Set ResultCell1 = Sheets("Report").Range("$E$4:$E$17")
    Set LookupValueCell1 = Sheets("Report").Range("$D$4:$D$400")
    Set LookupVector1 = Sheets("Main").Range("$A$4:$A$400")
    Set ResultVector1 = Sheets("Main").Range("$B$4:$B$372")

ResultCell1 = WorksheetFunction.Lookup(LookupValueCell1, LookupVector1, ResultVector1)

    Dim LookupValueCell2 As Range
    Dim LookupVector2 As Range
    Dim ResultVector2 As Range
    Dim ResultCell2 As Range
    
    
    Set ResultCell2 = Sheets("Report").Range("$H$4:$H$13")
    Set LookupValueCell2 = Sheets("Report").Range("$G$4:$H$400")
    Set LookupVector2 = Sheets("Main").Range("$A$4:$A$400")
    Set ResultVector2 = Sheets("Main").Range("$B$4:$B$372")

ResultCell2 = WorksheetFunction.Lookup(LookupValueCell2, LookupVector2, ResultVector2)
 
DateString = Format(Now, "DDMMMYYYY")
[B1].Value = DateString
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
This was my sample sheets, and report outcome wanted. Thank you

Book1
AB
1SKUTotal
2BrandA0011047
3BrandA0022634
4BrandA003981
5BrandA0041648
6BrandA0052163
7BrandA0062180
8BrandA0072252
9BrandA0081680
10BrandA0091074
11BrandA010901
12BrandA0111348
13BrandA0121558
14BrandA013250
15BrandA014252
16BrandA015222
17BrandA016285
18BrandA017659
19BrandA0181098
20BrandA0191585
21BrandA020220
22BrandB700150
23BrandB70022000
24BrandB700325
25BrandB700535
26BrandB7006552
27BrandB701141
28BrandB70122
29BrandB701458
30BrandB701656
31BrandB701757
32BrandB701858
33BrandB701959
34BrandC0160
35BrandC0261
36BrandC0362
37BrandC0463
38BrandC0564
39BrandC0665
40BrandC0766
41BrandC0867
42BrandC0968
43BrandC1069
Main


Book1
ABCDEFGH
126-Sep-24
2
3
4BrandA003981BrandB700150BrandC0160
5BrandA0041648BrandB70022000BrandC0261
6BrandA0052163BrandB700325BrandC0362
7BrandA0062180BrandB700535BrandC0463
8BrandA0072252BrandB7006552BrandC0564
9BrandA0081680BrandB701141BrandC0665
10BrandA0091074BrandB70122BrandC0766
11BrandA010901BrandB701458BrandC0867
12BrandA0111348BrandB701656BrandC0968
13BrandA0121558BrandB701757BrandC1069
14BrandA013250BrandB701858
15BrandA014252BrandB701959
16BrandA015222
17BrandA016285
18BrandA017659
19BrandA0181098
20BrandA0191585
21BrandA020220
Report
 
Upvote 0
Brand A 2 column
Brand B 2 column
Brand C 2 column
View attachment 117349
Give this a go.

VBA Code:
Public Sub subFilterBrands()
Dim arrBrands() As Variant
Dim i As Integer
Dim rngData As Range
Dim WsReport As Worksheet
Dim WsMain As Worksheet
Dim Wb As Workbook
Dim Q As String
Dim strFormula As String

  ActiveWorkbook.Save
  
  Set Wb = ActiveWorkbook
  
  Q = Chr(34)
    
  Set WsMain = Wb.Sheets("Main")
  
  WsMain.Activate
   
  strFormula = "=SUBSTITUTE($A2,TEXTJOIN(" & Q & Q & ",TRUE,IFERROR((MID($A2,ROW(INDIRECT(" & Q & "1:" & Q & "&LEN($A2))),1)*1)," & Q & Q & "))," & Q & Q & ",1)"
  
  WsMain.Range("B1").EntireColumn.Insert
  
  With WsMain.Range("A1").CurrentRegion.Columns(1)
  
    With .Offset(1, 1).Resize(.Rows.Count, 1)
      .Cells(1).Offset(-1, 0).Value = "Brand"
      .Formula2 = strFormula
      .Value = .Value
    End With
  
  End With
      
  Application.DisplayAlerts = False
  On Error Resume Next
  Wb.Worksheets("Report").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  
  Set WsReport = ActiveWorkbook.Sheets.Add(After:=Wb.Sheets(Wb.Sheets.Count))

  WsReport.Name = "Report"
  
  With WsMain.Range("A1").CurrentRegion
  
    With .Offset(1, 0).Resize(.Rows.Count, .Columns.Count)
    
      arrBrands = Evaluate("SORT(UNIQUE(" & WsMain.Name & "!" & .Columns(2).Address & "),1)")
    
      For i = LBound(arrBrands) To UBound(arrBrands) - 1
            
        strFormula = "=CHOOSECOLS(FILTER(" & WsMain.Name & "!" & .Address & "," & WsMain.Name & "!" & .Columns(2).Address & "=" & Q & arrBrands(i, 1) & Q & ",""""),1,14)"
    
        WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula
    
      Next i
  
    End With
    
  End With
    
  With WsReport
    .UsedRange.Value = WsReport.UsedRange.Value
    .Cells.EntireColumn.AutoFit
  End With
  
  WsMain.Range("B1").EntireColumn.Delete
  
  MsgBox "Reports Compiled.", vbOKOnly, "Confirmation"
  
End Sub
 
Upvote 0
wasn't able to show any info
this was shown in sample excel,
#VALUE!#VALUE!#VALUE!


For my main excel nothing shown on report sheets and error 1004 on "WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula"
1727361607842.png

as for main sheet created column B on this formula.
1727361753796.png
 
Upvote 0
wasn't able to show any info
this was shown in sample excel,
#VALUE!#VALUE!#VALUE!


For my main excel nothing shown on report sheets and error 1004 on "WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula"
View attachment 117354
as for main sheet created column B on this formula.
View attachment 117355
Rename your Main sheet and create a Main sheet from this data and then run the code.

Column B in Main will need to be deleted. This is used to seperate the Brand and the Product for the filter.

Filtering different brands of items into new sheet.xlsm
ABCDEFGHIJKLM
1BrandCOLUMN $B$1COLUMN $C$1COLUMN $D$1COLUMN $E$1COLUMN $F$1COLUMN $G$1COLUMN $H$1COLUMN $I$1COLUMN $J$1COLUMN $K$1COLUMN $L$1COLUMN $M$1
2BrandE002$B$2$C$2$D$2$E$2$F$2$G$2$H$2$I$2$J$2$K$2$L$2$M$2
3BrandC003$B$3$C$3$D$3$E$3$F$3$G$3$H$3$I$3$J$3$K$3$L$3$M$3
4BrandE004$B$4$C$4$D$4$E$4$F$4$G$4$H$4$I$4$J$4$K$4$L$4$M$4
5BrandC005$B$5$C$5$D$5$E$5$F$5$G$5$H$5$I$5$J$5$K$5$L$5$M$5
6BrandE006$B$6$C$6$D$6$E$6$F$6$G$6$H$6$I$6$J$6$K$6$L$6$M$6
7BrandB007$B$7$C$7$D$7$E$7$F$7$G$7$H$7$I$7$J$7$K$7$L$7$M$7
8BrandC008$B$8$C$8$D$8$E$8$F$8$G$8$H$8$I$8$J$8$K$8$L$8$M$8
9BrandA009$B$9$C$9$D$9$E$9$F$9$G$9$H$9$I$9$J$9$K$9$L$9$M$9
10BrandC0010$B$10$C$10$D$10$E$10$F$10$G$10$H$10$I$10$J$10$K$10$L$10$M$10
11BrandF0011$B$11$C$11$D$11$E$11$F$11$G$11$H$11$I$11$J$11$K$11$L$11$M$11
12BrandE0012$B$12$C$12$D$12$E$12$F$12$G$12$H$12$I$12$J$12$K$12$L$12$M$12
13BrandC0013$B$13$C$13$D$13$E$13$F$13$G$13$H$13$I$13$J$13$K$13$L$13$M$13
14BrandC0014$B$14$C$14$D$14$E$14$F$14$G$14$H$14$I$14$J$14$K$14$L$14$M$14
15BrandA0015$B$15$C$15$D$15$E$15$F$15$G$15$H$15$I$15$J$15$K$15$L$15$M$15
16BrandE0016$B$16$C$16$D$16$E$16$F$16$G$16$H$16$I$16$J$16$K$16$L$16$M$16
17BrandF0017$B$17$C$17$D$17$E$17$F$17$G$17$H$17$I$17$J$17$K$17$L$17$M$17
18BrandA0018$B$18$C$18$D$18$E$18$F$18$G$18$H$18$I$18$J$18$K$18$L$18$M$18
19BrandD0019$B$19$C$19$D$19$E$19$F$19$G$19$H$19$I$19$J$19$K$19$L$19$M$19
20BrandC0020$B$20$C$20$D$20$E$20$F$20$G$20$H$20$I$20$J$20$K$20$L$20$M$20
21BrandD0021$B$21$C$21$D$21$E$21$F$21$G$21$H$21$I$21$J$21$K$21$L$21$M$21
22BrandD0022$B$22$C$22$D$22$E$22$F$22$G$22$H$22$I$22$J$22$K$22$L$22$M$22
23BrandA0023$B$23$C$23$D$23$E$23$F$23$G$23$H$23$I$23$J$23$K$23$L$23$M$23
24BrandC0024$B$24$C$24$D$24$E$24$F$24$G$24$H$24$I$24$J$24$K$24$L$24$M$24
25BrandD0025$B$25$C$25$D$25$E$25$F$25$G$25$H$25$I$25$J$25$K$25$L$25$M$25
26BrandB0026$B$26$C$26$D$26$E$26$F$26$G$26$H$26$I$26$J$26$K$26$L$26$M$26
27BrandF0027$B$27$C$27$D$27$E$27$F$27$G$27$H$27$I$27$J$27$K$27$L$27$M$27
28BrandB0028$B$28$C$28$D$28$E$28$F$28$G$28$H$28$I$28$J$28$K$28$L$28$M$28
29BrandD0029$B$29$C$29$D$29$E$29$F$29$G$29$H$29$I$29$J$29$K$29$L$29$M$29
30BrandA0030$B$30$C$30$D$30$E$30$F$30$G$30$H$30$I$30$J$30$K$30$L$30$M$30
31BrandE0031$B$31$C$31$D$31$E$31$F$31$G$31$H$31$I$31$J$31$K$31$L$31$M$31
32BrandC0032$B$32$C$32$D$32$E$32$F$32$G$32$H$32$I$32$J$32$K$32$L$32$M$32
33BrandD0033$B$33$C$33$D$33$E$33$F$33$G$33$H$33$I$33$J$33$K$33$L$33$M$33
34BrandF0034$B$34$C$34$D$34$E$34$F$34$G$34$H$34$I$34$J$34$K$34$L$34$M$34
35BrandA0035$B$35$C$35$D$35$E$35$F$35$G$35$H$35$I$35$J$35$K$35$L$35$M$35
36BrandF0036$B$36$C$36$D$36$E$36$F$36$G$36$H$36$I$36$J$36$K$36$L$36$M$36
37BrandE0037$B$37$C$37$D$37$E$37$F$37$G$37$H$37$I$37$J$37$K$37$L$37$M$37
38BrandF0038$B$38$C$38$D$38$E$38$F$38$G$38$H$38$I$38$J$38$K$38$L$38$M$38
39BrandB0039$B$39$C$39$D$39$E$39$F$39$G$39$H$39$I$39$J$39$K$39$L$39$M$39
40BrandC0040$B$40$C$40$D$40$E$40$F$40$G$40$H$40$I$40$J$40$K$40$L$40$M$40
41BrandA0041$B$41$C$41$D$41$E$41$F$41$G$41$H$41$I$41$J$41$K$41$L$41$M$41
42BrandC0042$B$42$C$42$D$42$E$42$F$42$G$42$H$42$I$42$J$42$K$42$L$42$M$42
43BrandF0043$B$43$C$43$D$43$E$43$F$43$G$43$H$43$I$43$J$43$K$43$L$43$M$43
44BrandB0044$B$44$C$44$D$44$E$44$F$44$G$44$H$44$I$44$J$44$K$44$L$44$M$44
45BrandA0045$B$45$C$45$D$45$E$45$F$45$G$45$H$45$I$45$J$45$K$45$L$45$M$45
46BrandB0046$B$46$C$46$D$46$E$46$F$46$G$46$H$46$I$46$J$46$K$46$L$46$M$46
47BrandC0047$B$47$C$47$D$47$E$47$F$47$G$47$H$47$I$47$J$47$K$47$L$47$M$47
48BrandD0048$B$48$C$48$D$48$E$48$F$48$G$48$H$48$I$48$J$48$K$48$L$48$M$48
49BrandA0049$B$49$C$49$D$49$E$49$F$49$G$49$H$49$I$49$J$49$K$49$L$49$M$49
50BrandB0050$B$50$C$50$D$50$E$50$F$50$G$50$H$50$I$50$J$50$K$50$L$50$M$50
51BrandD0051$B$51$C$51$D$51$E$51$F$51$G$51$H$51$I$51$J$51$K$51$L$51$M$51
52BrandC0052$B$52$C$52$D$52$E$52$F$52$G$52$H$52$I$52$J$52$K$52$L$52$M$52
53BrandB0053$B$53$C$53$D$53$E$53$F$53$G$53$H$53$I$53$J$53$K$53$L$53$M$53
54BrandC0054$B$54$C$54$D$54$E$54$F$54$G$54$H$54$I$54$J$54$K$54$L$54$M$54
55BrandC0055$B$55$C$55$D$55$E$55$F$55$G$55$H$55$I$55$J$55$K$55$L$55$M$55
56BrandB0056$B$56$C$56$D$56$E$56$F$56$G$56$H$56$I$56$J$56$K$56$L$56$M$56
57BrandB0057$B$57$C$57$D$57$E$57$F$57$G$57$H$57$I$57$J$57$K$57$L$57$M$57
58BrandC0058$B$58$C$58$D$58$E$58$F$58$G$58$H$58$I$58$J$58$K$58$L$58$M$58
59BrandE0059$B$59$C$59$D$59$E$59$F$59$G$59$H$59$I$59$J$59$K$59$L$59$M$59
60BrandB0060$B$60$C$60$D$60$E$60$F$60$G$60$H$60$I$60$J$60$K$60$L$60$M$60
61BrandC0061$B$61$C$61$D$61$E$61$F$61$G$61$H$61$I$61$J$61$K$61$L$61$M$61
62BrandE0062$B$62$C$62$D$62$E$62$F$62$G$62$H$62$I$62$J$62$K$62$L$62$M$62
63BrandB0063$B$63$C$63$D$63$E$63$F$63$G$63$H$63$I$63$J$63$K$63$L$63$M$63
64BrandF0064$B$64$C$64$D$64$E$64$F$64$G$64$H$64$I$64$J$64$K$64$L$64$M$64
65BrandB0065$B$65$C$65$D$65$E$65$F$65$G$65$H$65$I$65$J$65$K$65$L$65$M$65
66BrandC0066$B$66$C$66$D$66$E$66$F$66$G$66$H$66$I$66$J$66$K$66$L$66$M$66
67BrandF0067$B$67$C$67$D$67$E$67$F$67$G$67$H$67$I$67$J$67$K$67$L$67$M$67
68BrandF0068$B$68$C$68$D$68$E$68$F$68$G$68$H$68$I$68$J$68$K$68$L$68$M$68
69BrandD0069$B$69$C$69$D$69$E$69$F$69$G$69$H$69$I$69$J$69$K$69$L$69$M$69
70BrandC0070$B$70$C$70$D$70$E$70$F$70$G$70$H$70$I$70$J$70$K$70$L$70$M$70
71BrandE0071$B$71$C$71$D$71$E$71$F$71$G$71$H$71$I$71$J$71$K$71$L$71$M$71
72BrandB0072$B$72$C$72$D$72$E$72$F$72$G$72$H$72$I$72$J$72$K$72$L$72$M$72
73BrandB0073$B$73$C$73$D$73$E$73$F$73$G$73$H$73$I$73$J$73$K$73$L$73$M$73
74BrandA0074$B$74$C$74$D$74$E$74$F$74$G$74$H$74$I$74$J$74$K$74$L$74$M$74
75BrandD0075$B$75$C$75$D$75$E$75$F$75$G$75$H$75$I$75$J$75$K$75$L$75$M$75
76BrandE0076$B$76$C$76$D$76$E$76$F$76$G$76$H$76$I$76$J$76$K$76$L$76$M$76
Main



Here is a sample of what I get.


Filtering different brands of items into new sheet.xlsm
ABCDEFGHIJK
1
2
3
4BrandA009$M$9BrandB007$M$7BrandC003$M$3BrandD0019$M$19
5BrandA0015$M$15BrandB0026$M$26BrandC005$M$5BrandD0021$M$21
6BrandA0018$M$18BrandB0028$M$28BrandC008$M$8BrandD0022$M$22
7BrandA0023$M$23BrandB0039$M$39BrandC0010$M$10BrandD0025$M$25
8BrandA0030$M$30BrandB0044$M$44BrandC0013$M$13BrandD0029$M$29
9BrandA0035$M$35BrandB0046$M$46BrandC0014$M$14BrandD0033$M$33
10BrandA0041$M$41BrandB0050$M$50BrandC0020$M$20BrandD0048$M$48
11BrandA0045$M$45BrandB0053$M$53BrandC0024$M$24BrandD0051$M$51
12BrandA0049$M$49BrandB0056$M$56BrandC0032$M$32BrandD0069$M$69
13BrandA0074$M$74BrandB0057$M$57BrandC0040$M$40BrandD0075$M$75
14BrandA0082$M$82BrandB0060$M$60BrandC0042$M$42BrandD0092$M$92
15BrandA0088$M$88BrandB0063$M$63BrandC0047$M$47BrandD0093$M$93
16BrandA00104$M$104BrandB0065$M$65BrandC0052$M$52BrandD0097$M$97
17BrandA00109$M$109BrandB0072$M$72BrandC0054$M$54BrandD00103$M$103
18BrandA00117$M$117BrandB0073$M$73BrandC0055$M$55BrandD00108$M$108
19BrandA00123$M$123BrandB0077$M$77BrandC0058$M$58BrandD00110$M$110
20BrandA00132$M$132BrandB0080$M$80BrandC0061$M$61BrandD00111$M$111
21BrandA00136$M$136BrandB0087$M$87BrandC0066$M$66BrandD00112$M$112
22BrandA00153$M$153BrandB0099$M$99BrandC0070$M$70BrandD00113$M$113
23BrandA00155$M$155BrandB00115$M$115BrandC0081$M$81BrandD00114$M$114
24BrandA00160$M$160BrandB00120$M$120BrandC0083$M$83BrandD00121$M$121
Report
 
Upvote 0
I do get your results, but it was limited to the word "brand". May i know which part of the code that i need to change to my code? Thanks
 
Upvote 0
This are sample of the main sheet.
227686c5-9d83-40e5-b33c-6a42c088dd61_Inventory_Products_Stock_Level_Report.xlsm
ABCDEFGHIJKLM
2SKUTotal
3HTDKG107914.009.14
4HTDKG108900.009
5HTDKG1091,170.0011.7
6HTDKG110490.004.9
7HTDKG1111,500.0015
8HTK0012,318.0023.18
9HTK0021,518.0015.18
10HTK0032,866.0028.66
11HTK005240.002.4
12HTK0062,318.0023.18
13HTK014546.005.46
14HTK0161,505.0015.05
16HTDG012,198.0021.98
18HTDG022,214.0022.14
Main
Cell Formulas
RangeFormula
M3:M14,M16,M18M3=$K3/100
 
Upvote 0

Forum statistics

Threads
1,223,104
Messages
6,170,125
Members
452,303
Latest member
c4cstore

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