Sort Column C for all similar values in Column A

**R_S_6** · 01-12-2009, 06:04 AM

Hi All,

I would like a macro to sort values in column C in ascending order for all rows in which Column A values are identical.

I have a spreadsheet that once organised will have several Subcases (1000 to 1010 for example) listed in column A. These Subcases may have as many as 100 repetitions of each, however there will be a space of 2 clear rows between each Subcase. The macro should sort the Column C values in ascending order for all like Subcases.

The searching of Column A for similar fields in which to sort according to Column C values should start at Row 15.

From the example spreadsheet uploaded, the first 'Sort' function would be for all 'Subcase 1000' Column A Values, rearranging these rows for the ascending order of Column C Values.

Thanks for all your help thus far!!!

Cheers

**MickG** · 01-12-2009, 08:32 AM

Hi, Try this:-
Nb:- The results are in column "D" at the moment, to relocate them To Column"C", Alter the line of code "Set oRng = Dn.Offset(, 3)" To "Set oRng = Dn.Offset(, 2)"

Please Login or Register  to view this content.

Regards Mick

**R_S_6** · 01-12-2009, 10:16 AM

Mick,

I can't thank you enough! I changed the (, 3) to (, 2) and it worked first time!

Thanks Again - Really Appreciate it!

Cheers

**R_S_6** · 01-13-2009, 01:22 PM

Mick, How can I modify the awesome code above such that it will sort the data from the width of columns A through G inclusive. At current it stops sorting the data after column C.

I realise its my own fault for forgetting to define this earlier.

Cheers

**MickG** · 01-13-2009, 01:57 PM

Hi, I'll have a look at it and get back to you tomorrow.
Regards Mick

**R_S_6** · 01-14-2009, 04:35 AM

Thanks Mick - Appreciated!

**MickG** · 01-14-2009, 06:42 AM

Hi, Try this.
Code sorts Data from row 15 on Based on ranges specified by "SubCases" in column "A".
Columns sorted from Column "C" to last column with Data, based on row 15.

Please Login or Register  to view this content.

Regards Mick

**R_S_6** · 01-14-2009, 06:59 AM

Just tried that Mick,

Hits an error.

'Run-time error '13':

Type mismatch'

If you click debug it highlights the issue on line:

Please Login or Register  to view this content.

??? Any ideas. Thanks for your help - supremely appreciated!

Cheers

**MickG** · 01-14-2009, 07:39 AM

Hi, Have you got any Text in the sorting Range, That will cause it.
The number of rows in the sorting range is based on the Column "A", row 15 to last row with data.
If you have any Text Data in the range after the sorting range and also data in column "A" after the sorting range, this willl also cause it.
Try Changing the line:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Regards Mick

**R_S_6** · 01-14-2009, 08:08 AM

Still no luck.

I have attached an example excel sheet (Example_3.xls) with your new code and earlier mentioned modification.

It is still coming up with the same error, although does seem to sort the component ID field OK. I just need it to sort the 'Name', 'Node' and 'Value' columns (Columns D, E & F respectively) such that their values relate to their original ID's from Column C. However all sorting of data must remain relevant to its own Subcase (Column A).

Hope it makes more sense with the attached xls.

Cheers Again!

**MickG** · 01-14-2009, 10:09 AM

Hi, The code, in the attached sorts Column "C" and then sorts Column "D, E & "F in relation to the column "C" sort. I hope that's right.
I have added a sheet to your File for Testing.
There is now a New code in a Command Button in Both sheets and also a Module "Sort".
Sheet 1 shows the sorted result.
Regards Mick

**MickG** · 01-15-2009, 07:30 AM

Hi, After downloading your file I seem to now have a copy of addin :- funcres(FUNCRES.XLA) do you recognise this addin as coming from you.
I can't open it because I don't have the Password.
Can you throw any light on this ??
Regards Mick

**R_S_6** · 01-15-2009, 07:39 AM

Just running a search on my machine for that file name. Don't think it is from me - If i'm honest don't even know what defines an 'Addin', plus wouldn't know how to password it if I did.

Either way I'll let you know the search of the result ASAP.

Cheers.

**R_S_6** · 01-15-2009, 07:44 AM

Hi Mick,

I found a copy of that file on my machine. Last modified 2005 some time. Certainly not a file I had a hand in - Did a quick Google search.

Hope that helps???

Haven't had chance to update the macro into my spreadsheet - been snowed under. Hopefully i'll have chance to review it Monday morning and get back to you.

Cheers Mick - Your help is really appreciated!