Sort Column B against Column A

[c2k2e]

I would appreciate any help with the following. I need to sort range
B1:F96 against range A1:A96.

Range A1:A96 is a timeline by 15 minute intervals (00:00, 00:15, ... ,
23:30, 23:45) for a 24 hour period.

Range B1:F96 contains 5 columns of data, column B containing a timeline
similar to column A, however it does not have an entry for every 15
minute interval.

I need to sort B1:F96 to coincide with the timeline in A1:A96 (which
will space out the B1:F96 range along the 15 minute intervals in
A1:A96, leaving blanks where there is no data.

Example of original (ignore dashes, when posted it changed spacing):

--A--------B-------C-----D-----E-----F
0:00 --- 0:15 data.......................
0:15 --- 1:45 data.......................
0:30 --- 2:00 data.......................
0:45 --- 2:45 data.......................
1:00
1:15
1:30
1:45
2:00
2:15
2:30
2:45
...
23:30
23:45

Desired output:

--A--------B-------C-----D-----E-----F
0:00 --- 0:15 data.......................
0:15
0:30
0:45
1:00
1:15
1:30
1:45 --- 1:45 data.......................
2:00 --- 2:00 data.......................
2:15
2:30
2:45 --- 2:45 data.......................
...
23:30
23:45

Basically to match column B values with column the A counterparts.

Any ideas?

Thanks!
Chris

 

[Guest]

I would do that with a pivot table. Group on the Time and set your group by
interval.at 15 minutes. In the output ensure taht you set the time dimension
to show all. If you need more help with this just let me know.

 

[c2k2e]

Thanks for your response, I'm not quite sure how to set a pivot table up
though. I went through the wizard and it did not seem to have what I
was looking for.

I will need to copy/paste new information continually into this
spreadsheet to be formatted along the timeline, then pasted into a
different one (basically using it to format data to be put into a
master spreadsheet). Will a pivot table need to be set up each time?
This is why I thought a macro would work better since the data will be
coming and going once formatted.

Thanks!
Chris

 

[Norman Jones]

Hi Chris

Thanks for your response, I'm not quite sure how to set a pivot table up
though.

See Jon Peltier's intoduction to pivot tables at:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

See Debra Dalgleish's pivot table tutorials (under 'P') at:

http://www.contextures.com/tiptech.html

See Microsoft's pivot table tutorial and articles at:

http://support.microsoft.com/xlw2kpt

 

[Guest]

Chris,

Test this with sample data to make sure it does what you want. Put a button
on the sheet and paste this code into the click event for the button or
create an empty macro and paste the code there, it operates either way. You
might have to move the comments around if pasting doesn't maintain the
original format. If will not move data into an occupied cell. The algorithym
assumes that a time in column B is never in a higher row number than it's
corresponding time in col A, but is either the same row or lower. If this is
not true, an overwrite situation would occur and the procedure would abort
without overwriting. (i.e. Col B 7:15 would never exceed row 30.) If the
assumption is not correct, the data simply needs to be moved to a temporary
location and then sorted back into B1-F96.

Roy



Dim x, ScanRowA, ScanRowB, ColumnB2F As Integer
For ScanRowB = 96 To 1 Step -1 'B column bottom to top scan loop
If Cells(ScanRowB, 2).Value <> "" Then 'found some data in column b
If Cells(ScanRowB, 2).Value = Cells(ScanRowB, 1).Value Then 'it's a
match, do nothing
Else 'find the match in col A
For ScanRowA = 96 To 1 Step -1 'scan col A to find the match
If Cells(ScanRowB, 2).Value = Cells(ScanRowA, 1).Value Then
'match found
For ColumnB2F = 2 To 6 'lateral scan of cols b-f
If Cells(ScanRowA, ColumnB2F).Value <> "" Then
'destination occupied (bad)
x = MsgBox("The destination row was occupied.
Aborting procedure.")
Exit Sub
Else 'ok to move data
Cells(ScanRowA, ColumnB2F).Value =
Cells(ScanRowB, ColumnB2F).Value 'relocate data
Cells(ScanRowB, ColumnB2F).Value = "" 'clear old
cell
End If
Next
Exit For
Else 'do nothing
End If
Next
End If
End If
Next

 

[Chris Ferguson]

Have you looked at vlookup?

Chris

Chris,

Test this with sample data to make sure it does what you want. Put a
button
on the sheet and paste this code into the click event for the button or
create an empty macro and paste the code there, it operates either way.
You
might have to move the comments around if pasting doesn't maintain the
original format. If will not move data into an occupied cell. The
algorithym
assumes that a time in column B is never in a higher row number than it's
corresponding time in col A, but is either the same row or lower. If this
is
not true, an overwrite situation would occur and the procedure would abort
without overwriting. (i.e. Col B 7:15 would never exceed row 30.) If the
assumption is not correct, the data simply needs to be moved to a
temporary
location and then sorted back into B1-F96.

Roy



Dim x, ScanRowA, ScanRowB, ColumnB2F As Integer
For ScanRowB = 96 To 1 Step -1 'B column bottom to top scan loop
If Cells(ScanRowB, 2).Value <> "" Then 'found some data in column b
If Cells(ScanRowB, 2).Value = Cells(ScanRowB, 1).Value Then 'it's a
match, do nothing
Else 'find the match in col A
For ScanRowA = 96 To 1 Step -1 'scan col A to find the match
If Cells(ScanRowB, 2).Value = Cells(ScanRowA, 1).Value Then
'match found
For ColumnB2F = 2 To 6 'lateral scan of cols b-f
If Cells(ScanRowA, ColumnB2F).Value <> "" Then
'destination occupied (bad)
x = MsgBox("The destination row was occupied.
Aborting procedure.")
Exit Sub
Else 'ok to move data
Cells(ScanRowA, ColumnB2F).Value =
Cells(ScanRowB, ColumnB2F).Value 'relocate data
Cells(ScanRowB, ColumnB2F).Value = "" 'clear
old
cell
End If
Next
Exit For
Else 'do nothing
End If
Next
End If
End If
Next

 

[Guest]

Chris,

I agree, Vlookup would work. If you move your raw data to sheet 2 for
example in the same B1:F96 area, you could put this formula (below) on your
display area, presumably sheet1 B1:F96. Plug it in B1 and do an edit fill
down, then select B1:B96 and do an edit fill right to column F. Copy your
data to the mastersheet as before except use paste special - values. The IF
statement suppresses the error messages when there is no match.

=IF(ISNA(VLOOKUP(A1,Sheet2!$B$1:$F$96,1,FALSE)),"",VLOOKUP(A1,Sheet2!$B$1:$F$96,1,FALSE))

Roy

 

[Guest]

oops,

Forgot a couple of things. The first argument of the vlookup needs to be a
half absolute reference so you can do the fill right and maintain the A
column lookup. See corrected formula below.

Put this formula in B1, do an edit fill RIGHT to column F. Change the
vlookups 3rd argument from 1 to 2, 3, 4 or 5 in the formulas in cells C1, D1,
E1 and F1 respectively, then select B1:F1, do an edit fill down to row 96.

=IF(ISNA(VLOOKUP($A1,Sheet2!$B$1:$F$96,1,FALSE)),"",VLOOKUP($A1,Sheet2!$B$1:$F$96,1,FALSE))

 

[c2k2e]

Thank you all for your time and help. One of my co-workers wrote a
VLOOKUP formula for me (thank Chris and Roy). And Roy, your formula
helped him get the NA's to be hidden. I still may pursue the macro as
well, it would be easier to use going forward.

Thanks again everyone!
Chris

 

[jindon]

try the code

Code:
--------------------

Sub test()
Dim a, b, i, ii
Dim rng1 As Range, rng2 As Range
Set rng1 = Range("a1", Range("a65536").End(xlUp))
Set rng2 = Range("b1", Range("b65536").End(xlUp))
ReDim a(1 To rng1.Count, 1 To 2): ReDim b(1 To rng2.Count, 1 To 2)
For i = 1 To rng1.Count
a(i, 1) = rng1.Item(i).Text
Next
For i = 1 To rng2.Count
x = InStr(rng2.Item(i), Chr(32))
b(i, 1) = rng2.Item(i)
If x > 0 Then b(i, 2) = Left(rng2.Item(i), x - 1)
Next
For i = LBound(b, 1) To UBound(b, 1)
If Not IsEmpty(b(i, 1)) Then
For ii = LBound(a, 1) To UBound(a, 1)
If b(i, 2) = a(ii, 1) Then a(ii, 2) = b(i, 1)
Next
End If
Next
Range("a1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub