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
 
Use your data with the first row deleted and no blank rows.

Use this code. I used the data as in post 24.

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 & ",""""),2,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
Same error as #29, still limited to "brand"
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
1727457722862.png


Inventory_Products_Stock_Level_Report_testing.xlsm
ABCDEFGHIJKLMN
1SKUBrandUnit extract from SKUProductUnitAvailableAllocatedAvailableAllocatedAvailableAllocatedAvailableAllocatedTotal
2HTDKG107=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model1 - SeriesA / HTDKG107$E$2$F$2$G$2$H$2$I$2$J$2914.00$L$39.14
3HTDKG108=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model1 - SeriesB / HTDKG108$E$3$F$3$G$3$H$3$I$3$J$3900.00$L$49
4HTDKG109=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model1 - SeriesC / HTDKG109$E$4$F$4$G$4$H$4$I$4$J$41,170.00$L$511.7
5HTDKG110=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model1 - SeriesD / HTDKG110$E$5$F$5$G$5$H$5$I$5$J$5490.00$L$64.9
6HTDKG111=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model1 - SeriesE / HTDKG111$E$6$F$6$G$6$H$6$I$6$J$61,500.00$L$715
7HTK001=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesF / HTK001$E$7$F$7$G$7$H$7$I$7$J$72,318.00$L$823.18
8HTK002=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesG / HTK002$E$8$F$8$G$8$H$8$I$8$J$81,518.00$L$915.18
9HTK003=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesH / HTK003$E$9$F$9$G$9$H$9$I$9$J$92,866.00$L$1028.66
10HTK005=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesI / HTK005$E$10$F$10$G$10$H$10$I$10$J$10240.00$L$112.4
11HTK006=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesJ / HTK006$E$11$F$11$G$11$H$11$I$11$J$112,318.00$L$1223.18
12HTK014=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesK / HTK014$E$12$F$12$G$12$H$12$I$12$J$12546.00$L$135.46
13HTK016=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model2 - SeriesL / HTK016$E$13$F$13$G$13$H$13$I$13$J$131,505.00$L$1415.05
15HTDG01=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG01$E$15$F$15$G$15$H$15$I$15$J$152,198.00$L$1621.98
17HTDG02=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG02$E$17$F$17$G$17$H$17$I$17$J$172,214.00$L$1822.14
19HTDG03=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG03$E$19$F$19$G$19$H$19$I$19$J$191,850.00$L$2018.5
21HTDG04=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG04$E$21$F$21$G$21$H$21$I$21$J$211,384.00$L$2213.84
23HTDG05=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG05$E$23$F$23$G$23$H$23$I$23$J$23740.00$L$247.4
25HTDG06=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG06$E$25$F$25$G$25$H$25$I$25$J$252,338.00$L$2623.38
27HTDG07=SUBSTITUTE($A2,TEXTJOIN("",TRUE,IFERROR((MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)*1),"")),"",1)Model3 / HTDG07$E$27$F$27$G$27$H$27$I$27$J$271,980.00$L$2819.8
Main
Cell Formulas
RangeFormula
N2:N13,N15,N17,N19,N21,N23,N25,N27N2=$L2/100
 
Upvote 0
I have tried delete the first row, but there is 1004 error on this code "WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula = False". After i change my HTDKG, HTK and HTDG to BrandA, BrandB, and BrandC, the report came working correctly.
So is there a way to filter from "Brand" change to my sku code?
Where is this line in the code?

WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula = False
 
Upvote 0
Where is this line in the code?

WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula = False
This was the line WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula, when roll over shown this. WsReport.Range("A4").Offset(0, (i - 1) * 3).Formula2 = strFormula = False
 
Upvote 0
See if this works as you want.
Code:
Sub test()
    Dim x, e, t&
    If Not [isref('result'!a1)] Then Sheets.Add.Name = "Result"
    Sheets("result").Cells.Clear: t = 1
    With Sheets("main").[a1].CurrentRegion.Resize(, 2).offset(1)
        x = .Parent.[unique(iferror(textbefore(a3:a20000,sequence(10,,0),,,1),""))]
        For Each e In x
            If e <> "" Then
                .AutoFilter 1, e & "*"
                .Offset(1).Copy Sheets("result").Cells(4, t)
                t = t + 3
            End If
        Next
    End With
End Sub
 
Last edited:
Upvote 0
See if this works as you want.
Code:
Sub test()
    Dim x, e, t&
    If Not [isref('result'!a1)] Then Sheets.Add.Name = "Result"
    Sheets("result").Cells.Clear: t = 1
    With Sheets("main").[a1].CurrentRegion.Resize(, 2).offset(1)
        x = .Parent.[unique(iferror(textbefore(a3:a20000,sequence(10,,0),,,1),""))]
        For Each e In x
            If e <> "" Then
                .AutoFilter 1, e & "*"
                .Offset(1).Copy Sheets("result").Cells(4, t)
                t = t + 3
            End If
        Next
    End With
End Sub
Yes, the outcome layout is what i wanted, but the second data taken was on column B instead of column M.
And is there a way to remain the data on the main sheet as well? Thanks

Inventory_Products_Stock_Level_Report_testing.xlsm
ABCDEFGHI
1
2
3
4HTDKG108HTK001HTDG01
5HTDKG109HTK002HTDG02
6HTDKG110HTK003HTDG03
7HTDKG111HTK005HTDG04
8HTK006HTDG05
9HTK014HTDG06
10HTK016HTDG07
11HTDG08
12HTDG09
13HTDG10
14HTDG11
15HTDG12
16
17
18
Result
 
Upvote 0
Like this?
Code:
Sub test()
    Dim x, e, t&
    If Not [isref('result'!a1)] Then Sheets.Add.Name = "Result"
    Sheets("result").Cells.Clear: t = 1
    With Sheets("main").[a1].CurrentRegion.Offset(1)
        x = .Parent.[unique(iferror(textbefore(a3:a20000,sequence(10,,0),,,1),""))]
        For Each e In x
            If e <> "" Then
                .AutoFilter 1, e & "*"
                With .Offset(1)
                    Union(.Columns("a"), .Columns("m")).Copy Sheets("result").Cells(4, t)
                End With
                t = t + 3
            End If
        Next
        .AutoFilter
    End With
End Sub

Edit;
And is there a way to remain the data on the main sheet as well?
 
Last edited:
Upvote 0
Like this?
Code:
Sub test()
    Dim x, e, t&
    If Not [isref('result'!a1)] Then Sheets.Add.Name = "Result"
    Sheets("result").Cells.Clear: t = 1
    With Sheets("main").[a1].CurrentRegion.Offset(1)
        x = .Parent.[unique(iferror(textbefore(a3:a20000,sequence(10,,0),,,1),""))]
        For Each e In x
            If e <> "" Then
                .AutoFilter 1, e & "*"
                With .Offset(1)
                    Union(.Columns("a"), .Columns("m")).Copy Sheets("result").Cells(4, t)
                End With
                t = t + 3
            End If
        Next
    End With
End Sub
Yes, this i was what i wanted Thank you.
This was before i run your code (Is there a way to remain this primary data in the main sheets?)
SKUUnit extract from SKUProductUnitAvailableAllocatedAvailableAllocatedAvailableAllocatedAvailableAllocatedTotal
HTDKG107Model1 - SeriesA / HTDKG107$E$2$F$2$G$2$H$2$I$2$J$2914.00$L$39.14
HTDKG108Model1 - SeriesB / HTDKG108$E$3$F$3$G$3$H$3$I$3$J$3900.00$L$49
HTDKG109Model1 - SeriesC / HTDKG109$E$4$F$4$G$4$H$4$I$4$J$41,170.00$L$511.7
HTDKG110Model1 - SeriesD / HTDKG110$E$5$F$5$G$5$H$5$I$5$J$5490.00$L$64.9
HTDKG111Model1 - SeriesE / HTDKG111$E$6$F$6$G$6$H$6$I$6$J$61,500.00$L$715
HTK001Model2 - SeriesF / HTK001$E$7$F$7$G$7$H$7$I$7$J$72,318.00$L$823.18
HTK002Model2 - SeriesG / HTK002$E$8$F$8$G$8$H$8$I$8$J$81,518.00$L$915.18
HTK003Model2 - SeriesH / HTK003$E$9$F$9$G$9$H$9$I$9$J$92,866.00$L$1028.66
HTK005Model2 - SeriesI / HTK005$E$10$F$10$G$10$H$10$I$10$J$10240.00$L$112.4
HTK006Model2 - SeriesJ / HTK006$E$11$F$11$G$11$H$11$I$11$J$112,318.00$L$1223.18
HTK014Model2 - SeriesK / HTK014$E$12$F$12$G$12$H$12$I$12$J$12546.00$L$135.46
HTK016Model2 - SeriesL / HTK016$E$13$F$13$G$13$H$13$I$13$J$131,505.00$L$1415.05
HTDG01Model3 / HTDG01$E$15$F$15$G$15$H$15$I$15$J$152,198.00$L$1621.98
HTDG02Model3 / HTDG02$E$17$F$17$G$17$H$17$I$17$J$172,214.00$L$1822.14
HTDG03Model3 / HTDG03$E$19$F$19$G$19$H$19$I$19$J$191,850.00$L$2018.5
HTDG04Model3 / HTDG04$E$21$F$21$G$21$H$21$I$21$J$211,384.00$L$2213.84
HTDG05Model3 / HTDG05$E$23$F$23$G$23$H$23$I$23$J$23740.00$L$247.4
HTDG06Model3 / HTDG06$E$25$F$25$G$25$H$25$I$25$J$252,338.00$L$2623.38


This is after i run your code
SKUUnit extract from SKUProductUnitAvailableAllocatedAvailableAllocatedAvailableAllocatedAvailableAllocatedTotal
Grand Total0.000.000.000.000.000.000.000.00
 
Last edited:
Upvote 0
So, you don't want to change hide/unhide any row(s) in Main sheet...
try this one then.
Code:
Sub test()
    Dim e, w, x, y, t&
    If Not [isref('result'!a1)] Then Sheets.Add.Name = "Result"
    Sheets("result").Cells.Clear: t = 1
    With Sheets("main").[a1].CurrentRegion.Offset(1)
        x = .Parent.[unique(iferror(textbefore(a3:a20000,sequence(10,,0),,,1),""))]
        For Each e In x
            If (e <> "") * (LCase$(e) <> "grand total") Then
                y = Filter(.Parent.Evaluate("transpose(if(left(" & .Columns(1).Address & _
                ",len(""" & e & """))=""" & e & """,row(1:" & .Rows.Count & ")))"), False, 0)
                w = Application.Index(.Value, Application.Transpose(y), [{1,13}])
                Sheets("result").Cells(4, t).Resize(UBound(y) + 1, 2) = w
                t = t + 3
            End If
        Next
        .AutoFilter
    End With
End Sub
 
Upvote 1
Solution

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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