Formula for identifying duplicate entries and comparing them?

Champers

New Member
Joined
Sep 22, 2008
Messages
2
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
<o:p></o:p>
Example:<o:p></o:p>
<o:p></o:p>
1) Naughty Boys’ School buy in Jan 2007 for the first time – this is New Business<o:p></o:p>
2) Naughty Boys’ School renew in Jan 2008 – this is retention business<o:p></o:p>
3) Naughty Boys’ School add 10 more students in March 2008 – this is retention business<o:p></o:p>
<o:p></o:p>
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:p></o:p>

=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

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
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.
 

Champers

New Member
Joined
Sep 22, 2008
Messages
2
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

New Member
Joined
Jun 12, 2007
Messages
15
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
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top