Deleting unique dates
Closed
Hurley
-
Jul 12, 2009 at 07:27 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 27, 2009 at 09:08 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 27, 2009 at 09:08 PM
Related:
- Deleting unique dates
- Deleting snapchat account - Guide
- Windows defender not deleting virus ✓ - Viruses & Security Forum
- Different dates of "end to end encryption message" on blank chat? ✓ - Network Forum
- Deleting trending searches - Guide
- Unique phone ✓ - Word Forum
7 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 12, 2009 at 08:30 PM
Jul 12, 2009 at 08:30 PM
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.
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.
Sorry, let me clarify with this
</code>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.
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
</code>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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 14, 2009 at 08:40 PM
Jul 14, 2009 at 08:40 PM
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)
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.
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.
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:
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.
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.
Didn't find the answer you are looking for?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 16, 2009 at 09:12 PM
Jul 16, 2009 at 09:12 PM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 27, 2009 at 09:08 PM
Jul 27, 2009 at 09:08 PM
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
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