Sort Column Data

[Branagorn]

I have 3 columns of data, within each column each data piece will be unique, but will be duplicated across 1 or both of the other columns. for notes a single cell will contain a value that looks like this "511VSB643770000201"

But for illustration lets pretend that each cell contains a persons name.


Column A contains the names of people (note that the nature of the data, there are no duplicate names in a single list) in a school in January.

Column B contains All the names of people that have ever been in the school between January and August (the master list).

Column C contains the name of people in the school in August.


I wish to sort columns A and C against B, keeping B's original ordering. (I cannot see a way to add more text after the table I have added, so I'll post how it should look in a reply to this thread)

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]James[/TD]
[TD]James[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Luke[/TD]
[TD]Luke[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Jacob[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]Hillary[/TD]
[TD]Ben[/TD]
[TD]Justin[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Zack[/TD]
[TD]Clyde[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jack[/TD]
[TD]Zack[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hillary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Justin[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Clyde[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Branagorn]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]James[/TD]
[TD]James[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Luke[/TD]
[TD]Luke[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jacob[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Ben[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Zack[/TD]
[TD]Zack[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Jack[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hillary[/TD]
[TD]Hillary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Justin[/TD]
[TD]Justin[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Clyde[/TD]
[TD]Clyde[/TD]
[/TR]
</tbody>[/TABLE]

 

[NARAYANK991]

Hi ,

Rather than thinking of a sort , can you not use two additional columns to have formulae in them , which will align all the names in the A and C columns with the names in column B ?

Once this is done , you can just copy paste those columns into column A and column C.

For example , to process the names in column A , you could have the following formula in column D :

=IF(ISNUMBER(MATCH(B2,$A$2:$A$11, 0)), B2, "")

Similarly , to process the names in column C , you could have the following formula in column E :

=IF(ISNUMBER(MATCH(B2,$C$2:$C$11, 0)), B2, "")

 

[MickG]

Try this:-
NB:- Results will overwrite data in column "A & C"

[COLOR="Navy"]Sub[/COLOR] MG02Nov43
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn: [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] Ac = -1 To 1 [COLOR="Navy"]Step[/COLOR] 2
  Ray = Rng.Offset(, Ac)
  Rng.Offset(, Ac).ClearContents
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray)
        [COLOR="Navy"]If[/COLOR] .exists(Ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
             .Item(Ray(n, 1)).Offset(, Ac) = Ray(n, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick