VBA Code

[brs]

I have a tab called Master in my workbook. This tab includes a ton of data. It has 8,300+ rows and columns from A-AF. Data is entered into the Master tab once a week, so the rows will continue to grow. One of the columns on the spreadsheet is "Customer ID." Let's say we have 100+ Customer ID's.

I'm wanting to extract every row that does not contain one of 15 different "Customer ID's" to another tab within the same workbook. Let's call this tab "Others." I want the "Others" tab to automatically update each time the "Master" tab is updated.

I'm decent using excel formulas, but I don't think a formula is the best answer here. From the research I've done on this, I need to write a VBA code, but I'm not sure where to start.

If anyone could provide step-by-step instructions on how to write the code for Dummy's, that would be greatly appreciated!

 

[alansidman]

If you are able to provide some sample data to a third party location, I can demonstrate a solution using "Power Query/Get and Transform Data" with an anti-join. This item is located on the Data Tab of Excel.

 

[brs]

If you are able to provide some sample data to a third party location, I can demonstrate a solution using "Power Query/Get and Transform Data" with an anti-join. This item is located on the Data Tab of Excel.

Thanks for your response. Could you provide steps on how to start the query?

 

[alansidman]

First steps
1. Click in a cell in the range you wish to bring to the PQ editor.
2. On the Data Tab, select Get & Transform and select From Table or Range
3. Click the box or unclick the box for headers
4. The PQ editor will open and you can now manipulate your data.

You will need to load the second table containing the list of valid customers in the same manner. Once both are loaded you can merge (join) the tables using an Anti join.

This might be helpful to look at to start

 

[DanteAmor]

Hi and welcome to MrExcel.

Do the following:
1. A "Master" sheet with your data, for example:

varios 05oct2021.xlsm
	A	B	C	D	E	F	G
1	Tit1	Tit2	Tit3	Tit4	Customer ID	Tit6	and so on …
2	data	data	data	data	id 1	data	data
3	data	data	data	data	id 2	data	data
4	data	data	data	data	id 3	data	data
5	data	data	data	data	id 4	data	data
6	data	data	data	data	id 5	data	data
7	data	data	data	data	id 1	data	data
8	data	data	data	data	id 2	data	data
9	data	data	data	data	id 3	data	data
10	data	data	data	data	id 4	data	data
11	data	data	data	data	id 5	data	data
12	data	data	data	data	id 1	data	data
13	data	data	data	data	id 2	data	data
14	data	data	data	data	id 3	data	data
15	data	data	data	data	id 4	data	data
16	data	data	data	data	id 5	data	data
17					and so on …
Master

2. A "Customer" sheet with the 15 customer IDs that you already know, starting at cell A1, for example:

varios 05oct2021.xlsm
	A
1	Customer ID
2	Id 1
3	Id 2
4	Id 3
Customer

3. An "Others" sheet

Put the following macro in a module. Updates the data marked in blue in the macro.

Sub FilerOthers()
  Dim dic As Object
  Dim a As Variant, b As Variant, c As Variant
  Dim col As String
  Dim i As Long, j As Long, k As Long, n As Long
  
  col = "E" 'change "E" to the Customer_ID column
  
  Set dic = CreateObject("Scripting.Dictionary")
  n = Columns(col).Column
  a = Sheets("Master").Range("A1:AF" & Sheets("Master").Range(col & Rows.Count).End(3).Row).Value
  b = Sheets("Customer").Range("A2:A" & Sheets("Customer").Range("A" & Rows.Count).End(3).Row).Value
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  dic.comparemode = vbTextCompare
  '
  For i = 1 To UBound(b)
    dic(b(i, 1)) = Empty
  Next
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, n)) Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        c(k, j) = a(i, j)
      Next
    End If
  Next
  
  With Sheets("Others")
    .Cells.ClearContents
    .Range("A1").Resize(k, UBound(c, 2)).Value = c
  End With
End Sub

Result on sheet "Others"

varios 05oct2021.xlsm
	A	B	C	D	E	F	G
1	Tit1	Tit2	Tit3	Tit4	Customer ID	Tit6	and so on …
2	data	data	data	data	id 4	data	data
3	data	data	data	data	id 5	data	data
4	data	data	data	data	id 4	data	data
5	data	data	data	data	id 5	data	data
6	data	data	data	data	id 4	data	data
7	data	data	data	data	id 5	data	data
8					and so on …
Others

____________________________
HOW TO INSTALL MACROs
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FilerOthers) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

Here is another macro that should also work, however, I am not sure if it is faster or slower than the code Dante posted.

Sub FilterOthers()
  Dim V As Variant, Cust As Variant
  Cust = Sheets("Customer").Range("A2", Sheets("Customer").Cells(Rows.Count, "A").End(xlUp))
  With Sheets("Master")
    For Each V In Cust
      .Columns("E").Replace V, "=""" & V & """", xlPart, , False, , False, False
    Next
    Sheets("Others").Cells.ClearContents
    .Columns("E").SpecialCells(xlConstants).EntireRow.Copy Sheets("Others").Range("A1")
    .Columns("E").Replace "=", "", xlPart
    .Columns("E").Replace """", "", xlPart
  End With
End Sub

 

[brs]

First steps
1. Click in a cell in the range you wish to bring to the PQ editor.
2. On the Data Tab, select Get & Transform and select From Table or Range
3. Click the box or unclick the box for headers
4. The PQ editor will open and you can now manipulate your data.

You will need to load the second table containing the list of valid customers in the same manner. Once both are loaded you can merge (join) the tables using an Anti join.

This might be helpful to look at to start

Ok, so I've made it through step 4 where the PQ Editor opens.

I know that I'm going to need to list the 15 customers ID's that I want to exclude, but I'm not sure where or how that should be done. I'm assuming that I will need to do another PQ Editor for those, but how do I get to another tab? Should I select "Close & Load?"

 

[alansidman]

Close and Load to Connection and then go to the tab that has the 15 IDs and load that into PQ Editor. Close and Load that to Connection also. Now you have the data in the PQ editor. Putting it in the Connection means that it is in the editor and you have not put it into a new table in Excel which is only clutter as you have the originals already and don't need to replicate them.