Remove Duplicates keeping first and last values

[Vlkcn]

Hi,

I need an immediate assistance
I have a set of data likes this
02-Oca-1417:15:10
02-Oca-1417:25:54
02-Oca-1418:25:38
03-Oca-1408:17:30
03-Oca-1408:31:57
03-Oca-1408:41:53
03-Oca-1408:58:02
i need to remove
duplicates but keep first and last rows for each 2-ocak-14 and 3-0cak-14 and goes on, ocak is month january,

 

[jayalaxmi]

Hi,

I need an immediate assistance
I have a set of data likes this
02-Oca-1417:15:10
02-Oca-1417:25:54
02-Oca-1418:25:38
03-Oca-1408:17:30
03-Oca-1408:31:57
03-Oca-1408:41:53
03-Oca-1408:58:02
i need to remove
duplicates but keep first and last rows for each 2-ocak-14 and 3-0cak-14 and goes on, ocak is month january,

hey dear but none of the data in these are duplicates...every row is unique..

 

[Vlkcn]

hey dear but none of the data in these are duplicates...every row is unique..

dates are duplicate, every day include hourly data,,,i need to sort out earliest and latest hours for each day

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Vlkcn]

Hi ,

See if this is OK.

Narayan

thank u for the help, i believe your code will resolve it but i had diffuculties to implement on my file, and may i ask if we can delete the intermediate cells only keep earliest and latest time rows

 

[NARAYANK991]

Hi ,

See the attached file ; the problem was that the time values in your file are all text ; the earlier formula needed to be changed to take this into account.

Narayan

 

[jayalaxmi]

Amazing Narayan sir

 

[Vlkcn]

thank you Narayan,

u r a life saver,

 

[Krishna Kinker Singh]

I use four supportive column,

=LEFT(A2,9) =COUNTIF($B$2:B2,B2) =C2-C3 =IF(OR(C2=1,D2>-1),"Not Duplicate","Duplicate")

Thanks,

Krishna

 

[Aby]

Hi ,

See the attached file ; the problem was that the time values in your file are all text ; the earlier formula needed to be changed to take this into account.

Narayan

A formula to filter only unique dates and display it works awesome!

Great formula NARAYANK991 , Could you pls tell me when and why "--" is used. I also have seen it in SUMPRODUCT formulas, but dont know abt it..

 

[jayalaxmi]

A formula to filter only unique dates and display it works awesome!

Great formula NARAYANK991 , Could you pls tell me when and why "--" is used. I also have seen it in SUMPRODUCT formulas, but dont know abt it..

View attachment 19073

same questn Aby....I am also curious to know it....

 

[NARAYANK991]

Hi ,

The double minus is a standard operation to :

1. Retain the magnitude of the data value

2. Convert its type from text to numeric

The same result can be obtained by any mathematical operation which does not change the magnitude of the data value ; examples of this are :

a. Multiplication by 1

b. Division by 1

c. Addition of 0

Narayan

 

[jayalaxmi]

Hi ,

The double minus is a standard operation to :

1. Retain the magnitude of the data value

2. Convert its type from text to numeric

The same result can be obtained by any mathematical operation which does not change the magnitude of the data value ; examples of this are :

a. Multiplication by 1

b. Division by 1

c. Addition of 0

Narayan

thanks sir

 

[Vlkcn]

Dear Narayan,

I have one more question if u dont mind,

when i tried to change the range of formula,for higher number of cells, and copy it to another table it does not work, the brackets disappear. When i tried to recall brackets than it appears as a text...

 

[NARAYANK991]

Hi ,

The brackets are not to be manually entered ; they will be entered automatically by Excel.

The formula is what is known as an array formula ; this is to be entered using a 3 keystroke combination , consisting of the keys CTRL SHIFT ENTER

To use this , what you do is after copying the formula where ever you want to , press F2 to enter EDIT mode ; now keep the keys CTRL and SHIFT pressed , and then press the ENTER key , so that all 3 keys are pressed together ; this enters the formula as an array formula , and Excel automatically inserts the brackets around the formula.

Narayan