Find Records,

[josros60]

Hi,


i have this huge spreasheet (attached) that i want to search for records and put the results temporary an new sheet but i am not good in vba, can any body help.

CARRIER ACCOUNT NUMBER ACCOUNT NAME BTN WTN AUTH_CODE AUTH.CODE.STATUS BCTEL 000071411 CENTURY 21 MOVING REAL ESTATE BC LTD 2503784344 0000000000 2503784344 Active BCTEL 000000021 CN (IT TELECOM) 2504597918 0000000000 2504597918 Active BCTEL 000000021 CN (IT TELECOM) 2504597928 0000000000 2504597928 Active BCTEL 000110709 GLOBAL PARTNERS INSTITUTE 6044484250 0000000000 6044484250 Active BCTEL 000076963 GEORGE RICHARD LOW 2505773758 0000000000 2505773758 Active BCTEL 000110024 ROYAL LEPAGE LOCATIONS WEST REALTY 2504937587 0000000000 2504937587 Active BCTEL 000151077 VILLAGE OF PORT ALICE 2502843392 0000000000 2502843392 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507701317 0000000000 2507701317 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708601 0000000000 2507708601 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708602 0000000000 2507708602 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507709157 0000000000 2507709157 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507701317 0000000000 2507701317 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708601 0000000000 2507708601 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708602 0000000000 2507708602 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507709157 0000000000 2507709157 Active BCTEL 000074693 HOMELIFE GLENAYRE REALTY CO LTD 6044627359 0000000000 6044627359 Active BCTEL 000163771 IKTEROS ENTERPRISES INC 6044488790 0000000000 6044488790 Active TELUS 000164717 BANCORP FINANCIAL SERVICES INC 6046082717 0000000000 6046082717 Active TELUS 000158736 ESKIMO STEEL 4034680910 0000000000 4034680910 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170542 0000000000 4034170542 Active TELUS 000158736 ESKIMO STEEL 4034680909 0000000000 4034680909 Active TELUS 000158736 ESKIMO STEEL 4034681736 0000000000 4034681736 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170513 0000000000 4034170513 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170514 0000000000 4034170514 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170515 0000000000 4034170515 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170516 0000000000 4034170516 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170517 0000000000 4034170517 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170564 0000000000 4034170564 Active TELUS 000158736 ESKIMO STEEL 4034639704 0000000000 4034639704 Active BCTEL 000171397 STERLING GROUP CORP 6048501299 0000000000 6048501299 Active BCTEL 000061435 CONSULATE GENERAL OF JAPAN 6046855895 0000000000 6046855895 Active BCTEL 000095108 TIDE RIP TOURS LTD. 2509283092 0000000000 2509283092 Active <tbody> </tbody>

<tbody>
</tbody>

thank you

 

[mumps]

What criteria do you want to use to search for the records? For example, do you want to search by Account Number, Account Name, etc.? If there is more than one occurrence of the search criteria, do you want to copy all the occurrences or just the first one?

 

[josros60]

thank you for responding.

i want to search by AUTH_CODE.

thanks again

 

[codeliftsleep]

Copy the range to a variant array and then filter by Auth_Code...

Actually that won't work right, scratch that.

How many rows is huge, btw? This might be a perfect spot to use a binary search algorithm on a variant array.

Are the auth_codes all different?

Are you looking for multiple ones at a time?

Could also use autofilter and pass in an array. Need to know more about how exactly you want this to work tho. I have passed in an array of 623 values on a 203,000 row record set and it filtered it pretty quickly...

 

[mumps]

This macro will prompt you to enter a AUTH CODE. If found (assuming it is in column D and that there are no duplicate codes), that row will be copied to Sheet2.

Sub AuthCODE()
    Application.ScreenUpdating = False
    Dim foundAC As Range
    Dim response As String
    response = InputBox("Please enter the AUH CODE.")
    Set foundAC = Range("D:D").Find(response, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundAC Is Nothing Then
        foundAC.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Else
        MsgBox (response & " not found.")
    End If
    Application.ScreenUpdating = True
End Sub

If there can be duplicate codes, let me know and I will modify the macro to account for that.

 

[josros60]

thank you.


actually the auth_codes is will be link to this TSCI_CODE


whihc means that for example in the list 2503484444 can have two or more TSCI_CODE codes, like 2004, 2002 etc.


what i want is when search put the resuls auth_coes with the differetns TSCI_CODE.




here the table i missed the first column previously.

TSCI_CODE	DIRECTION	CARRIER	ACCOUNT NUMBER	ACCOUNT NAME	BTN	WTN	AUTH_CODE	AUTH.CODE.STATUS	ACCT STATUS
2104	2	BCTEL	000071411	CENTURY 21 MOVING REAL ESTATE BC LTD	2503784344	0000000000	2503784344	Active	A
2104	2	BCTEL	000000021	CN (IT TELECOM)	2504597918	0000000000	2504597918	Active	A
2104	2	BCTEL	000000021	CN (IT TELECOM)	2504597928	0000000000	2504597928	Active	A
2104	2	BCTEL	000110709	GLOBAL PARTNERS INSTITUTE	6044484250	0000000000	6044484250	Active	A
2104	2	BCTEL	000076963	GEORGE RICHARD LOW	2505773758	0000000000	2505773758	Active	A
2104	2	BCTEL	000110024	ROYAL LEPAGE LOCATIONS WEST REALTY	2504937587	0000000000	2504937587	Active	A
2104	2	BCTEL	000151077	VILLAGE OF PORT ALICE	2502843392	0000000000	2502843392	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507701317	0000000000	2507701317	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507708601	0000000000	2507708601	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507708602	0000000000	2507708602	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507709157	0000000000	2507709157	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507701317	0000000000	2507701317	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507708601	0000000000	2507708601	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507708602	0000000000	2507708602	Active	A
2104	2	BCTEL	000105151	ABC ALLEN BUSINESS COMMUNICATIONS	2507709157	0000000000	2507709157	Active	A
2104	2	BCTEL	000074693	HOMELIFE GLENAYRE REALTY CO LTD	6044627359	0000000000	6044627359	Active	A

<colgroup><col span="3"><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[mumps]

I'm not sure what you mean when you say

the auth_codes is will be link to this TSCI_CODE

Please clarify. What do you mean by "link"?
Also you say

2503484444 can have two or more TSCI_CODE codes, like 2004, 2002

I don't see these values on the data you posted so it's difficult to see the relationship between the AUTH_CODE and the TSCI_CODE codes.

 

[codeliftsleep]

How are you determining which auth_codes to search? So you have them listed on a separate sheet? Enter them in somewhere?

This seems like a perfect case to just autofilter by an array and then copy the autofilter range but I need to know more to set it up properly.

 

[josros60]

hi,

the code sample vba posted above works but only give me one record and what i want is that when i enter the auth_codes 2503214488 to give all the records related to that number because that number can be repeated with different TSCI_CODE codes hence the result will be same auth_codes with different tsci_codes. i know the list i posted have the same tsci_code 2104 but the complete list have lots of different ones.

thank you

 

[codeliftsleep]

hi,

the code sample vba posted above works but only give me one record and what i want is that when i enter the auth_codes 2503214488 to give all the records related to that number because that number can be repeated with different TSCI_CODE codes hence the result will be same auth_codes with different tsci_codes. i know the list i posted have the same tsci_code 2104 but the complete list have lots of different ones.

thank you

Where are these records coming from? Ill build you an autofilter function but I need to know how you are getting which auth code to look up