Free Microsoft Excel 2013 Quick Reference

Excel not sorting numbers correctly Results

hi guys,

i am average excel user and am having an issue.
I have some issues sorting a column.

Attached is a two sheet workbook:

on the first sheet is 5 columns (store #, Street, City, State, Zip)
on the second sheet i used this entry:
=Sheet1!A2 & " : " & Sheet1!B2 & ", " & Sheet1!C2 & ", " & Sheet1!D2 & " " & Sheet1!E2
(The attachment is set up as above)

When i then try and sort the sheet 2 column, it arranges the numbers very strangely, where 100 comes before 11, 23, and 7 for example.
The reason im concerned about the sorting is that i want to use the new column on sheet 2 in a pivot table that has other columns with store information.
When I create the pivot table and put the sheet 2 column in as a row label, it does the weird sorting.

I hope i am expressing my issue clearly. I would like it to sort in standard numerical order (1,2,3,...,9,10,11,...,98,98,100,...,199,200,201,...,etc)

I'd appreciate any help or suggestions

I need to be able to sort numbers of the following format:1-1,1-2,1-3,2-1,2-2,2-3,etc.
Excel is not happy with this. I first have to format the cell as text just to keep excel from reformatting my numbers as dates, but then it doesn't allow me to sort them correctly. Help me please.

When sorting numbers in excell with letter in front and then zeros, I
don't get the expected results. The Macintosh OSX finder views the
numbers correctly, but I can't seem to get the same results in excel.
Can this even be done in excel?

Excell sort
CA0002
CA00020
CA0021
CA02
CA021
CA02C
CA2

Needs to be
CA0002
CA02
CA02C
CA2
CA00020
CA0021
CA021

--
darkjedi
------------------------------------------------------------------------
darkjedi's Profile: http://www.excelforum.com/member.php...o&userid=29153
View this thread: http://www.excelforum.com/showthread...hreadid=489794

When sorting numbers in excell with letter in front and then zeros, I don't get the expected results. The Macintosh OSX finder views the numbers correctly, but I can't seem to get the same results in excel. Can this even be done in excel?

Excell sort
CA0002
CA00020
CA0021
CA02
CA021
CA02C
CA2

Needs to be
CA0002
CA02
CA02C
CA2
CA00020
CA0021
CA021

I have reviewed much information about color pallets at various web sites and looked at various postings here and still have not sorted out the following problem:

I wish to allow a user to select the color of a rectangle drawn in VBA. I wish to have the user select a cell and use Format/Cells/Patterns and select a background color for the cell.

The VBA code (see below for test program) will then acquire the number associated with the background color of the cell and then add the rectangle shape designating the fill color using the previously acquired number.

The code works in that the color of the rectangle does change as the color of the cell changes by user formatting, but the colors of the cell and the rectangle are surprisingly not the same. I suspect that the pallet for the two entities (cell and rectangle) are different, but I am at a loss as to how to set the Workbook color pallet the same as the pallet the user sees when using Format/Cells/Patterns.

For example, if I make the background color of call A1 red, the color number of 3 is captured correctly by the program, but the ActiveSheet.Shapes command that sets the fill color of the rectangle has a different color (green) associated with color number 3.

Any suggestions?

CODE FOLLOWS====================================================

Option Explicit

Sub Graphics()

Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled

Dim cposnx As Double, cposny As Double, vlen As Double, vwid As Double
Dim ctr_x1 As Double, ctr_y1 As Double
Dim Rect1 As Shape
Dim veh1_color As Integer

Worksheets("Sheet1").Activate
ActiveSheet.Shapes("Rect1").Delete

' Determine the background color (interior) of the cell
' Workbooks("color_test.xls").ResetColors
Worksheets("Sheet1").Range("A1").Activate
veh1_color = ActiveCell.Interior.ColorIndex

cposnx = 200 ' x-coord of RF corner position of rect
cposny = 100 ' y-coord of RF corner position of rect
vlen = 100 ' rect length in screen units
vwid = vlen / 3 ' rect width in screen units

Application.ScreenUpdating = False

ctr_x1 = cposnx + vlen / 2
ctr_y1 = cposny + vwid / 2

' Draw a rectangle
With ActiveSheet.Shapes.AddShape(msoShapeRectangle, cposnx, cposny, vlen, vwid)
.Name = ("Rect1")
End With
ActiveSheet.Shapes("Rect1").Fill.ForeColor.SchemeColor = veh1_color

Application.EnableCancelKey = xlInterrupt
Application.ScreenUpdating = True

End Sub

I am working with a pivot table that contains columns for year, month, and week number when items were processed. The year and month numbers sort correctly but the week numbers do not. How do I get the pivot tables to do this correctly without a ton of manual sorting.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1ClientYearMonthWeekNumberItemsDays (Ave.)2Durham200762411523  83221114   3421035  11484356   452447   465398  125235109   4983610   5081911   51281412 20081295413   123414   516215   454416   3795Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi All

I am trying to create a Bingo Card generator using VBA, I'd tried a few I had download and none of them used the UK rules that I require so I thought I would have a bash at it myself.

I've got as far as being able to create the card, all the numbers are in the right columns and none are repeated, however, I would now like the numbers to appear in the correct order, taking this as an example;

******** ******************** ************************************************************************>Microsoft Excel - Bingo.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB3=
BCDEFGHIJ3*112033*52*78*47*27**5963*815*1323**586870*Bingo*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Column D would be 20 - 23 - 27 but I would like Column I to be 70 - - 78 (retaining the missing middle cell).

I tried sorting but that just puts them in the right order, but removes the gaps which I would like to keep.

Has anyone got any suggestions as to how I can achieve this?

Thanks in advance...

I have looked through all the posts with the name Vlookup up, but can't see why my vlookup is failing.

This is the information given by Excel 97 help...

--------------------------------------------
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

∑ If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
∑ You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
∑ The values in the first column of table_array can be text, numbers, or logical values.

∑ Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

∑ If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
∑ If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
∑ If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

--------------------------------------------

I have a lookup table on the second tab of my worksheet, this is not sorted. This area on the second tab is named, and called Gb_Docs

My Vlookup, on the 1st page reads :-

=VLOOKUP(A1,GB_Docs,2,FALSE)

A1 on the first sheet is say AD-001, A2= L2
The GB_Docs data on page 2, looks like this:-

AD-001 Cherries
AK-001 Strawberries
FG-002 Bananas
L2 Cocoa
L8 Junk

The Vlookup, works for the first 3 items in this list, but returns #na for the last 2 items, and yet, according to the excel help, using the FALSE expression on the end, means the list doesn't need to be sorted.

Your starter for 10 is worth 10 points.

I have looked through all the posts with the name Vlookup up, but can't see why my vlookup is failing.

This is the information given by Excel 97 help...

--------------------------------------------
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

∑ If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
∑ You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
∑ The values in the first column of table_array can be text, numbers, or logical values.

∑ Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

∑ If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
∑ If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
∑ If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

--------------------------------------------

I have a lookup table on the second tab of my worksheet, this is not sorted. This area on the second tab is named, and called Gb_Docs

My Vlookup, on the 1st page reads :-

=VLOOKUP(A1,GB_Docs,2,FALSE)

A1 on the first sheet is say AD-001, A2= L2
The GB_Docs data on page 2, looks like this:-

AD-001 Cherries
AK-001 Strawberries
FG-002 Bananas
L2 Cocoa
L8 Junk

The Vlookup, works for the first 3 items in this list, but returns #na for the last 2 items, and yet, according to the excel help, using the FALSE expression on the end, means the list doesn't need to be sorted.

Your starter for 10 is worth 10 points.

I need to sort a large amount of data by a column that contains 3-character
numbers; some of the numbers are a mix of numbers and text (e.g. 16C) and
some of the numbers are all numbers (e.g. 151). I am using Excel 2003 on a
Windows XP computer. According to the Excel Help, I should be able to "sort
anything that looks like a number as a number" as long as I have formatted
all the numbers in the column to be text (which I have done). However, I
can't find a way to specify to sort numbers stored as text as numbers. I want
16C to show in the column after 151 but before 161, but when I select
Data/Sort... there is nothing that indicates which way I want to sort the
numbers. Instead, the numbers are sorted first by number, then by alpha (i.e.
16C shows up after 999). It is sorted the same way regardless of whether the
numbers are formatted as numbers or text. This is very frustrating because
the Help doesn't give me any information on how to force Excel to sort the
way I need it to sort.

I've read some of the recent posts about sorting numbers that include text,
but the solutions all seem to require an extra column with a long formula to
make Excel sort correctly. While I am familiar with formulas and have
successfully used them, I don't understand why this type of sorting would
require formulas when the Help documentation seems to indicate that Excel
will already sort the way I need. Is this a known glitch? Why would it be
mentioned in the Help but not actually perform the way it is documented?

Thanks for your help.

I would consider my self an intermediate user of excel, and I am just not
sure what the fastest way of formatting my text file would be.

I imported a text file into excel and now it needs some manipulation. I
know how to do most, but I am stuck on how to get my item number linked to
the appropriate data. See below.

Item: 12345
1015839 37315 12.83 12.33 .50

1015668 37315 64.17 61.67 2.50

1015661 37315 38.50 37.00 1.50

Item: 67890
1000495 13732 22.36 20.86 1.50

1000639 37233 22.36 20.86 1.50

1000339 37239 22.27- 20.77- 1.50-

1000686 37246 22.36 20.86 1.50

I need to link the item number to each line in its group so then I can sort
by item and then I can go on from there and use the data and create a pivot
table to get my answers. I just would like to know if there is something
quicker than cutting a pasting the item number next to each line that doesn't
have the item number? I have 177 items and 16,000 rows of data, and 5 more
reports like this to run. I am waiting on our IT to redo the reports itself
so it will include the item number in each line, but who knows when that will
happen. Please help if you can or give me some advice. Thanks. Sorry if I
am not using the correct terminology.

This originally got buried under someone's February post so please forgive
the reposting...

User has Excel 2000. This spreadsheet has a column identified as an ID# but
the numbering is not consistent. The majority within the data are 5-digit
numbers. There are some that start with a leading zero. There are also
some 10+ digit numbers which have letters in them, generally at the end.
There are even some records that do not have any ID and that field is blank.
So far we have not been able to get a good sort out of the data.

The sort first gives us the 11xxx numbers, followed by 2 through 8 5-digit
numbers, then followed by the 5-digit numbers with a leader zero. Then it
gives some of the 10+ digits #s which include a letter within a sort order
starting with the first number (e.g., 123485767GH, 135679848B, 348674798HH,
etc.). Then some 10xxx numbers start up again and we start numbering all
over. The blank cell rows are sorted at the END of the document when I
would think they would have been put at the top.

I have copied entire sheet to new document and pasted as special values. I
have copied just this column of data as well as another column 100%
populated with data and pasted special values. I have formatted the column
to text and as number. The data continues to sort in the same incorrect
fashion.

The puzzler is that it does sort 100% correctly in Excel XP. So, can
anybody suggest anything to try in Excel 2000 or is the only answer for the
user to be upgraded? TIA!

I have a multi-column sheet with 326 rows that I am attempting to sort by:
State, City and Name. Currently the values for State are limited to "NJ".
If I sort by the above order there are eleven rows of data that are not
sorted correctly. They occur about 2/3ds of the way down the sheet.

I have already tried changing formats to text and number, copying and
pasting into another workbook as values. No fix. It does work normally if
you copy and paste into Word. But if you copy and paste back into Excel it
reverts back.

Any ideas on the cause and remedy would be greatly appreciated.

confused,

i know your project is probably over by now but i came across your post
today and had to reply. i too suffer from "data slip" and you are the first
person to talk about. i am wondering if you have had any luck resolving the
problem. i understand select all needs to be done before sorting and i am
assuming you do this as well.

i have a similar spreadsheet in terms of size but mine deals with projects
and milestones so there are lots of dates on a single row. i began to find
dates that were way out of place. soon i began testing. i would enter a date
in a cell, save the ss, close, reopen, and the data was in a different cell.
with mine it didn't just slip to empty cells. i suspect your data copied over
other data also but it was harder to spot. my ss is shared by multiple users.
i have also noticed that the change history will not record changes correctly
(i.e. a cell which has not been changed will show a change made to it while
the cell whose data was changed does not show a change). the only way i can
resolve the problem is to unshare the ss, fix the cells and then reshare it.
this has really become a problem.

none of the techs i have spoken with have been able to recreate the problem.
the latest suggesting i have been given is to make sure all versions of excel
which edit the ss have been fully updated. like you i sort and filter the ss
all the time.

as a side note, i too am a novice compared to the average person answering
posts on this site but i had a few thoughts about your project if it could
still help. first, don't sort while the ss is filtered, this will hang your
computer forever - sort first then filter. second, this is a low tech
solution for comparing a ss without having a unique key column - what about
creating one ss which at least has all the last names filled in and make
another which has at least the zip and so on. compare each to another ss
which has been prepared the same way. when finished merge them back together.
i'm not sure it that makes any sense but maybe it will help.

anyway, anything you can share about "data slip" would be appreciated. thanks,

scott

"ConfusedNovice" wrote:

> Thanks, Dave. This thread is getting unwieldy, but I
> *think* I understand what you're advising.
>
> Sticking with the issues at hand, yes, the records are all
> one per row. Last Name is probably the best key, but there
> are some records with only a Company Name, & a few with
> only an email name. I have been using Last Name & Zip
> Codes to try & match things up.
>
> I discovered that when sorting some of the intermediate db
> versions by "new" entries, those newest entries were
> mostly correct. I am thinking of isolating these records &
> copy/pasting them on to the oldest db version. The idea is
> to try for a cleaner sheet to compare to the current one.
> Maybe it would be better not to merge these? It's crunch
> time - I will try your formulae on a test file today.
>
> And moving ahead: If I can execute the comparisons you
> suggest, and if I can achieve a cleaner list, then what's
> the best way to prevent data from slipping into the empty
> cells in the future? Should I make a new index column,
> number each record, and append & number all new records?
> Can people safely sort & filter the data? What if they
> update a record in a filtered list & then re-sort? Or
> Find/Replace & then re-sort? I still don't understand how
> some of the data slipped out of one record & into another.
> Until I do understand, I'm afraid it will continue to
> happen & all this work will be for nothing.
>
> Thanks again! It's an ugly way to learn Excel.
>
> Jane
>
> Subject: How to sort/update large excel db
> From: "Dave Peterson" >
> Sent: 9/30/2004 1:05:46 PM
>
> Just to clarify, is your data laid out one record per
> row? You don't use
> multiple rows to represent one "logical" record, do you?
>
> If you do, then this will make it even more difficult.
>
> But to match/merge two different worksheets, I would think
> that you'd need to
> have a unique key/index into the data.
>
> If you don't have this key, how do you match up your data
> to look for
> differences?
>
> I wouldn't put too much faith in having that unique key in
> the last name
> column--but maybe you can use a couple of columns that
> could serve as that key.
>
> For instance, if you had the lastname, firstname, address
> in 3 separate columns,
> you could use a helper cell and do something like:
> =A2&char(10)&b2&char(10)&c2
> (The char(10) probably won't appear in your data and will
> serve as a field
> delimiter--just in case there's records that might look
> identical when
> combined.)
>
> But even if you did this, you'd have to make sure that
> these fields weren't
> changed. If any were changed, then you couldn't use them
> for a match between
> worksheets.
>
> To find out if the records in one worksheet are in the
> other (based on any
> single column you can use--in your data or derived from
> your data):
>
> =isnumber(match(a2,sheet1!a:a,0))
> and drag down.
> You'll see True if it appears on that other worksheet.
>
> And use the equivalent to check the other worksheet.
>
> Once you've found your Falses--you'll want to inspect them
> to see if they should
> be cleansed--cleaned up to make them match.
>
> You could end up fixing the data on either worksheet--just
> to find the matches.
>
> Once you get rid of all the Falses, you can do the =vlookup
> () stuff to match
> merge.
>
> I'm not sure if all this work can be done on a quick
> schedule. This kind of
> stuff usually means fix, check, fix, check....until you
> can't find any more
> differences. Then you let someone else review it and it
> starts again.
>
> Good luck,
>
>
>
> ConfusedNovice wrote:
> >
> > Still trying to understand what I've got. There is no
> > column with all unique entries. Every column has blank
> > cells someplace. On 2 of the earlier database versions,
> > the leftmost column (Column A) is set up as "Index
> > Column," but not all the records have an "Index" number
> in
> > this column - some of them are blank, & some have zeros
> in
> > Column A. The most recent master does not have Index
> > column, it starts with a Last Name field.
> >
> > This brings me back to the question of how to prevent
> > names/values from slipping into the blank cells when
> > sorting & updating records. I have been reading a lot of
> > Excel info, but this is *not* my area at all, & I want to
> > fix these problems, not compound them. Should I be able
> to
> > identify key column(s)? If yes, how? And if no, is there
> > any safe way to sort when blanks exist in every column?
> > (Or can it be a Key & contain multiple blank cells?)
> >
> > There are ~17,000 records, & when the wrong values move
> > into a blank cell, that record doesn't indicate that it
> > has been changed. (Probably because it was not opened?)
> >
> > I'm seriously running out of time. I've got a pretty big
> > list of changes ready to enter directly on db records. I
> > also have isolated a few hundred recent entries by date,
> > verified them, & placed them in a separate worksheet.
> > (Hoping to append to a clean main list & sort into
> place.)
> > I don't think it's possible to verify every row.
> >
> > Yes, no matter how you look at this, it's a pain! I'm not
> > really dumb, just a total rookie, & your help is
> > invaluable.
> > To summarize:
> > Whatsup with "Index Column"?
> > How to identify/designate Key columns?
> > Implications of having blank cells throughout sheet?
> > Fastest way to add/edit records if I go back to an older,
> > cleaner version of the database?
> >
> > Thanks again!
> >
> > Jane
> >
> > >-----Original Message-----
> > >I'm saying that if you have two "live" copies of the
> > workbook, it'll be a pain
> > >to merge one of them back into the other.
> > >
> > >There's nothing built into excel that will force you to
> > make sure your key
> > >column entries are unique.
> > >
> > >When I do this, I spend some time verifying that the
> data
> > is at least
> > >consistent--no additional entries with the same key were
> > added. If you can
> > >believe/verify this, then, yeah, you can do =vlookup()'s
> > to merge the updated
> > >values into the one real workbook.
> > >
> > >It's just in my experience, avoiding this is usually
> > better. (Just give the
> > >file to someone else and tell them to return it when
> > they're done--and put a
> > >freeze on any other changes.)
> > >
> > >
>
>
>

I am attempting to create a file system using excel that can be easily
searched and sorted by a number ie 1.3.12 or alphabetically. At this time I
have entered the data as I would like it to appear if sorted by number,
however if I attempt to alphabetize I can not restore the numbers to their
proper order.

Currently I have only 3 columns. One with numbers, one with file names, and
one with additional information. With the numbering extending to 8 numbers
(15.1.1.3.11.4.7.1)

Here is a more detailed example of what I have.

1 Office
1.1 Employees
1.1.1 Bob Marketing
1.1.2 Jan IT
…
1.1.12 Chris Marketing
1.2 Equipment
1.2.1 Computers
….
12 Locations
12.1 USA
12.1.1 New York
12.1.1.1 New York Primary
12.2 New Mexico
12.2.1 Albuquerque Primary
…
12.2.15 California
12.2.15.1 L.A. Secondary
…

I recently posted this same question, however could not seem to get the
suggested macros to work. I have included the suggestions I received below
with a description of why they did not work for me.

Dave Peterson suggested putting each number in a separate column and sorting
that way. This amounts to almost the same thing as Bernd Plumhoff suggested,
however he did it by creating an example macro that can be downloaded from
this link: http://www.bplumhoff.de/software/sort_chapter.xls

In both cases, however, the numbers still do not end up on the correct
order. The numbering ends up going something like:
3.4.1
3.4.10
3.4.11
3.4.2
3.4.3

Ron Rosenfeld made a suggestion which I have included below. When I
attempted to run the included code it said there is a syntax error in the
first line…and highlighted it yellow. Also, to work the new function I have
to type =combine(A1) (or whatever cell my number with multiple decimals is
in)?

Any more help would be much appreciated.

-Ian

My first post of this question can be found at:
http://www.microsoft.com/office/comm...e-06dca1ac3de3

---------------------------Ron’s
suggestion----------------------------------------------
Although I'm sure this can be done with worksheet functions, I found it
relatively simple to do using a VBA UDF (user defined function).

The "trick" is to transform the outline number appropriately, so that when
sorted it will give you the result you want. You then put that number in an
adjacent column to your table, and sort on that column. When done, you can
delete the column.

To enter the UDF, opens the VB editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Be sure to note the MaxNum constant which is the Maximum Number of outline
sublevels you might have. In your example, you showed four, so that is what I
used. In addition, I have assumed that the maximum number of levels within
each
sublevel is 99.

In a column adjacent to your table, enter the formula:
=combine(cell_ref) (where cell_ref is the outline number)
and copy/drag down as far as needed.

Then sort on this new column (ascending).

=================================
Function combine(rg As Range) As Double
Const dot As String = "."
Const NullString As String = ""
Const MaxLevels As Long = 4 'Maximum number of levels
Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be
10^x-1
Dim i As Long
Dim temp

temp = Split(rg.Text, dot)

For i = 0 To UBound(temp)
combine = combine + temp(i) * 10 ^ (((MaxLevels - 1) - i) _
* Log(MaxSubLevels + 1) / Log(10))
Next i

End Function
==================================

HTH,

--ron
---------------------End Ron’s
suggestion----------------------------------------------

Thanks aganin,
-Ian

Select a cell that has leading zeroes, and post back with exactly what the
cell displays, and exactly what the formula bar displays.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:DA5D6838-5796-4AEA-9685-9606D973587F@microsoft.com...
> I just sorted that same data as well and it also worked for me. There
must
> be something within the spreadsheets that were given to us that is messing
it
> up because the last four digits I used in my example were taken directly
from
> SSNs that were out of order.
>
> One thing is that the SSN is formatted as an SSN (format cells, number,
> special, SSN). When I change this to a regular number, the 0's in the
> beginning and end disappear.
>
> I just tried your system with some SSNs that began with 0 and this is what
> happens:
>
> The SSN is 000-42-0400 for example. When I do Text to Column and it comes
> up in the box to put in the dividers, it looks like this:
>
> 420400
>
> ...because the 0s in the front disappear. I have no choice but to put the
> dividers like this:
> 420 | 40 |
>
> ...because that is where the lines go for SSNs that do not have 0s missing
> from them. So when it is broken down, it goes into sort boxes that way.
So
> if I have two SSNs, 000-42-0400 and 123-45-6789, they look like this in
the
> extra columns:
>
> 420 40
> 123 45 67 89
>
> ...and get sorted as such.
>
> I don't mean to be a pain, and your suggestion is great! Can you think of
> what could be causing it to do this? It has to be something within the
> spreadsheet, the way the cells are formatted, because I get the correct
> output when I hand type sample SSNs into a new spreadsheet.
>
> Thank you.
>
>
>
>
> "Ragdyer" wrote:
>
> > I just sorted your example numbers, following the instructions that I
posted
> > here, and they sorted *exactly* as you stated that they *should be*.
> >
> > The procedure works fine!
> >
> > Say the original numbers are in Column A, and the 6 parsed (separated)
> > columns are B to G.
> > B = first 3 numbers
> > C = dash
> > D = middle 2 numbers
> > E = dash
> > F = first 2 numbers of last set
> > G = last 2 numbers
> >
> > After the 6 columns were parsed along side the original data, did you
select
> > *all* columns.
> > Did you then set the *first* sort key as Column G (last 2 numbers),
> > *Second* key as Column F,
> > And *third* sort key as Column D?
> >
> > It worked perfectly for me!!!
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> >
> > "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> > news:38BA77F6-7318-4AC1-A376-49D07BC2CF6C@microsoft.com...
> > > Ok now that I went and wrote that everything worked fine I have to
write
> > > again and say it didn't work when I actually put all 45K names
together.
> > > Here is what is happening (again, with the 0's)...
> > >
> > > I have the following SSNs:
> > > 111-11-4030
> > > 222-22-0400
> > > 333-33-7040
> > > 444-44-0500
> > > 555-55-0300
> > > 666-66-0040
> > >
> > > In TDO, they should show up like this:
> > > 555-55-0300
> > > 222-22-0400
> > > 444-44-0500
> > > 111-11-4030
> > > 666-66-0040
> > > 333-33-7040
> > >
> > > ...but instead they are showing up like this:
> > > 111-11-4030
> > > 555-55-0300
> > > 666-66-0040
> > > 333-33-7040
> > > 222-22-0400
> > > 444-44-0500
> > >
> > > ...which is not TDO. I believe this is happening because of the 0's
that
> > > are disappearing at the end of the SSN, but I don't know for sure.
Any
> > > suggestions?
> > >
> > >
> > >
> > > "RagDyer" wrote:
> > >
> > > > Since your *original* data is *untouched*, I don't see what
difference
> > that
> > > > makes.
> > > >
> > > > You're going to sort on 4 ... right?
> > > >
> > > > You're going to throw it (04, 4)away when you're done anyway!
> > > >
> > > > Perhaps you're missing the original concept of this procedure.
> > > > --
> > > > Regards,
> > > >
> > > > RD
> > > > --------------------------------------------------------------------
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > -------------------------------------------------------------------
> > > >
> > > > "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> > > > news:9EE9AF0A-8E2F-4F86-9CCA-F14B12ED0FC2@microsoft.com...
> > > > > Ok, I tried all of the options given here and this one seems to be
the
> > > > best
> > > > > solution. My only concern now is that the spreadsheets were given
to
> > us
> > > > set
> > > > > up as SSNs and Numbers and not as text so whenever I make it text
to
> > > > column
> > > > > and one of the columns has a "04" in it, then only the four shows
up.
> > I
> > > > have
> > > > > over 11K names and SSNs on one list (and five lists) so how can I
make
> > > > those
> > > > > 0's appear without having to go thru each and every number to
input
> > the 0
> > > > > manually?
> > > > >
> > > > > Thank you!
> > > > >
> > > > >
> > > > >
> > > > > "RagDyer" wrote:
> > > > >
> > > > > > Just repeating, TTC is *perfect* for what you're looking to do.
> > > > > >
> > > > > > Select your column of numbers, then,
> > > > > > <Data> <Text To Columns> <Fixed Width> <Next>
> > > > > > Then create 5 break lines, separating your last set of 4 digits
in
> > the
> > > > > > middle and then separating out the dashes from the other
numbers.
> > > > > > Then click <Next>
> > > > > >
> > > > > > Now, change the address in the "Destination" box, to a column
where
> > > > there's
> > > > > > room to print out the 6 columns, and also this allows the
original
> > > > column of
> > > > > > numbers to remain intact, where they were.
> > > > > >
> > > > > > Now click <Finish>
> > > > > >
> > > > > > You have your original data, with adjoining columns parsed out
the
> > way
> > > > you
> > > > > > separated them.
> > > > > > Now select them all, and sort in any order that you wish.
> > > > > >
> > > > > > When you're done, throw away those extra columns.
> > > > > > --
> > > > > > HTH,
> > > > > >
> > > > > > RD
> > > > > > ==============================================
> > > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > > > > ==============================================
> > > > > >
> > > > > > "postitnote" <postitnote@discussions.microsoft.com> wrote in
message
> > > > > > news:D312381B-518B-4CC8-803A-D6493D7D9511@microsoft.com...
> > > > > > > This sounds like a very good idea though after looking at my
> > > > spreadsheet I
> > > > > > > must admit that I'm not sure what to do with this information.
> > What
> > > > is a
> > > > > > > "helper column" and how do I get it? I did input the
information
> > you
> > > > gave
> > > > > > me
> > > > > > > (=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it
changed
> > the
> > > > > > column,
> > > > > > > but it's not exactly what I need (see my reply to the post
made by
> > Ken
> > > > in
> > > > > > > this thread).
> > > > > > >
> > > > > > > Thank you for your help.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ron Rosenfeld" wrote:
> > > > > > >
> > > > > > > > On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
> > > > > > > > <postitnote@discussions.microsoft.com> wrote:
> > > > > > > >
> > > > > > > > >I work in a medical records office and we are attempting to
> > combine
> > > > > > five
> > > > > > > > >years' worth of records onto one spreadsheet. Our office
uses
> > > > Terminal
> > > > > > Digit
> > > > > > > > >Order (or TDO) and it doesn't appear that Excel can sort
this
> > way
> > > > as it
> > > > > > is in
> > > > > > > > >order with the last four digits, then the middle two, then
the
> > top
> > > > > > three.
> > > > > > > > >Does anyone know if there is a way to do this without
having to
> > put
> > > > the
> > > > > > SSN
> > > > > > > > >in three separate columns and then merging the columns
together
> > > > > > somehow?
> > > > > > > >
> > > > > > > > The simplest method would be to add a "helper column" that
has
> > the
> > > > digit
> > > > > > groups
> > > > > > > > in the order to be sorted -- then sort on that column.
> > > > > > > >
> > > > > > > > If your digit groups are separated by dashes (and are
located in
> > > > column
> > > > > > G),
> > > > > > > > then:
> > > > > > > >
> > > > > > > > =RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)
> > > > > > > >
> > > > > > > > will return a text string of the digits in the order you
> > specified.
> > > > > > > >
> > > > > > > > Include this helper column in your sort table, and sort on
that
> > > > column.
> > > > > > You
> > > > > > > > can then delete or hide the helper column.
> > > > > > > >
> > > > > > > > If it is going to be a repetitive task, it can be automated
> > using a
> > > > > > macro.
> > > > > > > >
> > > > > > > > If your numbers are NOT separated by dashes, but are rather
a
> > > > sequential
> > > > > > > > string, then use this formula:
> > > > > > > >
> > > > > > > > =RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
> > > > > > > > G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)
> > > > > > > >
> > > > > > > >
> > > > > > > > --ron
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >

I am attempting to create a file system using excel that can be easily
searched and sorted by a number ie 1.3.12 or alphabetically. At this time I
have entered the data as I would like it to appear if sorted by number,
however if I attempt to alphabetize I can not restore the numbers to their
proper order.

Currently I have only 3 columns. One with numbers, one with file names, and
one with additional information. With the numbering extending to 8 numbers
(15.1.1.3.11.4.7.1)

Here is a more detailed example of what I have.

1 Office
1.1 Employees
1.1.1 Bob Marketing
1.1.2 Jan IT
…
1.1.12 Chris Marketing
1.2 Equipment
1.2.1 Computers
….
12 Locations
12.1 USA
12.1.1 New York
12.1.1.1 New York Primary
12.2 New Mexico
12.2.1 Albuquerque Primary
…
12.2.15 California
12.2.15.1 L.A. Secondary
…

I recently posted this same question, however could not seem to get the
suggested macros to work. I have included the suggestions I received below
with a description of why they did not work for me.

Dave Peterson suggested putting each number in a separate column and sorting
that way. This amounts to almost the same thing as Bernd Plumhoff suggested,
however he did it by creating an example macro that can be downloaded from
this link: http://www.bplumhoff.de/software/sort_chapter.xls

In both cases, however, the numbers still do not end up on the correct
order. The numbering ends up going something like:
3.4.1
3.4.10
3.4.11
3.4.2
3.4.3

