Sorting by clients names

[Boris7265]

Hello everybody,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I am really appreciate help and guide how to solve this problem .The problem that I am facing ( I would name it is a challenge) this –I have a very large spreadsheet about 15000-20000 rows in it and we upload this spreadsheet into Oracle ( accounting soft ware) to printing checks out every week ( 7000-8000 checks to be printed every week) .The problem that there a lot of requirements from legal department ( I am working in big law firm) that they want to us to sort the checks by clients ( big American banks ) , than dates , and last names of debtor. I m doing all sorting in Excel worksheet ,but it is so time consuming and prone to mistake .I am a little bit new n vb specially in excel part , but I have read a lot of threads and now I am working on macro that asks user what client to be sort and once user enter it in a input box the client name the macro will sort by the specific clients name , date and last name .Clients are in the column Q, date in column M and last names are in column N .The sorting should be done until the client from input box match the value in column Q and only those rows should be sorted .Once the sorting done the msg box will ask if there is another client to be sorted if yes “Please enter the bank in the input box if not close the macro” .
<o> </o>
I am working on this but so far I am stuck.
<o> </o>
Really appreciate for any help .It helps me a lot on my job.
<o> </o>
Thank very much in advance.
<o> </o>
The Best Regards,
<o> </o>
BorisGomel

 

[Desu Nota from Columbus]

Have you tried using the Auto-Filter tool under the data menu?

Creates a drop-down menu out of the column headers so you can pull up whatever selection you want.

 

[Boris7265]

Thnak you very much Desu Nota,

I did fugure it out as you said in your post. It works nice for me .Just a little qestion for you if you are so kind would be to help me .Sorting is fine , but I need to sort as well banks by last name that in column M only those rows that have Cost id #2 which are in the colum Q. Everything is bein sorted fine by using Auto Filter , howere I would like to have macro to sort column M that is the last name of debtor only those rowa which have id Cost #2 that are in cloumn Q .The below is code that I ma working on it .
Set SrchRng1 = ActiveSheet.Range("Q1", ActiveSheet.Range("Q65536").End(xlUp))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim c1 As Range<o></o>
Do<o></o>
Set c1 = SrchRng1.Find("2", LookIn:=xlValues)<o></o>
If Not c1 Is Nothing Then c1.Sort Key1:=Range("M4"), Order1:=xlAscending, Header:=xlGuess, _<o></o>
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<o></o>
DataOption1:=xlSortNormal<o></o>
Loop While Not c1 Is Nothing<o></o>

The code doesn't work.

Any help will be greatly appreciated.

Thank you ,

The Best Regards,

BorisGomel

 

[Desu Nota from Columbus]

I'm having some trouble understanding what you are saying, so correct me where I may be misinterpreting:

you want to filter your data using Column Q with the criteria = 2?

What exactly are you doing with this data once you have it sorted?

I do not have a comprehensive knowledge of VBA, so I am really only helpful in using the methods I have previously mastered. I don't have a crystal clear understanding of what you are trying to do, but I will try to explain how I would go about what you are doing (i think ):

`I would use the auto-filter capabilities
Cells.AutoFilter
    Selection.AutoFilter Field:=17, Criteria1:="2" `filters your sheet by        column Q (17) for all values =2
    Selection.AutoFilter Field:=13, Criteria1:="Bank Name that you are searching for from input box, have search box input name here"

Add all the filters you need. You could use input boxes for all the criteria you wish to filter your data. You could add filters for any amount of criteria and have boxes inputting the desired value into the above code.

I don't know if this a route you would like to take, because I am not exactly sure how your data is setup and what you do with it after filtering.

It is possible to select visible cells once filtered

Cells.SpecialCells(xlCellTypeVisible).Copy

You could then paste them to another sheet and work with them exclusively.

Let me know if any of this is practical or makes sense.

 

[Boris7265]

Thank you so much Desu Nota ,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Auto Filters work for vey fine me .I did as you said only I create new macro and record whiel I was setting auto filters. I didn’t 't know that Excel has such feature.<o></o>
Thank you very much for all your efforts.<o></o>
<o></o>
The Best Regards, <o></o>
<o></o>
BorisGomel<o></o>
<o> </o>

 

[Desu Nota from Columbus]

Glad I could help.

Just a word of warning about the macro recorder: It is very good for seeing the syntax of commands, but it relies solely on select and selection which can bog down and crash excel (especially with the amount of data you have).

I would highly recommend stripping down the recorded macro as much as possible and make it concise to avoid excel becoming bogged down!