MS Excel VBA Code to Sort a Column Based on Distance and then Group Values

[OilEconomist]

Thanks in advance for you assistance.
I have a set of data that I am trying to sort by four criteria in the following order:
(1) By distance
(2) Name
(3) Gender
(4) Yes or No

The issue is that some of the names are repeated so there is no way to group them together if one of the other columns like Distance is different. Take this small example, I would like to sort by Distance and name. Initially I have this:

Specialist Tool - (Active).xlsm
	A	B	C
1	NAME	NEW	DISTANCE
2	Ron Smith	Yes	2
3	Jack Frost	No	10
4	Ron Smith	Yes	20
Sheet2

Finally I would like this:

Specialist Tool - (Active).xlsm
	A	B	C
1	NAME	NEW	DISTANCE
2	Ron Smith	Yes	2
3	Ron Smith	Yes	20
4	Jack Frost	No	10
Sheet2

But there is no way to do this with sort because if you sort by distance, you could not group all the Ron Smith's together.

Any ideas? Please note the data size I am using is much larger (like 150+ rows)

 

[Larry Haydn]

Why not sort by name first, then distance, etc.

 

[OilEconomist]

Why not sort by name first, then distance, etc.

Because I need the distance first.

 

[Fluff]

You could use a helper column & sort on that 1st

Fluff.xlsm
	A	B	C	D
1	NAME	NEW	DISTANCE	help
2	Ron Smith	Yes	2	2
3	Jack Frost	No	10	10
4	Ron Smith	Yes	20	2
Data

Cell Formulas
Range	Formula
D2:D4	D2	=MINIFS(C:C,A:A,A2)

 

[OilEconomist]

You could use a helper column & sort on that 1st

Fluff.xlsm
	A	B	C	D
1	NAME	NEW	DISTANCE	help
2	Ron Smith	Yes	2	2
3	Jack Frost	No	10	10
4	Ron Smith	Yes	20	2
Data

Cell Formulas
Range	Formula
D2:D4	D2	=MINIFS(C:C,A:A,A2)

Thanks. That's a possibility. I was thinking of:
(1) Sorting by distance
(2) Then take the first value, in this case "Ron Smith", storing it in a string like "SortIt"
(3) whenever a line equaled "SortIt", moving it to the the top section after the previous SortIt.

This could run really slow and your idea might be the way to go.

I'm surprised nothing like this has been done previously or I just can't find it.

 

[Fluff]

It's certainly come up before.

 

[OilEconomist]

It's certainly come up before.

If you have a link, can you post so I can see what they did.

 

[Fluff]

It's basically just what I suggested.

 

[OilEconomist]

It's basically just what I suggested.

Okay thanks!

 

[Fluff]

You're welcome.