• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

search data with multiple criteria

W

weiwei1967

New Member
I have a huge data which need to input the "Last" customer, based on the following criteria:　

1. same GROUP　

2. same PART　

3. DATE - Oldest to Newest　

At "Result" column : if GROUP & PART same, 　

- "Result" blank for Oldest DATE. 　

- copy Customer with oldest date and paste at "Result" with 2nd oldest date.　

If only have single GROUP & PART, "Result" = Customer　

Anyone have similar VBA file which can share with me?　

Thanks in advance for all the helps and looking forward to heard the good news from any of you soon.
 

Attachments

  • sample file.xlsx
    9.5 KB · Views: 3
Hi,
Thanks for the reply. The data currently I have is more than 10k. And it will be kept increasing.
Thanks in advance for the helps.
 
does your real data have like an index value or something? In my head, you could do this with probably a couple of recordset objects to at least identify the right values. I have no idea how I would approach getting it back into the list.
 
I just did this over a cup of coffee. It's junk code that I have that I use pretty frequently: I just customized it for this. Initial requirements would be having to rename your "group" to something else. I called it "rengroup". Group is an owned word in sql and even if you can trick your code into working, it's just generally a bad practice to avoid it. Also you have to name the range of data. I used "demo", because I'm lazy.

Anyway, first function gets a list of distinct values and goes through them passing the combinations to the second function which fetches an ordered list of dates

It just uses some basic navigation to return the second to last date in the list.




Code: 
Sub fetchdis()


'record set for excel self query
Dim sdbName As String
Dim sConnectString As String
Dim sSQL As String
Dim Connection As ADODB.Connection
Dim rsTarget As ADODB.Recordset

sdbName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
sConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sdbName & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

Set Connection = New ADODB.Connection
Connection.Open ConnectionString:=sConnectString

Set rsTarget = New ADODB.Recordset
sfilter = "B"
sSQL = "SELECT DISTINCT rengroup, part  FROM demo;"


rsTarget.Open sSQL, Connection, adOpenDynamic, adLockReadOnly


'do things

Do While Not rsTarget.EOF
  Call fedvals(rsTarget("renGroup"), rsTarget("Part"))
  rsTarget.MoveNext
Loop


'cleanup
rsTarget.Close
Set rsTarget = Nothing
Connection.Close
Set Connection = Nothing


End Sub


Sub fedvals(sGroup As String, sPart As String)


'record set for excel self query
Dim sdbName As String
Dim sConnectString As String
Dim sSQL As String
Dim Connection As ADODB.Connection
Dim rsTarget As ADODB.Recordset

sdbName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
sConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sdbName & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

Set Connection = New ADODB.Connection
Connection.Open ConnectionString:=sConnectString

Set rsTarget = New ADODB.Recordset

sSQL = "SELECT date FROM demo WHERE rengroup = '" & sGroup & "' and Part = '" & sPart & "' ORDER BY date;"




Debug.Print sSQL


rsTarget.Open sSQL, Connection, adOpenDynamic, adLockReadOnly


'do things
rsTarget.MoveLast
rsTarget.MovePrevious
Debug.Print rsTarget("Date")


'cleanup
rsTarget.Close
Set rsTarget = Nothing
Connection.Close
Set Connection = Nothing


End Sub


Also, just saying: You can do this sort of project in Excel - no doubt. But it might be safer and less tedious to just use access.
 
Last edited:
Hi,
Thank you so much for all the helps.　

I had tried out the code on my excel file. An Error message appear as I do not have Microsoft Eccess.
 
Hi ,

Can you confirm whether the data in your Excel workbook will be sorted on Group first , then Part , then Customer ?

Or should the code do a sort before it processes the data ?

Narayan
 
Hi ,

Thanks for the clarification / confirmation.

Can you please upload a workbook which has around 50 rows of data , with enough variety in the data , so that what ever code is written can be thoroughly tested ?

Narayan
 
Hi,
Column E is the result i wish to have after running the Code. 　

For same Pre-Group and same Part, the second oldest Date will have the Customer name with Oldest Date. For the Customer with Oldest Date, column E will be Blank.　
Hope to hear a good news from you soon.
Thanks!
 
Hi ,

The file you uploaded does not seem to be sorted ; can you indicate what the sort order is ?

Level 1 : ?

Level 2 : ?

Level 3 : ?

Level 4 : ?

Narayan
 
Hi ,

I am attaching your file with the SORT done the way you have mentioned.

Please indicate the result in column E.

Narayan
 

Attachments

  • Sample file.xlsm
    17.8 KB · Views: 12
Hi Narayan,
Here is the file with column E updated.
Thanks and hope to hear from you soon.
 

Attachments

  • Sample file.xlsm
    16.7 KB · Views: 9
You must log in or register to reply here.
Back
Top