Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flag Data?
Let's see if I can explain this. I have the following rows and columns
Ship Date BOL# PO# 9/14/06 110011 44754 914/06 110011 45451 9/15/06 121114 14541 9/15/06 110011 22412 As you will notice, rows 1,2,and 4 have the same BOL# but row 4 has a different ship date than 1 and 2. I need to be able to identify when a single BOL# has multiple ship dates. Keep in mind that my spreadsheet could have over 1000 lines in it so sorting and looking for it isn't a reasonable option. I need to be able to either have the ship date change to the earliest of dates listed, or at the very least highlight the occurances. Is there anyway to do this? Thank you in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flag Data?
You could create a column on the right side of your workbook and concatenate
the ship date and BOL column =A1&B1. Then sort the table by the new column and subtotal using a count on the concatenated column. -- Kevin Backmann "Chuck Neal" wrote: Let's see if I can explain this. I have the following rows and columns Ship Date BOL# PO# 9/14/06 110011 44754 914/06 110011 45451 9/15/06 121114 14541 9/15/06 110011 22412 As you will notice, rows 1,2,and 4 have the same BOL# but row 4 has a different ship date than 1 and 2. I need to be able to identify when a single BOL# has multiple ship dates. Keep in mind that my spreadsheet could have over 1000 lines in it so sorting and looking for it isn't a reasonable option. I need to be able to either have the ship date change to the earliest of dates listed, or at the very least highlight the occurances. Is there anyway to do this? Thank you in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flag Data?
Sort the table by BOL (first) and ShipDate (both at the same time).
Then select those two columns and run this code... Sub HighlightLateDates() 'Jim Cone - San Francisco, USA - Sept. 2006 Dim rngAll As Excel.Range Dim rngCell As Excel.Range Set rngAll = Selection If rngAll.Columns.Count < 2 Then MsgBox "Select only the Date and BOL columns. " Exit Sub End If Set rngAll = Application.Intersect(Selection, ActiveSheet.UsedRange) For Each rngCell In rngAll.Columns(1).Cells If rngCell.Value < rngCell(0, 1).Value Or _ rngCell(0, 1).Interior.ColorIndex = 40 Then If rngCell(1, 2).Value = rngCell(0, 2).Value Then rngCell.Interior.ColorIndex = 40 End If End If Next 'rngCell End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Chuck Neal" wrote in message Let's see if I can explain this. I have the following rows and columns Ship Date BOL# PO# 9/14/06 110011 44754 914/06 110011 45451 9/15/06 121114 14541 9/15/06 110011 22412 As you will notice, rows 1,2,and 4 have the same BOL# but row 4 has a different ship date than 1 and 2. I need to be able to identify when a single BOL# has multiple ship dates. Keep in mind that my spreadsheet could have over 1000 lines in it so sorting and looking for it isn't a reasonable option. I need to be able to either have the ship date change to the earliest of dates listed, or at the very least highlight the occurances. Is there anyway to do this? Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |