1

Well the scenario is a excel sheet contain 97K rows with similar email address I want to compare the alternate cells in an excel sheet and get unique email address . Suppose their are 4 columns

  1. A contains Name
  2. B contains Email Address
  3. C contains type
  4. D contains the latest updated date time

but the conditions are :-

  1. if B contain any 2 or 3 cells having same email address then go to the next column i.e C
  2. in column C check for the type
  3. if all the people contain type as LEAD/CONTACT then go for the column C and check for latest update and get that row.
  4. if the people with matching emails contain type one as LEAD and another as CONTACT then get the row which have type as CONTACT
  5. if the type is Blank then get those row which contain any type either LEAD or CONTACT.

So can anyone help me out , how quickly this can be done? Right now i am manually doing it one by one. A short or snippet approach is appreciable. Thank you.It would be a great help enter image description here

Improve this question
3
  • do you have to do this multiple times that you are asking for VBA? If no why dont you sort the data firstly for the emails and secondly by the type. There is also a function implemented in the ribbon for deleting doubled data (although I'm not sure on what criteria exactly the data gets deleted). If you'd really want to do it with VBA you'll probably have to start somewhere, why don't you start with comparing one cell to another. :) Commented Jan 28, 2016 at 13:55
  • comparing cells can be easily done with "If Cells(A1).value = Cells(b1).value Then". You can also work with Cells(Row, Col) for the coordinates of a single cell. For Row and Col you could make a For-Loop which will then lead to the cells ranges being dynamic. Commented Jan 28, 2016 at 13:59
  • yes.. their are 96K records and almost every other record is having duplicate emails. Commented Jan 28, 2016 at 16:33

1 Answer 1

Reset to default
3

Sort the Data in the following order:

  1. Email - Ascending
  2. Updated At - Descending
  3. Type - Ascending

Then in Column E write the following formula: =IF(B1<>B2,"Keep","")

Then filter the data by blanks in Column D, then delete the filtered rows.

Picture with sample data based on your data below. enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@MiliBiswas - did you read this line in my answer -> Then filter the data by blanks in Column D, then delete the filtered rows.
for each cell in column D.. will i have to repeat the formule? isnt there a way to get in one click?
@MiliBiswas - just drag the formula for column D. Or double-click the lower-right-hand corner of D2 when the thin cross appears, and it will auto-fill the formula down the row.
hi @scott , thankyou for the help, but i think the sorting order is not same as what you did .. I first sort the EMAIL then Updated at and then TYPE .. all the email of alternative cell are different then, so eventually the formula gives KEEP for all.. now what to do? Is this correct way..?
@MiliBiswas did you sort them in the Ascending and Descending order as I have outlined in the answer. Also, please make sure the formula is just as I have written in the answer as well.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.