Thread: Sorting data

I have 9 columns of data in C2-C10 that I can sort numerically by the values in C2. All the numeric values in C2 are also in C1 but C1 has additional numbers not in C2.
I want to sort C2-C10 such that each row in C2-C10 aligns with its respective value in C1.

e.g. If C1 contains 1,2,5,8,34,42,56 and C2 contains only 2,5,34,56 I want the row (of C2-C10) with C2=2 to align with the cell where C1=2. And where C2=34 I want the row (C2-C10) with C2=34 to align with the cell where C1=34.
Is this possible though i'm not sure my explanation of what I want to do is good enough!

Note that my dataset is going to be very large (500,000 rows).

Thanks

Your description of your problem is a tad confusing.

Does this do what you want?

Sub lineUp()
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 1) <> Cells(i, 2) Then
    Cells(i, 2).Insert shift:=xlDown
End If
Next
End Sub

If this is not the solution you are looking for, please post a sample of the data as well as the desired outcome. Please post the sample saved as a version prior to 2007. (for those of us who do ot have 2007)

Hi,
Many thanks for your suggestion. I have no experience of using such code but I loaded it into the Visual Basic editor in Developer and ran it but it did not do what I wanted. This could be because my description was woeful (probably!), or because my file differed slightly from what I described - I was writing this description before completing the file and naively assumed that i would be able to easily edit whatever solution was suggested.

To avoid any confusion I have uploaded a file (example.xls) to show exactly what I have and what I would like to be able to do. Note that I previously was just describing a subset of my data and thought that I would be able to edit your suggestion to work on the additional data but now, given the opportunity, have a complete file.

I have 9 sets of samples. The first set is in C-M, the second in O-Y etc. For each set of samples there is a column labelled position e.g. C and O. The positions in this column match positions in column A. All the positions in each of the columns C, O etc for the 9 sets of samples are somewhere in column A and I want to be able to align the row labelled with the particular position with that position in column A. There is an example (though actual) in sheet 1. In sheet 1 I have postioned manually the first 3 rows of each of the 9 samples. I want to be able to do this for all samples.

Additionally, I have some other datasets in which the file format will be exactly the same (column positions and labels) but there will be many more rows (over 500,000) - will the code work on this too without editing?

I hope this is clearer but I do find it difficult to describe!

Thanks again.

Hi,
thanks so much to both of you who have replied - it is really appreciated. This macro has done exactly what I wanted and prompted me to look into how to do this myself and i'll look into this as soon as I get chance.
I am though having an issue running it on another file where I get the message:

Named range "keys" does not exist!

I can see this potential error message written into the macro but do not know why my file is forcing the error. The file has exactly (copied and pasted) the same headers as the file you posted and is in the same format - the only difference is the size. Now the new file is 800,812 rows in length. Presumably such an error can not be caused by the size, this is some other error? I do have the example file open at the same time and the macro runs fine on that. My new file prompts the error whether saved as excel 2007 format or excel 2007 macro enabled. Do I need to change some ranges in the macro?

I did but I didn't take it on board - my apologies. Thanks once again for your help (and patience)