Deleting unique dates Closed

Question

Hi,

I have two colums of recorded dates going back 5 years but sometimes one will have and extra date or it will be missing one. I'd like to delete the unique dates in either of the two columns, using conditional formatting i can format the unique cells but i would like to be able to delete each unique cell and the cell right beside it (price) to get a result of the exact same dates in the two columns and their rsepective columns alongside. I imagine this isn't incdredibally difficult but i cant figure out how to do it at all. Any help would be greatly appreciated. Thanks.

venkat1926 · Answer

your use of term <unique> is little confusing
let me try to rephrase it and see whether it is ok
you have dates in column  A and B 

you want the entries in these 2 columns in a particualr row should be 
the same

for e.g. A1 and A2 should have same date. 
suppose if  A1 or A2 is blank, the blank cell can be substsituted by the 
value in the non blank cell. ok
but if A1 and A2 are having two different dates which date is the correct date?

think about this and rephrase your quetion, if necessary giving examples.

Hurley · Answer

Sorry, let me clarify with this 13/01/2004 4440.1 13/01/2004 10427.18 14/01/2004 4461.4 14/01/2004 10538.37 15/01/2004 4456.1 15/01/2004 10553.85 16/01/2004 4487.9 16/01/2004 10600.51 19/01/2004 4518.1 20/01/2004 10528.66 20/01/2004 4499.3 21/01/2004 10623.62 21/01/2004 4511.2 22/01/2004 10623.18 22/01/2004 4476.8 23/01/2004 10568.29 23/01/2004 4460.8 26/01/2004 10702.51 as you can see most of the dates are the same except for the 19/01/2004 in the first column. (Sometimes the second column of dates has the extra date).I would like to be able to delete that date and its corresponding price, moving all prices underneath it up so as to get the dates in both columns matching in each particular row. If i use the conditional formatting i can highlight the "unique" dates between the two columns, i would like to delete all of these and their corresponding price automatically as i have a lot to get through. Thank you for your swift reply and your time.

venkat1926 · Answer

I do not know whether I have understood you completely.
keep the original file safely so that this file is messed up you can retrieve original file 

your original sheet is like this (SHEET1)

date1	data1	date2	data2
13/01/2004	4440.1	13/01/2004	10427.18
14/01/2004	4461.4	14/01/2004	10538.37
15/01/2004	4456.1	15/01/2004	10553.85
16/01/2004	4487.9	16/01/2004	10600.51
19/01/2004	4518.1	20/01/2004	10528.66
20/01/2004	4499.3	21/01/2004	10623.62
21/01/2004	4511.2	22/01/2004	10623.18
22/01/2004	4476.8	23/01/2004	10568.29
23/01/2004	4460.8	26/01/2004	10702.51
 the macro is (modify to suit you)
Sub test()
Dim rng As Range, c As Range
Dim cfind As Range
Worksheets("sheet1").Activate
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In rng
If c <> c.Offset(0, 2) Then
Set cfind = rng.Cells.Find(what:=c.Offset(0, 2).Value, lookat:=xlWhole)
 Range(cfind, cfind.Offset(0, 1)).Cut
c.Select
ActiveSheet.Paste
End If
Next

End Sub

after running the macro sheet1 will look like this (Is this what you want)

date1	data1	date2	data2
13/01/2004	4440.1	13/01/2004	10427.18
14/01/2004	4461.4	14/01/2004	10538.37
15/01/2004	4456.1	15/01/2004	10553.85
16/01/2004	4487.9	16/01/2004	10600.51
20/01/2004	4499.3	20/01/2004	10528.66
21/01/2004	4511.2	21/01/2004	10623.62
22/01/2004	4476.8	22/01/2004	10623.18
23/01/2004	4460.8	23/01/2004	10568.29
		                        26/01/2004        10702.51
post feedback.

Hurley · Answer

Thank you,
That macro does work for the first column of dates until it breaks down when there is an irregular (or unique) date under "date2". I have tried to adjust the macro to suit but cannot.
For example:

01/04/2004	4410.7	02/04/2004	10470.59
02/04/2004	4465.6	05/04/2004	10558.37
05/04/2004	4480.7	06/04/2004	10570.81
06/04/2004	4472.8	07/04/2004	10480.15
07/04/2004	4468.7	08/04/2004	10442.03
08/04/2004	4489.7	12/04/2004	10515.56
13/04/2004	4515.8	13/04/2004	10381.28
14/04/2004	4485.4	14/04/2004	10377.95
15/04/2004	4505.5	15/04/2004	10397.46
16/04/2004	4537.3	16/04/2004	10451.97

At the date 12/04/2004 in the "date2" column the code stops running because there is no 12/04/2004 date in the "date1" column. 
Your help is much appreciated and is helping me learn more for which i thank you.

venkat1926 · Answer

you have not tole me what to do with the "offending" row containing 12/4/2004

anyhow I have modified the macro see whether it is ok 

by the by in excel the dates are entered as mm/dd/yy and not as you have entered 

Sub test()
Dim rng As Range, c As Range
Dim cfind As Range
On Error Resume Next

Worksheets("sheet1").Activate
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In rng
If c <> c.Offset(0, 2) Then
Set cfind = rng.Cells.Find(what:=c.Offset(0, 2).Value, lookat:=xlWhole)
 If cfind Is Nothing Then GoTo line1
 MsgBox cfind.Address
 Range(cfind, cfind.Offset(0, 1)).Cut
c.Select
ActiveSheet.Paste
End If
line1:
Next

End Sub

Hurley · Answer

I would like to delete all the 'offending' dates, and their corresponding value.

venkat1926 · Answer

if your macro works it must be ok. to delete the offending ROWS I have added two lines at the end of the macro.  I think this will take care of that.

I hope your original file is safe 

Sub test()
Dim rng As Range, c As Range
Dim cfind As Range
On Error Resume Next

Worksheets("sheet1").Activate
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In rng
If c <> c.Offset(0, 2) Then
Set cfind = rng.Cells.Find(what:=c.Offset(0, 2).Value, lookat:=xlWhole)
 If cfind Is Nothing Then GoTo line1
 'MsgBox cfind.Address
 Range(cfind, cfind.Offset(0, 1)).Cut
c.Select
ActiveSheet.Paste
End If
line1:
Next
rng.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

Deleting unique dates

7 responses

Similar discussions

Subscribe To Our Newsletter!