Check the first X characters of each cell in range against list

[TiagoRocha]

Hi all,

I have been struggling for a few days with this issue and I'd really appreciate some help:

I have 64 columns of data in one sheet with which I want to build a pivot table. In this pivot table I need to add a filter for the Product IDs columns and I filter out all the Product IDs that start with certain characters or numbers (which I have listed in a named range in another sheet).

These characters or numbers that the Products IDs cannot start with are of different lengths.

Can anyone help me figure out how to write the VBA code for this?

Many thanks.

 

[Col Delane]

Howdy & welcome to the Forum

It should be possible to do what you want without VBA, but depends on how your data is laid out.

I need to add a filter for the Product IDs columns

Do you have only one Product ID column (preferred) or multiple columns (uugh!)?​

the Product IDs that start with certain characters or numbers (which I have listed in a named range in another sheet).

How many of these Products do you have?
Can you provide some samples of Product IDs and the string of characters/numbers that would result in them being filtered out.
​

 

[TiagoRocha]

Howdy & welcome to the Forum

It should be possible to do what you want without VBA, but depends on how your data is laid out.

Do you have only one Product ID column (preferred) or multiple columns (uugh!)?​

How many of these Products do you have?
Can you provide some samples of Product IDs and the string of characters/numbers that would result in them being filtered out.
​

Hi Col

Thank you for your reply.

The Product IDs are all located at the same column. As this is to be a recurrent analysis of different data sets, the number of product ids is variable.

There's also an additional requirement to this filter which is, out of the product id start strings that have to be filtered out, one has to exceptions (exceptions to the exceptions, you might say).

Example of the data:

Sheet1 - Column A: Product ID list
thdj5m6g09
37462586097
djfh-tuet-ot=
eednkf56jt8
eedthcltj432
eedthbjdk56

Sheet2 - Column A: Start strings that will exclude the product ids from the pivot filter
3746
djf
eed

Sheet2 - Column B: Exceptions to column A
eedthc
eedthb

So, the pivot would only show the following product IDs:
thdj5m6g09
eedthcltj432
eedthbjdk56

 

[Col Delane]

Are those Product IDs for real , or are you just making stuff up for this Forum???

Are the strings you've listed in:

1. Sheet2 - Column A: Start strings that will exclude the product ids from the pivot filter
2. Sheet2 - Column B: Exceptions to column A

all the exclusions and exemptions, or are these just samples?
If just samples, how many are there in each category?

My initial thought is that the solution (which can be done with formulas rather than VBA) would best be implemented as follows:

1. Somewhere in the data table of 64 columns (on Sheet1) add three helper columns:
   1. Helper1: identify the Product IDs in Sheet1!A:A to be excluded in the first pass (Start strings that will exclude certain product IDs)
   2. Helper2: identify the IDs flagged in Helper1 which are to be reinstated for inclusion in the "valid" list of Products (Exceptions to Helper1/Sheet2 column A)
   3. Helper3: identifies whether the ID should be Included or Excluded, being Sheet1A:A less Helper1 add-back Helper2.
2. Your source range for the PT is then expanded to include Helper3 so that Helper3 becomes a field available to the PT which you can filter as desired to include or exclude the unwanted IDs.

 

[TiagoRocha]

Are those Product IDs for real , or are you just making stuff up for this Forum???

Are the strings you've listed in:

1. Sheet2 - Column A: Start strings that will exclude the product ids from the pivot filter
2. Sheet2 - Column B: Exceptions to column A

all the exclusions and exemptions, or are these just samples?
If just samples, how many are there in each category?

My initial thought is that the solution (which can be done with formulas rather than VBA) would best be implemented as follows:

1. Somewhere in the data table of 64 columns (on Sheet1) add three helper columns:
   1. Helper1: identify the Product IDs in Sheet1!A:A to be excluded in the first pass (Start strings that will exclude certain product IDs)
   2. Helper2: identify the IDs flagged in Helper1 which are to be reinstated for inclusion in the "valid" list of Products (Exceptions to Helper1/Sheet2 column A)
   3. Helper3: identifies whether the ID should be Included or Excluded, being Sheet1A:A less Helper1 add-back Helper2.
2. Your source range for the PT is then expanded to include Helper3 so that Helper3 becomes a field available to the PT which you can filter as desired to include or exclude the unwanted IDs.

Hi Col,

I ended up solving this with an additional column in the database table that feeds the pivot.

Please find below what I came up with:

Dim i As Long, j As Long, k As Long
Dim LastRow As Long
Dim LastRowII As Long
Dim LastRowIII As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim flg As Boolean

'Find the last row of data of the PID exceptions and the Exceptions to the PID Exceptions lists
LastRowII = ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
LastRowIII = ThisWorkbook.Sheets("Sheet1").Range("B1").End(xlDown).Row


'Create an extra column of data that identifies the PIDs that are to be excluded from the analysis
Set ws1 = ActiveWorkbook.Sheets("Custom Inventory and Segment")
Set ws2 = ThisWorkbook.Sheets("Sheet1")


For i = 2 To LastRow
  For j = 2 To LastRowII
    For k = 2 To LastRowIII
      flg = False
      If ws2.Range("B" & k).Value = Left(ws1.Range("AE" & i), Len(ws2.Range("B" & k))) Then
          flg = True
          Exit For
      ElseIf ws2.Range("A" & j).Value = Left(ws1.Range("AE" & i), Len(ws2.Range("A" & j))) Then
          ws1.Range("BM" & i) = "Yes"
          flg = True
          Exit For
          End If
    Next k
    If flg = True Then Exit For
  Next j
Next i


Set ws1 = Nothing: Set ws2 = Nothing

Leaving this here for whom it might be useful to.

Thanks for your help anyway.

Regards.

 

[Col Delane]

Glad to here that you've got a solution.

Out of curiosity, why are you using VBA to achieve this (which is using "hard coded" column references, so the code will require editing if columns are inserted to the left) rather than a formula (or two) that can just sit in your source data table and produce the necessary flag when the workbook is recalculated (and if your source data is encompassed within an Excel Table, will automatically replicate for any added data rows)?

 

[TiagoRocha]

Glad to here that you've got a solution.

Out of curiosity, why are you using VBA to achieve this (which is using "hard coded" column references, so the code will require editing if columns are inserted to the left) rather than a formula (or two) that can just sit in your source data table and produce the necessary flag when the workbook is recalculated (and if your source data is encompassed within an Excel Table, will automatically replicate for any added data rows)?

Hi Col,

As the analysis this pivot will serve can be performed by several people within my Business Analysts team, I built an Excel Ad-In with the relevant code for the pivot and other parts of the analysis. Plus, this code will run on top of different reports that are exported from a company tool.

Two main reasons for me to choose the Add-In over the workbook:

- I has never worked with add-ins before and I wanted to try it
- The mobility the add-in provides over the workbook (many people can use it, it's lighter, you can access it from different workbooks, etc)

If it was only me performing this analysis, I would definitely consider the workbook option differently.

Any way, thank you for your time and interest.

Kind regards.