# Comparing Rows of Duplicate Records To Add a Value

#### HWmR

##### New Member
Please identify if a formula in "Mail" is possible after looking at a sorted address and checking for unique Lname, highest sales and latest date. Or, do I just use a de-dupe utility ? Thanks - H.

Logic: record 1 & 2 duplicate - same last name and address =
Take record 2 becasue the sales are higher and date sold is most current.

Perform this chjecking for each and next record. Results would be to mail "M" the 3 records in a mail-merge letter.

BEFORE

Lname Address Type Sales Date Sold Mail
1SORGATZ 0N417 ARMSTRONG LN 0 260,485 3/20/2000
2SORGATZ 0N417 ARMSTRONG LN 0 360,000 3/9/2005
3WODZISZ 0N422 SULLEY PL 0 80,000 7/9/2001
4WILSON 0N425 SULLEY PL 0 21,721 8/3/1999
5WILSON 0N425 SULLEY PL 0 32,081 8/8/2000

AFTER
1SORGATZ 0N417 ARMSTRONG LN 0 260,485 3/20/2000 D
2SORGATZ 0N417 ARMSTRONG LN 0 360,000 3/9/2005 M
3WODZISZ 0N422 SULLEY PL 0 80,000 7/9/2001 M
4WILSON 0N425 SULLEY PL 0 21,721 8/3/1999 M
5WILSON 0N425 SULLEY PL 0 32,081 8/8/2000 D

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
HWmR

Welcome to the Mr Excel board!

Take record 2 becasue the sales are higher and date sold is most current.
1. What if sales are higher but date sold is older?
2. You don't seem to have applied this logic to records 4 & 5 where you have put an 'M' beside the lower sales and older date.
3. Not quite sure about lining up your data with headings. Have I got it right in the sheet below?
4. Following on from point 3, itreally does help to show a small sample of your worksheet. To do so, see the link at the bottom of this page to Colo's HTML Maker. Also read the ‘Sticky’ titled: 'HTML Maker FAQ (How to show your sheet on the board)' which is at the top of this ‘Excel Questions’ forum. Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.

To get the ball rolling, I have taken a bit of a guess and assumed that if the sales are higher (cumulative sales?) then the date will be more recent. That is, if the name and address are the same, or the record is unique, this formula will put an 'M' on that row.

Note that I have changed the date format to Australian format, but that has no bearing on the formula.

The formula in F2 (copied down) is:
=IF(SUMPRODUCT(LARGE((\$A\$2:\$A\$6&\$B\$2:\$B\$6=A2&B2)*(\$D\$2:\$D\$6),1))=D2,"M","D")
Mr Excel.xls
ABCDEF
2SORGATZ0N417ARMSTRONG LN 0260,48520/03/2000D
3SORGATZ0N417ARMSTRONG LN 0360,00009/03/2000M
4WODZISZ0N422SULLEY PL 080,00009/07/2001M
5WILSON0N425SULLEY PL 021,72103/08/1999D
6WILSON0N425SULLEY PL 032,08108/08/2000M
Mail

Thank You Peter -

You really gave me a GREAT headstart. I applied the formula to a sample of 8,000 records in my spreadsheet and the sensitivities of the data reveal the following.

1. Can the assignment of the "M" be conditional. It should be assigned to the highest sale only if the other sale (lowest) is within 6 mos. Said another way, the rule for assigning M is Highest sale or lower sale because lower sale record is 6 months newer than the highest sales date.

Therefore, the M goes to the record that is at least 6 months newer than a highest sales. The M can be a lower sale and newer record.

2. Where sales \$\$ were equal amounts M should be assigned to the newest date.

I will send a sample if it would help, but sis not want to slow down the creative juices for you, waiting for me to learn to post properly to the forum. Thanks. - H.

HWmR

I must admit, I'm struggling to get a good handle on the requirements and how best to attack it.

Which row would get the "M" in the sample below?

Row 13 is the highest sale, but row 17 is more than six months 'newer'. On that basis I think you would use row 17.

However, row 15 has higher sales than row 17 and is not more than six months older. On that basis, maybe you would use row 15?

(I did get your PM which was the same as your last post here. Best to keep the thread going in the public arena so that other people that may have better ideas, or can see flaws in the approach taken so far, can join in.)
Mr Excel.xls
ABCDEF
12WILSON0N425SULLEY PL 0101/01/2000
13WILSON0N425SULLEY PL 0901/03/2000
14WILSON0N425SULLEY PL 0501/05/2000
15WILSON0N425SULLEY PL 0701/07/2000
16WILSON0N425SULLEY PL 0201/09/2000
17WILSON0N425SULLEY PL 0601/11/2000
Mail (2)

Replies
5
Views
264
Replies
7
Views
542
Replies
0
Views
319
Replies
0
Views
285
Replies
2
Views
895

1,203,561
Messages
6,056,082
Members
444,844
Latest member
Taps07

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back