Manual sort macro

[smpita]

Thank you for your attention to my thread.

I have spreadsheet that I have to sort the rows in. I have already recorded an autosort to put the spreadsheet into alphabetical format but I need a slight modification of the sort. The spreadsheet is not very long so it shouldn't be much issue to sort it twice unless you know of a better way.

Here is a sample that shows what a typical spreadsheet might look like.
spreadsheet.png

What I need is a way to move the MN and URS rows to the top of the list. In this case, it would end up in an order similar to this:

MN 12345
MN 123456
URS 23456
URS 234567
ARB 123A
WRD 1234

It does not matter which of MN or URS is at the top, but MN needs to stay grouped together as does URS. I already have the following code cycling through the rows.

Please Login or Register  to view this content.

Thank you so much!

[stanleydgromjr]

smpita,

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[protonLeah]

Please Login or Register  to view this content.

[smpita]

Attached (rawsortexample.csv) is a spreadsheet that needs to be custom sorted by my VBA macro. Post macro, it should look like sortexampledone.csv

In column B, you see several different values in the format "Community" (URS, MN, ARB, etc) then an "Account number" (1234a, etc) then a date. I need to sort it so that the URS and MN communities are on top and everything else is on the bottom. My example shows ARB, CM and WRD communities but we are often adding new communities so I can't use the CustomOrder: method suggested by protonLeah above as we would need to be constantly updating the macro to include new communities. I was thinking something to the effect of pseudocode:

Please Login or Register  to view this content.

The communities will already be sorted by the following code and be in alphabetical order. I just need to move the URS and MN accounts to the top of the pile somehow.

Please Login or Register  to view this content.

Here is the part of my macro that deals with this report in full.

Please Login or Register  to view this content.

[shg]

You could use a two-column lookup table with communities on the left column and sort order on the right, and then use a lookup formula in a convenient column. Sort on that.

[smpita]

shg - I'm not sure I know what you meant but this is what I came up with from your suggestion. It seems to work, but I'm not sure if it's the most efficient code. Thank you!

Please Login or Register  to view this content.

[shg]

Using code for this lacks flexibility and is overkill. I had in mind just a table and a formula:

	A	B	C	D	E	F	G	H
1	Comm	Acct	Date	Sort			Comm	Order
2	MN			2	D2: =IFERROR(VLOOKUP(A2, $G$2:$H$4, 2, 0), 99)		URS	1
3	URS			1			MN	2
4	XXX			99			ARB	3
5	MN			2
6	XXX			99
7	XXX			99
8	URS			1
9	XXX			99
10	MN			2
11	ARB			3
12	MN			2