Advanced filtering based off of another file Vlookup

I

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
793
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello - I have posted before but I am not getting any hits. so going to keep my problem brief and generic. Hopefully someone could lead me to an answer or refer me to a way to get there.

Existing Code: Will open another worksheet (CSV) and covert it to a format I need to upload. The data within that file has unique characters in column A. In my worksheet where I execute the code from has those same unique values in Column B but also has them grouped by Column A. And what I am looking to do is list out all of the Column A's I want to see in that upload.

Example below: (i only want my upload to show 10 records)

Book2
ABCD
1TypeUnique TagFor Export
2AAABC1AA
3AABBB1CC
4AACCC1EE
5AADDD1
6BBACD1
7BBADD1
8BBAFF1
9BBAGG1
10CCLLL1
11CCLLL2
12CCLLL3
13CCYYY4
14DDUUU8
15DDPPP0
16DDMNM1
17EEAFD1
18EEDF21
Sheet2
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
For simplicity I put everything in one workbook
So you will need to modify the code slightly after testing to apply to multiple workbooks

To test the code I suggest that you do EXACTLY what I did (so that I can help you debug any issues)
- create a new workbook
- add the 2 sheets and name them the same as I have done
- insert the code
- run it

I created 2 sheets using your data
sheet name "CONVERTED"
Book1
ABCD
1Unique TagData1Data2Data3
2ABC105/07/2020523Ref01
3BBB106/07/2020802Ref02
4CCC107/07/2020864Ref03
5DDD108/07/2020872Ref04
6ACD109/07/2020527Ref05
7ADD110/07/2020516Ref06
8AFF111/07/2020491Ref07
9AGG112/07/2020205Ref08
10LLL113/07/2020696Ref09
11LLL214/07/2020971Ref10
12LLL315/07/2020414Ref11
13YYY416/07/2020145Ref12
14UUU817/07/2020747Ref13
15PPP018/07/2020593Ref14
16MNM119/07/2020637Ref15
17AFD120/07/2020994Ref16
18DF2121/07/2020269Ref17
19DF2222/07/2020529Ref18
20DF2323/07/2020816Ref19
21DF2424/07/2020440Ref20
22DF2525/07/2020355Ref21
23DF2626/07/2020594Ref22
24DF2727/07/2020550Ref23
25DF2828/07/2020862Ref24
26DF2929/07/2020532Ref25
27DF3030/07/2020601Ref26
28DF3131/07/2020688Ref27
29DF3201/08/2020560Ref28
Converted


sheet name "EXECUTE"
Book1
ABCD
1TypeUnique TagFor Export
2AAABC1AA
3AABBB1CC
4AACCC1EE
5AADDD1
6BBACD1
7BBADD1
8BBAFF1
9BBAGG1
10CCLLL1
11CCLLL2
12CCLLL3
13CCYYY4
14DDUUU8
15DDPPP0
16DDMNM1
17EEAFD1
18EEDF21
Execute


I placed this code in the SAME workbook and ran it
VBA Code: 
Sub AdvFilter()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim expArr As Variant, tagArr As Variant
    Dim coll As New Collection, App As Application
    Dim a As Long, b As Long, c As Long
    Dim dataRng As Range
    Set ws1 = Sheets("Execute")
    Set ws2 = Sheets("Converted")
    Set dataRng = ws2.Range("A1").CurrentRegion
    Set App = Excel.Application
    App.ScreenUpdating = False: App.Calculation = xlCalculationManual
'create arrays of values
    expArr = ws1.Range("D2", ws1.Range("D" & ws1.Rows.Count).End(xlUp)).Value
    tagArr = ws1.Range("A2", ws1.Range("A" & ws1.Rows.Count).End(xlUp)).Resize(, 2).Value
    On Error Resume Next
'create filter list
    For a = 1 To UBound(tagArr)
        For b = 1 To UBound(expArr)
            If tagArr(a, 1) = expArr(b, 1) Then coll.Add CStr(tagArr(a, 2)), CStr(tagArr(a, 2))
        Next b
    Next a
    On Error GoTo 0
    c = coll.Count
'create temp sheet
    Set ws3 = ws2.Parent.Worksheets.Add
'add criteria
    ws3.Cells(1, 1) = ws2.Cells(1, 1)
    For a = 1 To c
        ws3.Cells(a + 1, 1) = coll(a)
    Next a
'add data and filter
    dataRng.Copy ws3.Cells(c + 3, 1)
    Set dataRng = ws3.Cells(c + 3, 1).CurrentRegion
    dataRng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws3.Range("A1").Resize(c + 1), Unique:=False
