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" wrote in message
...
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