I have an excel spreadsheet with part numbers in Column A and bin
locations in Cols. B to G. I would like to find a formula that sorts
the bin locations in Cols. B-G alphabetically. The bin locations are
formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts like this:
22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

--
tb

I have an excel spreadsheet with part numbers in Column A and bin
locations in Cols. B to G. I would like to find a formula that sorts
the bin locations in Cols. B-G alphabetically. The bin locations are
formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts like this:
22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

Why not use Excel's built-in Sort feature on the row[s]?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 12/12/2017 at 11:05:12 PM GS wrote:

I have an excel spreadsheet with part numbers in Column A and bin
locations in Cols. B to G. I would like to find a formula that
sorts the bin locations in Cols. B-G alphabetically. The bin
locations are formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts like
this: 22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

Why not use Excel's built-in Sort feature on the row[s]?

Because (I think) I would have to sort each row individually. That
would take forever.

Each row has bin numbers for a different part number, so I would have
to sort Row 1 by itself, then sort Row 2 by itself, etc. etc.

Am I perhaps missing something?
--
tb

On 12/12/2017 at 11:05:12 PM GS wrote:

I have an excel spreadsheet with part numbers in Column A and bin
locations in Cols. B to G. I would like to find a formula that
sorts the bin locations in Cols. B-G alphabetically. The bin
locations are formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts like
this: 22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

Why not use Excel's built-in Sort feature on the row[s]?

Because (I think) I would have to sort each row individually. That
would take forever.

Each row has bin numbers for a different part number, so I would have
to sort Row 1 by itself, then sort Row 2 by itself, etc. etc.

Am I perhaps missing something?

In the Sort dialog Options/Orientation, choose Left to Right!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 12/13/2017 at 2:49:34 PM GS wrote:

On 12/12/2017 at 11:05:12 PM GS wrote:

I have an excel spreadsheet with part numbers in Column A and
bin locations in Cols. B to G. I would like to find a formula
that sorts the bin locations in Cols. B-G alphabetically. The
bin locations are formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts like
this: 22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

Why not use Excel's built-in Sort feature on the row[s]?

Because (I think) I would have to sort each row individually. That
would take forever.

Each row has bin numbers for a different part number, so I would
have to sort Row 1 by itself, then sort Row 2 by itself, etc. etc.

Am I perhaps missing something?

In the Sort dialog Options/Orientation, choose Left to Right!

Right but then you have to tell Excel with one of the rows is the
primary sorting key. All the other rows will be sorted based on the
primary sorting key. I need to sort each row _independently_ from any
other row.

Say I have the following bin locations in Row 1 and Row 2 (each row
represents bin locations for a different part number):
22-2D 49-6A 48-1A 49-1C 49-5A
30-1B 05-2C 25-4D 01-1A 11-5B

When I do a Custom Sort - Options - Sort left to right, I have to
specify a Sort By Row key. Let's assume that I pick Row 1 as my Sort
By Row key. The end result would be this:
22-2D 48-1A 49-1C 49-5A 49-6A
30-1B 25-4D 01-1A 11-5B 05-2C

As you can see, Row 1 was sorted correctly but Row 2 was not.

--
tb

On 12/13/2017 at 2:49:34 PM GS wrote:

On 12/12/2017 at 11:05:12 PM GS wrote:

I have an excel spreadsheet with part numbers in Column A and
bin locations in Cols. B to G. I would like to find a formula
that sorts the bin locations in Cols. B-G alphabetically. The
bin locations are formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts like
this: 22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

Why not use Excel's built-in Sort feature on the row[s]?

Because (I think) I would have to sort each row individually. That
would take forever.

Each row has bin numbers for a different part number, so I would
have to sort Row 1 by itself, then sort Row 2 by itself, etc. etc.

Am I perhaps missing something?

In the Sort dialog Options/Orientation, choose Left to Right!

Right but then you have to tell Excel with one of the rows is the
primary sorting key. All the other rows will be sorted based on the
primary sorting key. I need to sort each row _independently_ from any
other row.

Say I have the following bin locations in Row 1 and Row 2 (each row
represents bin locations for a different part number):
22-2D 49-6A 48-1A 49-1C 49-5A
30-1B 05-2C 25-4D 01-1A 11-5B

When I do a Custom Sort - Options - Sort left to right, I have to
specify a Sort By Row key. Let's assume that I pick Row 1 as my Sort
By Row key. The end result would be this:
22-2D 48-1A 49-1C 49-5A 49-6A
30-1B 25-4D 01-1A 11-5B 05-2C

