Flag Data?

[Guest]

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.

 

[Guest]

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.

 

[Jim Cone]

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"
<[email protected]>
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.