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



## OilEconomist (Jun 4, 2022)

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).xlsmABC1NAMENEWDISTANCE2Ron SmithYes23Jack FrostNo104Ron SmithYes20Sheet2

Finally I would like this:
Specialist Tool - (Active).xlsmABC1NAMENEWDISTANCE2Ron SmithYes23Ron SmithYes204Jack FrostNo10Sheet2

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 (Jun 4, 2022)

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


----------



## OilEconomist (Jun 8, 2022)

Larry Haydn said:


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


Because I need the distance first.


----------



## Fluff (Jun 9, 2022)

You could use a helper column & sort on that 1st
Fluff.xlsmABCD1NAMENEWDISTANCEhelp2Ron SmithYes223Jack FrostNo10104Ron SmithYes202DataCell FormulasRangeFormulaD2:D4D2=MINIFS(C:C,A:A,A2)


----------



## OilEconomist (Jun 10, 2022)

Fluff said:


> You could use a helper column & sort on that 1st
> Fluff.xlsmABCD1NAMENEWDISTANCEhelp2Ron SmithYes223Jack FrostNo10104Ron SmithYes202DataCell FormulasRangeFormulaD2:D4D2=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 (Jun 10, 2022)

It's certainly come up before.


----------



## OilEconomist (Jun 10, 2022)

Fluff said:


> It's certainly come up before.


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


----------



## Fluff (Jun 10, 2022)

It's basically just what I suggested.


----------



## OilEconomist (Jun 10, 2022)

Fluff said:


> It's basically just what I suggested.


Okay thanks!


----------



## Fluff (Jun 10, 2022)

You're welcome.


----------



## OilEconomist (Jun 4, 2022)

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).xlsmABC1NAMENEWDISTANCE2Ron SmithYes23Jack FrostNo104Ron SmithYes20Sheet2

Finally I would like this:
Specialist Tool - (Active).xlsmABC1NAMENEWDISTANCE2Ron SmithYes23Ron SmithYes204Jack FrostNo10Sheet2

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)


----------



## OilEconomist (Dec 20, 2022)

Fluff said:


> You could use a helper column & sort on that 1st
> Fluff.xlsmABCD1NAMENEWDISTANCEhelp2Ron SmithYes223Jack FrostNo10104Ron SmithYes202DataCell FormulasRangeFormulaD2:D4D2=MINIFS(C:C,A:A,A2)


Thanks for this @Fluff. This works. Apologies I just marked it but I thought I already had done so.
Now in regards to setting up the formula for the help column. Is there way to set ranges for each column?

Thanks!


----------



## Fluff (Dec 21, 2022)

OilEconomist said:


> Is there way to set ranges for each column?


In what way?


----------

