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

[Jim Thomlinson]

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.
--
HTH...

Jim Thomlinson


"c2k2e" wrote:

>
> 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
>
>
> --
> c2k2e
> ------------------------------------------------------------------------
> c2k2e's Profile: http://www.excelforum.com/member.php...o&userid=25244
> View this thread: http://www.excelforum.com/showthread...hreadid=387363
>
>

[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


---
Regards,
Norman



"c2k2e" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> c2k2e
> ------------------------------------------------------------------------
> c2k2e's Profile:
> http://www.excelforum.com/member.php...o&userid=25244
> View this thread: http://www.excelforum.com/showthread...hreadid=387363
>

[Roy]

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


"c2k2e" wrote:

>
> 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
>
>
> --
> c2k2e
> ------------------------------------------------------------------------
> c2k2e's Profile: http://www.excelforum.com/member.php...o&userid=25244
> View this thread: http://www.excelforum.com/showthread...hreadid=387363
>
>

[Chris Ferguson]

Have you looked at vlookup?

Chris


"Roy" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "c2k2e" wrote:
>
>>
>> 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
>>
>>
>> --
>> c2k2e
>> ------------------------------------------------------------------------
>> c2k2e's Profile:
>> http://www.excelforum.com/member.php...o&userid=25244
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=387363
>>
>>

[Roy]

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

"Chris Ferguson" wrote:

> Have you looked at vlookup?
>
> Chris
>
>
> "Roy" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "c2k2e" wrote:
> >
> >>
> >> 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
> >>
> >>
> >> --
> >> c2k2e
> >> ------------------------------------------------------------------------
> >> c2k2e's Profile:
> >> http://www.excelforum.com/member.php...o&userid=25244
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=387363
> >>
> >>
>
>
>

[Roy]

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))

"Roy" wrote:

> 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
>
> "Chris Ferguson" wrote:
>
> > Have you looked at vlookup?
> >
> > Chris
> >
> >
> > "Roy" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> > > "c2k2e" wrote:
> > >
> > >>
> > >> 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
> > >>
> > >>
> > >> --
> > >> c2k2e
> > >> ------------------------------------------------------------------------
> > >> c2k2e's Profile:
> > >> http://www.excelforum.com/member.php...o&userid=25244
> > >> View this thread:
> > >> http://www.excelforum.com/showthread...hreadid=387363
> > >>
> > >>
> >
> >
> >

[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

Please Login or Register  to view this content.