Identify duplicates and keep the latest based on latest

[mils]

Hi,

I'm trying to identify duplicates and keep the latest.

The file contains two columns.
First column is the Ticket_Id, which is unique.
Second column is the instancia, which can be duplicate.
third columns is where i want to identify the duplicates

Each time a instancia enters to the system it give a new ticket. The system allows to enter same instancia, but if will give a new ticket (with a highir Ticket Id number).

I would like to keep (mark as 1) the unique based on the ticket. And from the duplicates I would like to keep (or mark as 1) the unique base on its higher ticket, which means was the latest to enter to the system

Ticket_Id INSTANCIA unique
47483 401574944 1
47485 401574966 1
47486 401574972 0
52427 401574972 1


Sometime could be more that 2 duplicates (3, or 4)

Any help will be really appreciated it.

Thanks,
mils

[oeldere]

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[oeldere]

C1 =IF(COUNTIF($B$1:$B$100,$B1)=Countif($B$1:$B1,$B1),1,"") and drag down.

The red value is the last row of the range.

[mils]

Hi oeldere,

thanks for your replay. However, your solution does not take into account the Ticket_Id.

from my example i would like to keep the instancia with the highest Ticket_Id. in this case the second one

47486 401574972 0
52427 401574972 1

is that possible to do?

thanks in advance!

[ashakantasharma]

I have tried to make out what you want. Please see the attached Excel Sheet. Let me know if anything I can do for you.

[oeldere]

@mils

Please show the file, where you get this failure, since I believe, my solution, is what you are asking for.

That is why I ask for an excel file in #2, with the manualy expected results, so I can compare the result.

[mils]

Thanks for your replay ashakantasharma.

What I want is to populate the third column called unique, with 1 or 0.

The formula should look first at duplicates Instancia (second column), when find one then look at the ticket_id (first column), and put 1 (in third column - unique), in the row where the ticket_id is higher (from both Instancia duplicates).

[mils]

Hi @oeldere, I have attached the file. Hope it helps.

[oeldere]

Why has C2 : C6 as result blanc?

I add my offered solution in Column C and add your result in bleu background color in Column D.

Please reply the criteria for C2 and C6.

[mils]

Hi oeldere ,

I'm a bit lost about your response.

what is in my excel is not right. I want to put 1 on those duplicates with higher ticked_ID.

I don't know how to apply your formula, sorry.

[oeldere]

You give in your excelfile as result for C2:C6 a blanc cell.

I added my formula in your file and my result for those cells is 1.

So there is a differance in what you expect to be the result and what I expect to be the result.

In that case you have to explain to me, what the criteria are for the cells C2: C6 (to get the result blanc).

If you add me the criteria, I will make the excel formula for that.



edit

you also have to change the range to 2083 (See the red text).

C1 =IF(COUNTIF($B$1:$B$2083,$B1)=Countif($B$1:$B1,$B1),1,"") and drag down.

I also added the amended file.

[mils]

the difference is that i would like to assign 1 to the one with the highest ticket_Id

47486 401574972 0
52427 401574972 1

[oeldere]

Your comment in #12 my, formula will do that (to my opinion).

Please open my Excel file I added and chech the results (#11)

Yes I know, but when should the result be blanc (like you showed in C2:C6 of your file)?

[bebo021999]

Another option in C2:

Please Login or Register  to view this content.