Formula for identifying duplicate entries and comparing them?

[Champers]

Okay, this one I can’t figure out at all. I need to identify customers in a list that have more than one entry (i.e. sold to more than once – resales/upsales etc.) and then I need excel to compare those multiple entries and identify the earliest invoice date. Any ideas?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The purpose of this is to categorise invoices as either ‘new business’ or ‘retention business’.

I’ve got far enough to work out which ones we’ve resold to but the very first sale to that customer doesn’t count as a renewal so I need excel to work out which one is the first invoice so I can add that to the new business totals. This all needs to feed into a pivot table to show how close the retention team are to target and how close the sales people are.<o></o>
<o></o>
Example:<o></o>
<o></o>
1) Naughty Boys’ School buy in Jan 2007 for the first time – this is New Business<o></o>
2) Naughty Boys’ School renew in Jan 2008 – this is retention business<o></o>
3) Naughty Boys’ School add 10 more students in March 2008 – this is retention business<o></o>
<o></o>
Entry 1) will go into one pot of money and entries 2) and 3) into another. Each pot will have its own pivot table for tracking targets.<o></o>

=COUNTIF(D192:D1630,D221) is the formula I already have in the frequency column, that just shows how many entries there are with multiple purchases. So I can see that all those with a ‘1’ are New Business but that doesn’t tell me which invoice of the repeat customers was the first one.

Any help at all would be greatly appreciated! Thanks...

 

[cornflakegirl]

Welcome to the board.

In E192 (or whichever cell you're using) put
=COUNTIF($D$192:D192,D192)
and copy down. This counts how many times the client has appeared so far in the list - so for repeat customers you'll get a 1 against their first transaction.

 

[Stormseed]

hi Champers, welcome to MrExcel

have a look at these links:

• http://support.microsoft.com/kb/291320
• http://office.microsoft.com/en-us/excel/HA011366161033.aspx
• http://www.mrexcel.com/archive/Formatting/23124.html
• http://www.exceltip.com/st/Identifying_Duplicate_Rows_within_a_Range/1090.html

 

[Champers]

Hey there, thanks for the welcomes and the help!

I can identify the duplicates no problem but it all comes unstuck when I try to identify the earliest invoice. This is because the first entry on the spreadsheet will not always be the earliest invoice date. Is there a way to cross reference with the invoice date column once the repeat customers have been identified?

The workbook is shared and is not always sorted in ascending date order. The invoice amounts are rarely the same for repeat purchases so it needs to be a specific entry that is labelled as the 'new business'.

 

[excpp]

Hi
Thanks for posting the link to microsoft.

The script compares two columns and deletes the duplicates in the second list, which is what I need.

However this script is for 1 column - I have a few columns per record.
e.g. Name | Company | Email

I will be comparing the 3 columns to 3 columns

I need to make sure that it only deletes records after comparing the full 3 columns in the record from Sheet 1 to Sheet 2.

e.g.
Sheet 1
Bob | Amex | bob@amex.com

Sheet 2
Bob | Amex | bob@amex.com <-------------this record should be deleted
Bob | Amex | bobby@amex.com <-----------this record should not be not deleted


Can you tell me how I can change this to delete the entire row in Sheet 2?
Thanks

---------------------------------

that shows this script:
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub