Comparing Rows of Duplicate Records To Add a Value

[HWmR]

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

 

[Peter_SSs]

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
	A	B	C	D	E	F
1	Lname	Address	Type	Sales	Date Sold	Mail
2	SORGATZ	0N417	ARMSTRONG LN 0	260,485	20/03/2000	D
3	SORGATZ	0N417	ARMSTRONG LN 0	360,000	09/03/2000	M
4	WODZISZ	0N422	SULLEY PL 0	80,000	09/07/2001	M
5	WILSON	0N425	SULLEY PL 0	21,721	03/08/1999	D
6	WILSON	0N425	SULLEY PL 0	32,081	08/08/2000	M
Mail

 

[HWmR]

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.

 

[Peter_SSs]

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
	A	B	C	D	E	F
11	Lname	Address	Type	Sales	Date Sold	Mail
12	WILSON	0N425	SULLEY PL 0	1	01/01/2000
13	WILSON	0N425	SULLEY PL 0	9	01/03/2000
14	WILSON	0N425	SULLEY PL 0	5	01/05/2000
15	WILSON	0N425	SULLEY PL 0	7	01/07/2000
16	WILSON	0N425	SULLEY PL 0	2	01/09/2000
17	WILSON	0N425	SULLEY PL 0	6	01/11/2000
Mail (2)