I am working with an excel spreadsheet of traffic counter data for three lanes of traffic in a street. The lanes are coded as "SB1", "SB2", and "SB3," and are all contained in one column. Unfortunately, the timestamps for these transmissions are not coded as either AM or PM. There are two identical timestamps for each lane, but they are definitely different datapoints. They are in such a consistency that I can create an automatic list that reads "AM" and then "PM" in every other cell.

To ensure optimal accuracy, I wish to run such a list when my workbook is filtered for one of the three lanes (SB1, SB2, and SB3). That is, I want "AM" for every other row that contains "SB1", and "PM" for all remaining rows that contain "SB1."

Does Excel have that capability? Do I have to write a macro to do this?

Attached Files

traffic.xlsx (15.2 KB, 11 views)