As you can see, Row 1 was sorted correctly but Row 2 was not.

Can you use a macro?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 12/13/2017 at 5:49:29 PM GS wrote:

On 12/13/2017 at 2:49:34 PM GS wrote:

On 12/12/2017 at 11:05:12 PM GS wrote:

I have an excel spreadsheet with part numbers in Column A
and bin locations in Cols. B to G. I would like to find a
formula that sorts the bin locations in Cols. B-G
alphabetically. The bin locations are formatted as Text.

For instance, Cols B1 to G1 show:
22-2D 49-6A 48-1A 49-1C 49-5A

I would like to have a formula in Cols. H1 to O1 that sorts
like this: 22-2D 48-1A 49-1C 49-5A 49-6A

Can anybody help me? Thanks.

Why not use Excel's built-in Sort feature on the row[s]?

Because (I think) I would have to sort each row individually.
That would take forever.

Each row has bin numbers for a different part number, so I would
have to sort Row 1 by itself, then sort Row 2 by itself, etc.
etc.

Am I perhaps missing something?

In the Sort dialog Options/Orientation, choose Left to Right!

Right but then you have to tell Excel with one of the rows is the
primary sorting key. All the other rows will be sorted based on the
primary sorting key. I need to sort each row independently from any
other row.

Say I have the following bin locations in Row 1 and Row 2 (each row
represents bin locations for a different part number):
22-2D 49-6A 48-1A 49-1C 49-5A
30-1B 05-2C 25-4D 01-1A 11-5B

When I do a Custom Sort - Options - Sort left to right, I have to
specify a Sort By Row key. Let's assume that I pick Row 1 as my
Sort By Row key. The end result would be this:
22-2D 48-1A 49-1C 49-5A 49-6A
30-1B 25-4D 01-1A 11-5B 05-2C

As you can see, Row 1 was sorted correctly but Row 2 was not.

Can you use a macro?

I'm open to all suggestions at this point...

--
tb

Can you use a macro?

I'm open to all suggestions at this point...

I'm thinking your criteria "Not Macro" implies the file contains no macros;
-thus a macro in Personal.xls would be available to all open files.

What I suggest is to select the cells to be sorted before running the macro.
The SortData() macro will step through the selection row by row, sorting each
row as it goes.

Copy this code into a standard module:

Option Explicit

Sub SortData()
Dim vData, n&, j&, s1$, as1$()
vData = Selection
For n = LBound(vData) To UBound(vData)
s1 = "": Erase as1
For j = 1 To UBound(vData, 2)
s1 = s1 & "|" & Left(vData(n, j), 5)
Next 'j
as1 = Split(Mid(s1, 2), "|")
SelectionSort as1
For j = 1 To UBound(vData, 2)
vData(n, j) = as1(j - 1) '
Next 'j
Next 'n
Selection = vData
End Sub

Public Sub SelectionSort(ListArray() As String, _
Optional ByVal bAscending As Boolean = True, _
Optional ByVal bCaseSensitive As Boolean = False)

Dim sSmallest$, lSmallest&, lCount1&, lCount2&
Dim lMin&, lMax&, lCompareType&, lOrder&

lMin = LBound(ListArray): lMax = UBound(ListArray)
If lMin = lMax Then Exit Sub

'Order Ascending or Descending?
lOrder = IIf(bAscending, -1, 1)

'Case sensitive search or not?
lCompareType = IIf(bCaseSensitive, vbBinaryCompare, vbTextCompare)

'Loop through array swapping the smallest\largest (determined by lOrder)
'item with the current item
For lCount1 = lMin To lMax - 1
sSmallest = ListArray(lCount1): lSmallest = lCount1

'Find the smallest\largest item in the array
For lCount2 = lCount1 + 1 To lMax
If StrComp(ListArray(lCount2), sSmallest, lCompareType) = lOrder Then
sSmallest = ListArray(lCount2): lSmallest = lCount2
End If
Next

'Just swap them, even if we are swapping it with itself,
'as it is generally quicker to do this than test first
ListArray(lSmallest) = ListArray(lCount1)
ListArray(lCount1) = sSmallest
Next
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

You might want to rename SortData to SortRowData and change the scope of
SelectionSort to Private so it doesn't appear in the Macros dialog.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 12/14/2017 at 3:18:53 AM GS wrote:

You might want to rename SortData to SortRowData and change the scope
of SelectionSort to Private so it doesn't appear in the Macros dialog.

Thanks for your help. I'll test the macro and see what happens...

--
tb