Highlighting non-matching ZIP in a batch
 

Hi

I work for a transport company and receive a report daily that shows
multi-part consignments by postcode and which van the items are loaded
on. Most of the parcels for a consignment will be on the same van but
ocassionally, one of them might be on a different vehicle.

For example, take these three columns -

ZIP CODE VAN DELIVERY DEPOT
HG5 9LZ 201 York Depot
HG5 9LZ 201 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
M30 7PJ 116 Manchester Depot
M30 7PJ 201 Manchester Depot
M30 7PJ 116 Manchester Depot
M30 7PJ 116 Manchester Depot
M30 7PJ 116 Manchester Depot
LS28 5PX 226 Leeds Depot
LS28 5PX 226 Leeds Depot
LS28 5PX 226 Leeds Depot

The postcode / zip code for each part of the consignment match the
vehicle number. As you can see from the above example, one parcel
going to M30 116 is on a different van.

Is there a quick way to highlight a batch of parcels where the van
number does not match the van number of other parcels going to the same
zip code? This report contains several thousand rows and I need to
check it visually every morning.

Any help would be appreciated,

Scott


--
Scotty_MK
------------------------------------------------------------------------
Scotty_MK's Profile: http://www.excelforum.com/member.php...o&userid=21873
View this thread: http://www.excelforum.com/showthread...hreadid=570055

Highlighting non-matching ZIP in a batch
 

If Van is column B, and Deleviery Depot is in Column C, you could create a
helper column in Column D (or any open column) and type this formula in the
cell of the 2nd row of data of your helper column. In your example that
would be the 2nd line containing 201. Then drag this cell to fill all the
cells in that column until you reach the end of the list.

=IF((C2=C3)*AND(B3<B2),"Warning","OK")

I assume a header row, which mean the data starts in row 2. So for my
example, I type the formula in D3 and copy it down. It will look at the
Depot for the row above it(C2), and see if it is the same as the depot of
it's row(C3). If it is the same, D2 will have an "OK" in D3. If it is not
the same it will display "Warning" in D3.

Hope this helps. Let us know.

"Scotty_MK" wrote:


Hi

I work for a transport company and receive a report daily that shows
multi-part consignments by postcode and which van the items are loaded
on. Most of the parcels for a consignment will be on the same van but
ocassionally, one of them might be on a different vehicle.

For example, take these three columns -

ZIP CODE VAN DELIVERY DEPOT
HG5 9LZ 201 York Depot
HG5 9LZ 201 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
HU7 3JD 124 York Depot
M30 7PJ 116 Manchester Depot
M30 7PJ 201 Manchester Depot
M30 7PJ 116 Manchester Depot
M30 7PJ 116 Manchester Depot
M30 7PJ 116 Manchester Depot
LS28 5PX 226 Leeds Depot
LS28 5PX 226 Leeds Depot
LS28 5PX 226 Leeds Depot

The postcode / zip code for each part of the consignment match the
vehicle number. As you can see from the above example, one parcel
going to M30 116 is on a different van.

Is there a quick way to highlight a batch of parcels where the van
number does not match the van number of other parcels going to the same
zip code? This report contains several thousand rows and I need to
check it visually every morning.

Any help would be appreciated,

Scott


--
Scotty_MK
------------------------------------------------------------------------
Scotty_MK's Profile: http://www.excelforum.com/member.php...o&userid=21873
View this thread: http://www.excelforum.com/showthread...hreadid=570055

Highlighting non-matching ZIP in a batch
 

Wiley

Superb! Many thanks, this did the trick nicely.

Have a good day.

Scott


--
Scotty_MK
------------------------------------------------------------------------
Scotty_MK's Profile: http://www.excelforum.com/member.php...o&userid=21873
View this thread: http://www.excelforum.com/showthread...hreadid=570055

Highlighting non-matching ZIP in a batch
 

Glad I could help. Thanks for the feedback.

"Scotty_MK" wrote:


Wiley

Superb! Many thanks, this did the trick nicely.

Have a good day.

Scott


--
Scotty_MK
------------------------------------------------------------------------
Scotty_MK's Profile: http://www.excelforum.com/member.php...o&userid=21873
View this thread: http://www.excelforum.com/showthread...hreadid=570055