Ron Rosenfeld made a suggestion which I have included below. When I
attempted to run the included code it said there is a syntax error in the
first line…and highlighted it yellow. Also, to work the new function I have
to type =combine(A1) (or whatever cell my number with multiple decimals is
in)?

Any more help would be much appreciated.

-Ian

My first post of this question can be found at:
http://www.microsoft.com/office/comm...e-06dca1ac3de3

---------------------------Ron’s
suggestion----------------------------------------------
Although I'm sure this can be done with worksheet functions, I found it
relatively simple to do using a VBA UDF (user defined function).

The "trick" is to transform the outline number appropriately, so that when
sorted it will give you the result you want. You then put that number in an
adjacent column to your table, and sort on that column. When done, you can
delete the column.

To enter the UDF, <alt><F11> opens the VB editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Be sure to note the MaxNum constant which is the Maximum Number of outline
sublevels you might have. In your example, you showed four, so that is what I
used. In addition, I have assumed that the maximum number of levels within
each
sublevel is 99.

In a column adjacent to your table, enter the formula:
=combine(cell_ref) (where cell_ref is the outline number)
and copy/drag down as far as needed.

Then sort on this new column (ascending).

=================================
Function combine(rg As Range) As Double
Const dot As String = "."
Const NullString As String = ""
Const MaxLevels As Long = 4 'Maximum number of levels
Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be
10^x-1
Dim i As Long
Dim temp

temp = Split(rg.Text, dot)

For i = 0 To UBound(temp)
combine = combine + temp(i) * 10 ^ (((MaxLevels - 1) - i) _
* Log(MaxSubLevels + 1) / Log(10))
Next i

End Function
==================================

HTH,

--ron
---------------------End Ron’s
suggestion----------------------------------------------

Thanks aganin,
-Ian

User has Excel 2000. This spreadsheet has a column identified as an ID# but
the numbering is not consistent. The majority within the data are 5-digit
numbers. There are some that start with a leading zero. There are also
some 10+ digit numbers which have letters in them, generally at the end.
There are even some records that do not have any ID and that field is blank.
So far we have not been able to get a good sort out of the data.

The sort first gives us the 11xxx numbers, followed by 2 through 8 5-digit
numbers, then followed by the 5-digit numbers with a leader zero. Then it
gives some of the 10+ digits #s which include a letter within a sort order
starting with the first number (e.g., 123485767GH, 135679848B, 348674798HH,
etc.). Then some 10xxx numbers start up again and we start numbering all
over. The blank cell rows are sorted at the END of the document when I
would think they would have been put at the top.

I have copied entire sheet to new document and pasted as special values. I
have copied just this column of data as well as another column 100%
populated with data and pasted special values. I have formatted the column
to text and as number. The data continues to sort in the same incorrect
fashion.

The puzzler is that it does sort 100% correctly in Excel XP. So, can
anybody suggest anything to try in Excel 2000 or is the only answer for the
user to be upgraded? TIA!

Excel - Sorting - Please help

Happy Holidays Everyone! I have a major problem.

I need to write a VBA macro for a more advanced sorting.

For example: if i have numbers that I must sort by and they range say from 1 to 200, some numbers have say 78A and 78B. When I sort, excel automatically puts them to the bottom of the list, not the acompanying 77,78A,78B,79 etc. place. In other words it looks like this: 200,78A,78B when it should look like what I previously mentioned.
What can I do to make excel sort it correctly? Please help, many thanks.
Possible macro?

-Arthur

I'm working with columns with "addresses" which are always constructed
with one or more letters (Or a word) first and then follows a number,
like f.ex.:

X1
X10
X2

But if I sort them in excel from top to bottom/a-z they are not sorted
correctly, it always comes out with "X10" before "X2" like shown above.
I would like to sort them so that they look like this:

X1
X2
X10

But I can't seem to get it to work... Formatting the cells doesn't seem
to help either.


No luck finding an answer? You could always try Google.