Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 -- c2k2e ------------------------------------------------------------------------ c2k2e's Profile: http://www.excelforum.com/member.php...o&userid=25244 View this thread: http://www.excelforum.com/showthread...hreadid=387363 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Column B against Column A
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 -------------------- -- jindon ------------------------------------------------------------------------ jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135 View this thread: http://www.excelforum.com/showthread...hreadid=387363 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Sort #'s in Column B,C&D in the order from Column A | Excel Worksheet Functions | |||
2 columns have same data:Sort column E, but column A changes as we | Excel Worksheet Functions | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
HOW TO SORT A COLUMN THE SAME AS ANOTHER COLUMN WITH SIMILAR CONT. | Excel Worksheet Functions |