Solved: Sort by two criteria

[gscarter]

if i have data for example:


613036110 5655M15
613052715 5655M15
613053701 5655M15
613054000 5.70E+96
613054800 5655147
613054850 5.697E+96
613056000 5655M15
613056600 5655147
613057000 5655M15
613058000 5655M15
613058000 5.697E+96
613127670 5655M15
613193610 5655M15
613195000 5655147
613195004 5.697E+96
613195500 5655147
613195500 5655M15

Left hand column: Customer
Right hand column: Version

It is already sorted by customer, is there an easy way to sort the version if there are multiple entries for one customer.

for example, if i have 3 versions:



5.697E+96
5655147
5655M15

If a customer appears more than once, for example from above '613058000', is there a way to make sure that the highest entry for this customer is '5655M15' then '5655147' if it exists, if not '5655147'. But to do that each customer that appears more than once.

[RonMcK]

gscarter,

Try using Data > Sort, name first column Customer and second as Serial, select sort Customers Ascending and Serial Descending.

Hint: Change format of Serial column to Text (Format > Cells > Number > Category > Text) to force Excel to see 5697E96 as a string instead of as a 5.697 * 10 raised to the 96th power.


HTH,

[gscarter]

Thanks for help, worked perfectly.

As for changing the values I'm having problems with that because the original number is actually '5697E93' and when i copy it to a new workbook it copies as '5.697E+96'. I haven't been able to find a way around it yet.

Thanks
Gary

[RonMcK]

Gary,

How are you entering the values? Keying (typing) them in? Or, via copy and paste?

Have you tried, before you enter any values, formatting the entire column using the command sequence I mentioned? When I do that, first, and then key in values, they stay the way you want them.

If you are using copy and paste, try instead using paste special > values. This will enter the value in but leave your formatting unmolested.

HTH,

[gscarter]

Gary,

How are you entering the values? Keying (typing) them in? Or, via copy and paste?

Have you tried, before you enter any values, formatting the entire column using the command sequence I mentioned? When I do that, first, and then key in values, they stay the way you want them.

If you are using copy and paste, try instead using paste special > values. This will enter the value in but leave your formatting unmolested.

HTH,
I am using:




Set wb1 = Workbooks.Open(path1, True, True)
Set wb2 = Workbooks.Open(path2, True, True)

With ThisWorkbook.Worksheets("Sheet1")
.Columns("A:B").Value = wb1.Worksheets(sheetName).Columns("A:B").Value
.Columns("D:E").Value = wb2.Worksheets(sheetName).Columns("A:B").Value
End With


to copy the data from two workbooks into one.

Gary

[RonMcK]

Gary,

How are col A:B formatted before you paste the values? Are they just 'General' or are the 'Text'?

XLD or MD or <another expert>: Does Gary need to change the formulas to do the equivalent of edit > paste special > values ? If so, how does his code need to change?

Thanks!

[mdmackillop]

If the original cells are formated text, a simple copy/paste suffices. You can force the formatting if required


Sub Copies()
Columns("B:B").NumberFormat = "@"
Columns("A:B").Copy Range("H1")
End Sub

[gscarter]

The values which contain an E bring up the error "Number stored as text" in the original workbooks.

Thanks for all your suggestions, ill give them a go.

Gary