# Conditional Formatting?

#### faeryluv

##### New Member
Hi, everyone! I have what I think is an easy question. I have a spreadsheet where column A is route numbers, and column B is item numbers. I need to highlight rows that have the identical route number and item number.

Example
Row 1: route 1 item D4
Row 2: route 1 item C3
Row 3: route 1 item D4

I need row 1 and row 3 to be highlighted. I have 4,217 rows.

Thanks in advance!

#### Fluff

##### MrExcel MVP, Moderator
Assuming your data starts in A2, select A2:B last row & use this formula
=COUNTIFS(\$A:\$A,\$A2,\$B:\$B,\$B2)>1

#### faeryluv

##### New Member
Assuming your data starts in A2, select A2:B last row & use this formula
=COUNTIFS(\$A:\$A,\$A2,\$B:\$B,\$B2)>1
Thank you! Does that go under Conditional Formatting >> add new rule?

#### faeryluv

##### New Member
Another question, if you don't mind...

I'm using the Subtotal function, and that works good for what I'm doing, however, I don't need all the extra rows.

Without the Subtotal function, I'm manually adding up the duplicate orders and then deleting the extra rows.

Example
Row 1: route 1 item D4 order 50
Row 2: route 1 item D4 order 25

In this case, I manually replace "50" with "75", then I manually delete Row 2. I have thousands like that so it takes hours to do. I need a faster way.

Any advice is much appreciated!!

#### Fluff

##### MrExcel MVP, Moderator
Thank you! Does that go under Conditional Formatting >> add new rule?
Yes it does.

You will need to start a new thread for the other question.

#### faeryluv

##### New Member
Assuming your data starts in A2, select A2:B last row & use this formula
=COUNTIFS(\$A:\$A,\$A2,\$B:\$B,\$B2)>1
Yes it does.

You will need to start a new thread for the other question.
Will do!! Thank you!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Threads
1,085,099
Messages
5,381,709
Members
401,751
Latest member
bschiebe