lookup multiple columns and extract data from another file

[Rodrigues]

All
I have two files (attached) one master2 and other to extract data from.
Extract data from file Code_Prod_Report.csv columns A,C and D
column A cut from right 6 digits ; Column C look only for "OUT" ; Column D looking only fpr "A"
Examples above: Column A6=2 & B6=Green ; A7=3 & B7=RED ; A8=9 & B8=Blue ; A9=10 & B9=Ambar
Please note: have to rename Code_Prod_Report7.csv to .xlsx to be able to upload the file.
Thanks
R

 

[p45cal]

In the attached is a table at cell E5 (column E has been hidden), column F is calculated from Column E.
The table is actually a query which SQLs the csv file.
There's also a button at cell F2 which calls a macro that changes the query, only the query needs adjustment by you before trying to execute it.

Sub Macro3()
With ThisWorkbook.Connections("Query from Chandoo25986").ODBCConnection
  .BackgroundQuery = True
  .CommandText = "SELECT code, Colour FROM Code_Prod_Report7.csv WHERE (Direction='" & UCase(Range("B2").Value) & "') AND (Group='" & UCase(Range("B3").Value) & "')"
  .CommandType = xlCmdSql
  .Connection = "ODBC;DefaultDir=C:\USERS\PUBLIC\DOCUMENTS\CHANDOO25986;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = ""
  .SourceDataFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
  .AlwaysUseConnectionFile = False
  .Refresh
End With
End Sub

There's a line:
.CommandText = "SELECT code, Colour FROM Code_Prod_Report7.csv WHERE (Direction='" & UCase(Range("B2").Value) & "') AND (Group='" & UCase(Range("B3").Value) & "')"

where you have to change the red to the name of your file (later this can be automated).

There's another line:
.Connection = "ODBC;DefaultDir=C:\USERS\PUBLIC\DOCUMENTS\CHANDOO25986;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

where the red is the folder where I put your csv file on my machine, you need to change this to refer to your folder (again, this can be automated later).

Then, fingers crossed and the necessary drivers are installed on your system, you can try clicking the button. Try changing B2 and B3 and clicking the button again (the button too can be eliminated, the table updating itself as soon as those two cells change). Does it work? If not there are probably other ways to do the same thing (an ADO connection perhaps).

ps. I had to re-create your csv file, I hope it was something like:

code,Transaction Date,Direction,Group,Colour
2282001,,OUT,A,Green
2283001,,IN,A,Yellow
3282002,,OUT,A,Red
9283001,,OUT,A,Blue
9283001,,IN,A,Brown
10283001,,OUT,A,Ambar
11283001,,IN,A,White

 

[Rodrigues]

Hi there
makes sense, I will try it.

Not abusing of your goodwill, do you know how the formula to copy Columns A and B from one excel file to another? Similar to above.
Master1 needs to import columns A & B from Machine Prod Log file.
Thanks.R