Filtering different brands of items into new sheet

alvin97 · Sep 26, 2024

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

After

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

HighAndWilder · Sep 27, 2024

alvin97 said:
Start from Row3 to Row371

What is in row 1?

alvin97 · Sep 27, 2024

HighAndWilder said:
What is in row 1?

Nothing on ColumnA&M , just different location code on column E to L

HighAndWilder · Sep 27, 2024

alvin97 said:
Nothing on ColumnA&M , just different location code on column E to L

Can you please use XL2BB to send me a good representation of your data?

Mr Excel XL2BB

alvin97 · Sep 27, 2024

Column E+G+I=K (this was fomulate in another system)
Column F+H+J=L (this was fomulate in another system)

Inventory_Products_Stock_Level_Report_testing.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Location1

Location 2

Location3

Grand Total

2

SKU

Unit extract from SKU

Product

Unit

Available

Allocated

Available

Allocated

Available

Allocated

Available

Allocated

Total

3

HTDKG107

Model1 - SeriesA / HTDKG107

$E$2

$F$2

$G$2

$H$2

$I$2

$J$2

914.00

$L$3

9.14

4

HTDKG108

Model1 - SeriesB / HTDKG108

$E$3

$F$3

$G$3

$H$3

$I$3

$J$3

900.00

$L$4

9

5

HTDKG109

Model1 - SeriesC / HTDKG109

$E$4

$F$4

$G$4

$H$4

$I$4

$J$4

1,170.00

$L$5

11.7

6

HTDKG110

Model1 - SeriesD / HTDKG110

$E$5

$F$5

$G$5

$H$5

$I$5

$J$5

490.00

$L$6

4.9

7

HTDKG111

Model1 - SeriesE / HTDKG111

$E$6

$F$6

$G$6

$H$6

$I$6

$J$6

1,500.00

$L$7

15

8

HTK001

Model2 - SeriesF / HTK001

$E$7

$F$7

$G$7

$H$7

$I$7

$J$7

2,318.00

$L$8

23.18

9

HTK002

Model2 - SeriesG / HTK002

$E$8

$F$8

$G$8

$H$8

$I$8

$J$8

1,518.00

$L$9

15.18

10

HTK003

Model2 - SeriesH / HTK003

$E$9

$F$9

$G$9

$H$9

$I$9

$J$9

2,866.00

$L$10

28.66

11

HTK005

Model2 - SeriesI / HTK005

$E$10

$F$10

$G$10

$H$10

$I$10

$J$10

240.00

$L$11

2.4

12

HTK006

Model2 - SeriesJ / HTK006

$E$11

$F$11

$G$11

$H$11

$I$11

$J$11

2,318.00

$L$12

23.18

13

HTK014

Model2 - SeriesK / HTK014

$E$12

$F$12

$G$12

$H$12

$I$12

$J$12

546.00

$L$13

5.46

14

HTK016

Model2 - SeriesL / HTK016

$E$13

$F$13

$G$13

$H$13

$I$13

$J$13

1,505.00

$L$14

15.05

16

HTDG01

Model3 / HTDG01

$E$15

$F$15

$G$15

$H$15

$I$15

$J$15

2,198.00

$L$16

21.98

18

HTDG02

Model3 / HTDG02

$E$17

$F$17

$G$17

$H$17

$I$17

$J$17

2,214.00

$L$18

22.14

20

HTDG03

Model3 / HTDG03

$E$19

$F$19

$G$19

$H$19

$I$19

$J$19

1,850.00

$L$20

18.5

22

HTDG04

Model3 / HTDG04

$E$21

$F$21

$G$21

$H$21

$I$21

$J$21

1,384.00

$L$22

13.84

24

HTDG05

Model3 / HTDG05

$E$23

$F$23

$G$23

$H$23

$I$23

$J$23

740.00

$L$24

7.4

Main

Cell Formulas
Range	Formula
M3:M14,M16,M18,M20,M22,M24	M3	=$K3/100

HighAndWilder · Sep 27, 2024

alvin97 said:
Column E+G+I=K (this was fomulate in another system)
Column F+H+J=L (this was fomulate in another system)

