Question on sorting

  • Thread starter Thread starter Michael Slater
  • Start date Start date
M

Michael Slater

I have a spreadsheet that contains 3 columns:

Col A = Names
Col B = Dates of Birth
Col C = An Assigned Password

The passwords are formatted as follows: A001Z, B002Y, C003X, etc...through
all rows.

The spreadsheet contains approximately 2300 rows, currently sorted by name.
I need to sort by Password in the above indicated order.

Anybody have any ideas how his might be done? Or if it's even possible?

Thanks,

Mike
 
Highlight the data in columns A to C, including a header row if you
have one. Click on Data | Sort and select column C as the first sort
field - if you have a header row and the header row box is ticked in
the panel, then the list of possible fields will be presented to you
as the names of the columns rather than Column A, Column B etc.

Ensure that Ascending order is checked, then click OK - the list
should appear in the order you want.

Hope this helps.

Pete
 
Select the area with the data.

Click on Data, then Sort

You'll see a drop down menu where you can select Column C

Hope that helps.
 
You need 2 help columns, preferably in D and E

assume you have headers, so in D2

=LEFT(C2)

in E2

=MID(C2,2,3)


copy down by double clicking the lower right corner of E2 after select D2
and E2
then if you want to sort by password sort by the first help column and
secondly the
second help column. If needed create a help column for the last character in
the pw

=RIGHT(C2,1)
 
Hi Peo,

don't you think that the password values (as quoted by the OP) will
sort correctly, without needing helper columns?

Pete
 
Yes they will, somehow I assumed the OP wouldn't have asked this unless
there was more to it than meets the eye
 
Yes, I thought that myself to begin with, then I realised that he had
posted into the newusers group, so I decided not to read too much into
it.

Pete
 
Hi Guys,

Thanks for the advice.

Yes, Peo is correct, in that, just performing a simple sort by the password
column did not produce the results I was looking for.

I need the sort order results to be sequential by password order. In other
words, the first password issued was A001Z, the second was B002Y. I have
over 2000 passwords following this format. A simple sort gives me all
passwords starting with "A" first, then "B", etc. What I need is the
original issue order as above, A001Z, B002Y, etc.

I am a new user to Excel, but I am quickly discovering it's many advantages
and how they can make my job a lot easier. My first, and greatest, problem
right now is attempting to apply Excel's functionality without having a
solid background using the program. I have several (large) books, which I
am currently reading from cover to cover, and always search through them, as
well as these newsgroups and the internet to try and find an answer before I
post a question. I try to be as self-sufficient as possible.

The second problem, in this case, is that I inherited this password system,
which has been used for the last 15+ years, and, had I managed it from the
beginning, would have kept a copy by Password order.

So, again, thank you, and I will try Peo's sorting method and see what
results.

Respectfully,

Mike
 
It looks like the first letter of your password increases with each
new one, as does the 3 digit number in the middle, and the final
letter decreases. If this is the case then you want to sort the
password by the 3 digits in the middle to get the order that you want.
Peo's column D is thus not needed - use his formula for column E to
extract the middle three digits and sort on these.

Hope this helps.

Pete
 
Back
Top