'copy visible cells to results sheet
    Set ws4 = Sheets.Add
    dataRng.SpecialCells(xlCellTypeVisible).Copy ws4.Cells(1, 1)
    App.DisplayAlerts = False
    ws3.Delete
    App.DisplayAlerts = True
    App.Calculation = xlCalculationAutomatic: App.ScreenUpdating = True
End Sub

Running code created this sheet

Book1
ABCD
1Unique TagData1Data2Data3
2ABC105/07/2020523Ref01
3BBB106/07/2020802Ref02
4CCC107/07/2020864Ref03
5DDD108/07/2020872Ref04
6LLL113/07/2020696Ref09
7LLL214/07/2020971Ref10
8LLL315/07/2020414Ref11
9YYY416/07/2020145Ref12
10AFD120/07/2020994Ref16
11DF2121/07/2020269Ref17
Sheet35
 
Upvote 0
Yongle said:
For simplicity I put everything in one workbook
So you will need to modify the code slightly after testing to apply to multiple workbooks

To test the code I suggest that you do EXACTLY what I did (so that I can help you debug any issues)
- create a new workbook
- add the 2 sheets and name them the same as I have done
- insert the code
- run it

I created 2 sheets using your data
sheet name "CONVERTED"
Book1
ABCD
1Unique TagData1Data2Data3
2ABC105/07/2020523Ref01
3BBB106/07/2020802Ref02
4CCC107/07/2020864Ref03
5DDD108/07/2020872Ref04
6ACD109/07/2020527Ref05
7ADD110/07/2020516Ref06
8AFF111/07/2020491Ref07
9AGG112/07/2020205Ref08
10LLL113/07/2020696Ref09
11LLL214/07/2020971Ref10
12LLL315/07/2020414Ref11
13YYY416/07/2020145Ref12
14UUU817/07/2020747Ref13
15PPP018/07/2020593Ref14
16MNM119/07/2020637Ref15
17AFD120/07/2020994Ref16
18DF2121/07/2020269Ref17
19DF2222/07/2020529Ref18
20DF2323/07/2020816Ref19
21DF2424/07/2020440Ref20
22DF2525/07/2020355Ref21
23DF2626/07/2020594Ref22
24DF2727/07/2020550Ref23
25DF2828/07/2020862Ref24
26DF2929/07/2020532Ref25
27DF3030/07/2020601Ref26
28DF3131/07/2020688Ref27
29DF3201/08/2020560Ref28
Converted


sheet name "EXECUTE"
Book1
ABCD
1TypeUnique TagFor Export
2AAABC1AA
3AABBB1CC
4AACCC1EE
5AADDD1
6BBACD1
7BBADD1
8BBAFF1
9BBAGG1
10CCLLL1
11CCLLL2
12CCLLL3
13CCYYY4
14DDUUU8
15DDPPP0
16DDMNM1
17EEAFD1
18EEDF21
Execute


I placed this code in the SAME workbook and ran it
VBA Code: 
Sub AdvFilter()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim expArr As Variant, tagArr As Variant
    Dim coll As New Collection, App As Application
    Dim a As Long, b As Long, c As Long
    Dim dataRng As Range
    Set ws1 = Sheets("Execute")
    Set ws2 = Sheets("Converted")
    Set dataRng = ws2.Range("A1").CurrentRegion
    Set App = Excel.Application
    App.ScreenUpdating = False: App.Calculation = xlCalculationManual
'create arrays of values
    expArr = ws1.Range("D2", ws1.Range("D" & ws1.Rows.Count).End(xlUp)).Value
    tagArr = ws1.Range("A2", ws1.Range("A" & ws1.Rows.Count).End(xlUp)).Resize(, 2).Value
    On Error Resume Next
'create filter list
    For a = 1 To UBound(tagArr)
        For b = 1 To UBound(expArr)
            If tagArr(a, 1) = expArr(b, 1) Then coll.Add CStr(tagArr(a, 2)), CStr(tagArr(a, 2))
        Next b
    Next a
    On Error GoTo 0
    c = coll.Count
'create temp sheet
    Set ws3 = ws2.Parent.Worksheets.Add
'add criteria
    ws3.Cells(1, 1) = ws2.Cells(1, 1)
    For a = 1 To c
        ws3.Cells(a + 1, 1) = coll(a)
    Next a
'add data and filter
    dataRng.Copy ws3.Cells(c + 3, 1)
    Set dataRng = ws3.Cells(c + 3, 1).CurrentRegion
    dataRng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws3.Range("A1").Resize(c + 1), Unique:=False
'copy visible cells to results sheet
    Set ws4 = Sheets.Add
    dataRng.SpecialCells(xlCellTypeVisible).Copy ws4.Cells(1, 1)
    App.DisplayAlerts = False
    ws3.Delete
    App.DisplayAlerts = True
    App.Calculation = xlCalculationAutomatic: App.ScreenUpdating = True
End Sub

Running code created this sheet

Book1
ABCD
1Unique TagData1Data2Data3
2ABC105/07/2020523Ref01
3BBB106/07/2020802Ref02
4CCC107/07/2020864Ref03
5DDD108/07/2020872Ref04
6LLL113/07/2020696Ref09
7LLL214/07/2020971Ref10
8LLL315/07/2020414Ref11
9YYY416/07/2020145Ref12
10AFD120/07/2020994Ref16
11DF2121/07/2020269Ref17
Sheet35
Click to expand...

If i put 1 criteria to filter by say AA or EE. I get something like this

Book2
ABCD
1Unique TagData1Data2Data3
2ABC15/7/2020523Ref01
3BBB19/7/2020527Ref05
4CCC1########516Ref06
5DDD120/07/2020994Ref16
6ACD1########491Ref07
7ADD1########205Ref08
8AFF16/7/2020802Ref02
9AGG17/7/2020864Ref03
10LLL18/7/2020872Ref04
11LLL221/07/2020269Ref17
12LLL322/07/2020529Ref18
13YYY423/07/2020816Ref19
14UUU824/07/2020440Ref20
15PPP025/07/2020355Ref21
16MNM126/07/2020594Ref22
17AFD127/07/2020550Ref23
18DF2128/07/2020862Ref24
Sheet17


when for EE should really only be

Book2
ABCD
1Unique TagData1Data2Data3
2AFD127/07/2020550Ref23
3DF2128/07/2020862Ref24
Sheet17
 
Upvote 0
I forgot - VBA does not allow array expArr to contain only one value :oops:
We can include a dummy value to keep VBA happy whilst not compromising the results

Amend one line
Rich (BB code): 
expArr = ws1.Range("D2", ws1.Range("D" & ws1.Rows.Count).End(xlUp)).Value
expArr = ws1.Range("D2", ws1.Range("D" & ws1.Rows.Count).End(xlUp).Offset(1)).Value
 
Upvote 0
Yongle said:
I forgot - VBA does not allow array expArr to contain only one value :oops:
We can include a dummy value to keep VBA happy whilst not compromising the results

Amend one line
Rich (BB code): 
expArr = ws1.Range("D2", ws1.Range("D" & ws1.Rows.Count).End(xlUp)).Value
expArr = ws1.Range("D2", ws1.Range("D" & ws1.Rows.Count).End(xlUp).Offset(1)).Value
Click to expand...
There we go! Ok so that works when the data is in 1 workbook.
 
Upvote 0
If you need further help, I need an explanation of how you want this to work
Essentially I need to understand where the code is to be placed, which workbooks, sheets and ranges are to be used for everything
It should simply be a case of pointing each variable at the correct target and the code will still work :unsure:

ws1
In post#1 you said that the code will reside in the worksheet code of the sheet containing columns A (= Type), and column B (= tag)
Q1 is that is what you meant (ie worksheet not workbook) ?
- there is no problem, I just need to know

Q2 Is that sheet laid out EXACTLY as in your example ?
- if not please detail
Q3 Does column D (of that same sheet) contain the for export filter values?
- if not, where do we tell VBA to find those values?

ws2
In post#1 you said that " Existing Code will open another worksheet (CSV) and convert it to a format I need to upload"
Q4 Does the conversion place headers in column1 starting in cell A1?
Q5 Do you want Existing Code to call macro AdvFilter after converting the csv?
- if not please detail
Q6 Where does Existing Code reside?
ws4
Where should results sheet be placed?
 
Last edited:
Upvote 0
Yongle said:
If you need further help, I need an explanation of how you want this to work

ws1
In post#1 you said that the code will reside in the worksheet code of the sheet containing columns A (= Type), and column B (= tag)
Q1 is that is what you meant (ie worksheet not workbook) ?
- there is no problem, I just need to know

Q2 Is that sheet laid out EXACTLY as in your example ?
- if not please detail
Q3 Does column D (of that same sheet) contain the for export filter values?
- if not, where do we tell VBA to find those values?

ws2
In post#1 you said that " Existing Code will open another worksheet (CSV) and convert it to a format I need to upload"
Q4 Does the conversion place headers in column1 starting in cell A1?
Q5 Do you want Existing Code to call macro AdvFilter after converting the csv?
- if not please detail
Q6 Where does Existing Code reside?
ws4
Where should results sheet be placed?
Click to expand...
Hi Yongle - all great questions let me elaborate and expand.

Q1 - Correct, the macro (code) button will be on the sheet and workbook where it has column A & B
Q2 - No, it has other fields and Column A and B end up being in different locations. But only need this sheet to define the relationship between in our example A & B
Q3 - Yes, it will reside in same sheet and workbook as Q1
Q4 - Yes, the upload has all data in Column A combined into 1 large string - the code will then covert delimited characters so each field is in its own respective column.
Q5 - Yes!
Q6 - existing code resides in same workbook at Q1 and Q3. the upload file is just a CSV file.

Ultimate goal. the upload file is in a bad format (auto generated). Code coverts the file into a fashion it should be in and this code i am looking for is to filter the end result to just show data in that upload based off of that range. Would it be of benefit if I provided that code? I just may need to censor a few sensitive items in it but more than welcome to.
 
Upvote 0
ItalianPlatinum said:
Ultimate goal. the upload file is in a bad format (auto generated). Code coverts the file into a fashion it should be in and this code i am looking for is to filter the end result to just show data in that upload based off of that range.
Click to expand...
Q1 Does the existing code ensure that the converted data is contiguous? (= no empty row or column)
- filtering does not work
Q2 Are you doing anything MANUALLY to FIX the data AFTER your existing code runs?

ItalianPlatinum said:
Would it be of benefit if I provided that code? I just may need to censor a few sensitive items in it but more than welcome to.
Click to expand...
No - I do not need the code at the moment (maybe later, hopefully not)
Q3 Is a variable used to refer to the worksheet or is it the ActiveSheet?

Sorry I did not number this question which is why you missed it
Q4 Where should results sheet be placed?

ItalianPlatinum said:
No, it has other fields and Column A and B end up being in different locations. But only need this sheet to define the relationship between in our example A & B
Click to expand...
Currently the code relies on the data being in Column A, Column B and Column D (FIXED) - but you say the data may end up in different columns
We could use VBA to determine the correct columns if the headers are consistent
Q5 What are the EXACT titles for the 3 columns in the converted data
 
Upvote 0
Yongle said:
Q1 Does the existing code ensure that the converted data is contiguous? (= no empty row or column)
- filtering does not work
Q2 Are you doing anything MANUALLY to FIX the data AFTER your existing code runs?


No - I do not need the code at the moment (maybe later, hopefully not)
Q3 Is a variable used to refer to the worksheet or is it the ActiveSheet?

Sorry I did not number this question which is why you missed it
Q4 Where should results sheet be placed?


Currently the code relies on the data being in Column A, Column B and Column D (FIXED) - but you say the data may end up in different columns
We could use VBA to determine the correct columns if the headers are consistent
Q5 What are the EXACT titles for the 3 columns in the converted data
Click to expand...
Am so sorry I left off a BIG piece and could simplify this a lot. My code actually will copy the upload file into the workbook that has the code into it and place it in its own sheet then does the formatting. When done it exports it out to its own workbook. I totally forgot that

Q1 - it does not ensure but the data should not have empty columns/rows part of the formatting does that removes empty rows
Q2 - No nothing manual after the upload is modified
Q3 - Yes it has a variable where it finds the partial format and date meets today in order to open the upload if not met it just kicks out a dialog box.
Q4 - In its own workbook with a static naming i can define when everything is all said and done
Q5 - so the column A and B ends up being static just a different location see below:
Column A (Title= Account) starts on A13
Column B (Title = Symbol) starts on E13
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Formatting csv file
Replies
6
Views
560
picklefactory
P
S
  • Question
CSV export to remove column
Replies
1
Views
144
StellarRumble59
S
C
  • Question
Confused over pivot table calculations :-(
Replies
1
Views
293
number kruncher
N
Yecart77
  • Question
Formula working on first row but not subsequent rows
Replies
1
Views
121
awoohaw
awoohaw
G
  • Solved
My VLOOKUP returns a #N/A!
Replies
1
Views
78
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,492
Messages
6,125,115
Members
449,206
Latest member
burgsrus

Share this page

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