Inventory_Products_Stock_Level_Report_testing.xlsm
A B C D E F G H I J K L M
1 Location1 Location1 Location 2 Location 2 Location3 Location3 Grand Total Grand Total
2 SKU Unit extract from SKU Product Unit Available Allocated Available Allocated Available Allocated Available Allocated Total
3 HTDKG107 Model1 - SeriesA / HTDKG107 $E$2 $F$2 $G$2 $H$2 $I$2 $J$2 914.00 $L$3 9.14
4 HTDKG108 Model1 - SeriesB / HTDKG108 $E$3 $F$3 $G$3 $H$3 $I$3 $J$3 900.00 $L$4 9
5 HTDKG109 Model1 - SeriesC / HTDKG109 $E$4 $F$4 $G$4 $H$4 $I$4 $J$4 1,170.00 $L$5 11.7
6 HTDKG110 Model1 - SeriesD / HTDKG110 $E$5 $F$5 $G$5 $H$5 $I$5 $J$5 490.00 $L$6 4.9
7 HTDKG111 Model1 - SeriesE / HTDKG111 $E$6 $F$6 $G$6 $H$6 $I$6 $J$6 1,500.00 $L$7 15
8 HTK001 Model2 - SeriesF / HTK001 $E$7 $F$7 $G$7 $H$7 $I$7 $J$7 2,318.00 $L$8 23.18
9 HTK002 Model2 - SeriesG / HTK002 $E$8 $F$8 $G$8 $H$8 $I$8 $J$8 1,518.00 $L$9 15.18
10 HTK003 Model2 - SeriesH / HTK003 $E$9 $F$9 $G$9 $H$9 $I$9 $J$9 2,866.00 $L$10 28.66
11 HTK005 Model2 - SeriesI / HTK005 $E$10 $F$10 $G$10 $H$10 $I$10 $J$10 240.00 $L$11 2.4
12 HTK006 Model2 - SeriesJ / HTK006 $E$11 $F$11 $G$11 $H$11 $I$11 $J$11 2,318.00 $L$12 23.18
13 HTK014 Model2 - SeriesK / HTK014 $E$12 $F$12 $G$12 $H$12 $I$12 $J$12 546.00 $L$13 5.46
14 HTK016 Model2 - SeriesL / HTK016 $E$13 $F$13 $G$13 $H$13 $I$13 $J$13 1,505.00 $L$14 15.05
16 HTDG01 Model3 / HTDG01 $E$15 $F$15 $G$15 $H$15 $I$15 $J$15 2,198.00 $L$16 21.98
18 HTDG02 Model3 / HTDG02 $E$17 $F$17 $G$17 $H$17 $I$17 $J$17 2,214.00 $L$18 22.14
20 HTDG03 Model3 / HTDG03 $E$19 $F$19 $G$19 $H$19 $I$19 $J$19 1,850.00 $L$20 18.5
22 HTDG04 Model3 / HTDG04 $E$21 $F$21 $G$21 $H$21 $I$21 $J$21 1,384.00 $L$22 13.84
24 HTDG05 Model3 / HTDG05 $E$23 $F$23 $G$23 $H$23 $I$23 $J$23 740.00 $L$24 7.4
Main
Cell Formulas
Range Formula
M3:M14,M16,M18,M20,M22,M24 M3 =$K3/100

So do you want in the report, for example from row 3,
HTDKG107
or
Model1 - SeriesA / HTDKG107?

alvin97 · Sep 27, 2024

HighAndWilder said:
So do you want in the report, for example from row 3,
HTDKG107
or
Model1 - SeriesA / HTDKG107?

For my Report, I just need my sku (HTDKG107 A3) and my total (M3) and arrange in each brand in each Column.

For example this below (this is what i wanted as my final result)
A3:M3
A4:M4
and continue while arrange via HTDKG, HTK and HTDG



	27-Sep-24


HTDKG107	9.14	HTK001	23.18	HTDG01	21.98
HTDKG108	9	HTK002	15.18	HTDG02	22.14
HTDKG109	11.7	HTK003	28.66	HTDG03	18.5
HTDKG110	4.9	HTK005	2.4	HTDG04	13.84
HTDKG111	15	HTK006	23.18	HTDG05	7.4
		HTK014	5.46	HTDG06	23.38
		HTK016	15.05	HTDG07	19.8
				HTDG08	17.52
				HTDG09	14.8
				HTDG10	9.33
				HTDG11	7.33
				HTDG12	17.2

alvin97 · Sep 27, 2024

You have created what in wanted in thread #13 but that code only filter to the word "Brand". But I need to filter based on this (HTDKG, HTK and HTDG).
Thank you

HighAndWilder · Sep 27, 2024

alvin97 said:
You have created what in wanted in thread #13 but that code only filter to the word "Brand". But I need to filter based on this (HTDKG, HTK and HTDG).
Thank you

Try removing the first row. It is rare for data sets to more than one header row.

alvin97 · Sep 27, 2024

HighAndWilder said:
Try removing the first row. It is rare for data sets to more than one header row.

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?

HighAndWilder · Sep 27, 2024

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

Filtering different brands of items into new sheet

alvin97

New Member

HighAndWilder

Well-known Member

Excel Facts

alvin97

New Member

HighAndWilder

Well-known Member

alvin97

New Member

HighAndWilder

Well-known Member

alvin97

New Member

alvin97

New Member

HighAndWilder

Well-known Member

alvin97

New Member

HighAndWilder

Well-known Member

Similar threads

Forum statistics

Share this page

Filtering different brands of items into new sheet

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock