Sort columns A & B into Column C

M

Marklarbear

Board Regular
Joined
Nov 6, 2003
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Excel 2000
Windows XP Pro

I have 2 columns of data (A & B) and I need to sort the results into column C. I can't get my head around it as not all cells in column A or B will have data in it:
Excel Workbook
ABC
111:45 - 8:0010:00 - 6:15
28:30 - 4:458:30 - 4:4510:00 - 6:15
38:00 - 1:008:00 - 1:0011:00 - 7:15
49:15 - 5:3011:45 - 8:00
511:00 - 7:158:00 - 1:00
68:00 - 1:00
78:00 - 4:15
88:00 - 4:15
910:00 - 6:1510:00 - 6:158:30 - 4:45
108:00 - 4:158:00 - 4:158:30 - 4:45
119:15 - 5:30
Sheet1
Excel 2000


Any help on a formula that will do this automatically will be greatly appreciated.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Not a formula because formulae can't move data around, but you can do this with VBA.

Would that be an acceptable solution?
 
Upvote 0
Something along these lines:-
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]  Public Sub SortAandBtoC()[/FONT]
 
[FONT=Fixedsys]  Dim iLast As Long[/FONT]
[FONT=Fixedsys]  Dim iRow As Long[/FONT]
[FONT=Fixedsys]  Dim iOut As Long[/FONT]
[FONT=Fixedsys]  Dim ws As Worksheet[/FONT]
 
[FONT=Fixedsys]  Set ws = [/FONT][COLOR=red][FONT=Fixedsys]ThisWorkbook.Sheets(1)    [COLOR=green]' sheet to be sorted[/COLOR][/FONT]
[/COLOR][FONT=Fixedsys]  ws.Columns("C").ClearContents[/FONT]
[FONT=Fixedsys]  iOut = 0[/FONT]
 
[FONT=Fixedsys]  iLast = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row[/FONT]
[FONT=Fixedsys]  For iRow = 1 To iLast[/FONT]
[FONT=Fixedsys]    If Not IsEmpty(ws.Cells(iRow, 1)) Then[/FONT]
[FONT=Fixedsys]      iOut = iOut + 1[/FONT]
[FONT=Fixedsys]      ws.Cells(iRow, 1).Copy Destination:=ws.Cells(iOut, 3)[/FONT]
[FONT=Fixedsys]      End If[/FONT]
[FONT=Fixedsys]  Next iRow[/FONT]
 
[FONT=Fixedsys]  iLast = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row[/FONT]
[FONT=Fixedsys]  For iRow = 1 To iLast[/FONT]
[FONT=Fixedsys]    If Not IsEmpty(ws.Cells(iRow, 2)) Then[/FONT]
[FONT=Fixedsys]      iOut = iOut + 1[/FONT]
[FONT=Fixedsys]      ws.Cells(iRow, 2).Copy Destination:=ws.Cells(iOut, 3)[/FONT]
[FONT=Fixedsys]    End If[/FONT]
[FONT=Fixedsys]  Next iRow[/FONT]
 
[FONT=Fixedsys]  With ws.Sort[/FONT]
[FONT=Fixedsys]    .SortFields.Clear[/FONT]
[FONT=Fixedsys]    .SortFields.Add Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, _[/FONT]
[FONT=Fixedsys]         DataOption:=xlSortTextAsNumbers[/FONT]
[FONT=Fixedsys]    .SetRange Range("C1:C" & iOut)[/FONT]
[FONT=Fixedsys]    .Header = xlNo[/FONT]
[FONT=Fixedsys]    .MatchCase = False[/FONT]
[FONT=Fixedsys]    .Orientation = xlTopToBottom[/FONT]
[FONT=Fixedsys]    .SortMethod = xlPinYin[/FONT]
[FONT=Fixedsys]    .Apply[/FONT]
[FONT=Fixedsys]  End With[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]
 
Last edited:
Upvote 0
close ... however i have data covering multiple ranges on the sheet ie A54:C71 and A74:C90

as i'm not that too familiar with VBA, can the code you suplied be modified to cover a specific range - say A54:C71 ?
 
Upvote 0
To be fair, that's not what you said in your original post!

The code can be made to do whatever you wish.

So do you want to sort A54:B71 into C54:C71? What if it doesn't fit into that area?

Or do you want to sort A54:C71 into another area? If so, where?

Are you saying you want those multiple ranges to be combined in some way?
 
Upvote 0
My apologies, in error i wasn't being too specific. The actual ranges I am working with are as follows:
A55:B71 to be sorted into C55:C71 and another range of A75:B90 to be sorted into C75:C90

I have data that populates some cells in column A and some in column B - but the total data will fit into the column C range. Currently i manually copy and paste the data from columns A and B into column C.
 
Upvote 0
Try this:-
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub SortAandBtoC_v2()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim iRow As Long
  Dim iOut As Long
  Dim ws As Worksheet[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Set ws = ThisWorkbook.Sheets(1)
  ws.Columns("C").ClearContents
  
  iOut = 55
  For iRow = iOut To 71
    If Not IsEmpty(ws.Cells(iRow, "A")) Then
      ws.Cells(iRow, "A").Copy Destination:=ws.Cells(iOut, "C")
      iOut = iOut + 1
    End If
    If Not IsEmpty(ws.Cells(iRow, "B")) Then
      ws.Cells(iRow, "B").Copy Destination:=ws.Cells(iOut, "C")
      iOut = iOut + 1
    End If
  Next iRow[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  With ws.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("C55"), SortOn:=xlSortOnValues, Order:=xlAscending, _
      DataOption:=xlSortTextAsNumbers
    .SetRange Range("C55:C71")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  iOut = 75
  For iRow = iOut To 90
    If Not IsEmpty(ws.Cells(iRow, "A")) Then
      ws.Cells(iRow, "A").Copy Destination:=ws.Cells(iOut, "C")
      iOut = iOut + 1
    End If
    If Not IsEmpty(ws.Cells(iRow, "B")) Then
      ws.Cells(iRow, "B").Copy Destination:=ws.Cells(iOut, "C")
      iOut = iOut + 1
    End If
  Next iRow[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  With ws.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("C75"), SortOn:=xlSortOnValues, Order:=xlAscending, _
      DataOption:=xlSortTextAsNumbers
    .SetRange Range("C75:C90")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With[/FONT]
[FONT=Fixedsys]End Sub
[/FONT]
The values in the cells are regarded by Excel as characters strings because stricltly speaking they're not valid times, so the sort process sorts them as string: 10:00 appears before 9:00 because "1" comes before "9".

If this isn't acceptable, let me know and I'll do something about it.
 
Upvote 0
Actually it was easier than I thought. This sorts column C into actual time (start time) order:-
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub SortAandBtoC_v3()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim iRow As Long
  Dim iOut As Long
  Dim ws As Worksheet
  Dim i As Integer
  Dim j As Integer
  Dim dtTemp As Date
  Dim strTemp As String
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.Columns("C").ClearContents
  
  ReDim dtArray(55 To 71)
  iOut = 55
  For iRow = iOut To 71
    If Not IsEmpty(ws.Cells(iRow, "A")) Then
      ws.Cells(iRow, "A").Copy Destination:=ws.Cells(iOut, "C")
      dtArray(iOut) = TimeValue(Left(ws.Cells(iOut, "C").Text, InStr(ws.Cells(iOut, "C").Text, " ") - 1))
      iOut = iOut + 1
    End If
    If Not IsEmpty(ws.Cells(iRow, "B")) Then
      ws.Cells(iRow, "B").Copy Destination:=ws.Cells(iOut, "C")
      dtArray(iOut) = TimeValue(Left(ws.Cells(iOut, "C").Text, InStr(ws.Cells(iOut, "C").Text, " ") - 1))
      iOut = iOut + 1
    End If
  Next iRow[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  For i = LBound(dtArray) To UBound(dtArray) - 1
    For j = i + 1 To UBound(dtArray)
      If dtArray(i) > dtArray(j) Then
        dtTemp = dtArray(i)
        dtArray(i) = dtArray(j)
        dtArray(j) = dtTemp
        strTemp = ws.Cells(i, "C").Text
        ws.Cells(i, "C") = ws.Cells(j, "C").Text
        ws.Cells(j, "C") = strTemp
      End If
    Next j
  Next i
  
  ReDim dtArray(75 To 90)
  iOut = 75
  For iRow = iOut To 90
    If Not IsEmpty(ws.Cells(iRow, "A")) Then
      ws.Cells(iRow, "A").Copy Destination:=ws.Cells(iOut, "C")
      dtArray(iOut) = TimeValue(Left(ws.Cells(iOut, "C").Text, InStr(ws.Cells(iOut, "C").Text, " ") - 1))
      iOut = iOut + 1
    End If
    If Not IsEmpty(ws.Cells(iRow, "B")) Then
      ws.Cells(iRow, "B").Copy Destination:=ws.Cells(iOut, "C")
      dtArray(iOut) = TimeValue(Left(ws.Cells(iOut, "C").Text, InStr(ws.Cells(iOut, "C").Text, " ") - 1))
      iOut = iOut + 1
    End If
  Next iRow
  
  For i = LBound(dtArray) To UBound(dtArray) - 1
    For j = i + 1 To UBound(dtArray)
      If dtArray(i) > dtArray(j) Then
        dtTemp = dtArray(i)
        dtArray(i) = dtArray(j)
        dtArray(j) = dtTemp
        strTemp = ws.Cells(i, "C").Text
        ws.Cells(i, "C") = ws.Cells(j, "C").Text
        ws.Cells(j, "C") = strTemp
      End If
    Next j
  Next i[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub
[/FONT]
See if that's any better.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Delete all spaces in columns "A", "B" and "C".
Replies
6
Views
343
rlv01
R
M
  • Solved
How to merge files?
Replies
2
Views
387
mrdigitalis
M
K
  • Solved
Populate summary by sorting data for customer for each sheet alone based on dates
Replies
4
Views
174
KalilMe
K
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
585
harzer
H
H
  • Question Question
Insert the space character in the penultimate place of all the cells of columns "A" - "B" & "C".
Replies
3
Views
361
Fluff
Fluff

Forum statistics

Threads
1,224,598
Messages
6,179,814
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top