Deleting Duplicate
Solved/Closed
Related:
- Deleting Duplicate
- Outlook duplicate items remover (odir) - Download - Email
- Deleting snapchat account - Guide
- Deleting trending searches - Guide
- How to duplicate a document in google docs - Guide
- How to add new music to ipod without deleting old - Guide
11 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 16, 2010 at 10:05 PM
Nov 16, 2010 at 10:05 PM
download your file from this web
http://www.speedyshare.com/files/25233769/angel1_DeleteDup_Fill_Empty.xlsm
You have not told me how far you are familiar with macro. If I send a macro do you know how to park it, how to run it etc
I assume you are NOT familiar and the following are instructions (if you are well versed in macros ignore these instructions)
.The macros are automated way of doing repeated operations.
.the macros can be RECORDED following the keyboard steps and edit it to suit it for general purposes or you can write the macro from the scratch for which you need some expertise on macros.
For your experimental file, .I have created two macros one for the scenario A(first sheet) and second macro for the scenario B (second sheet).
. I am sending the file back to you which is now called "angel 1 delete dup..............". now you see the suffix is xlsm and not xlsx.. That means the file contains macros and the macros can be enabled. . When you open the file a pop up box will come up asking you whether you want to enable macros or disable macros. Click enable macros. (by the by if a file has suffix .xlsx that means it is not macro-enabled. In the two sheets the macros have ALREADY BEEN RUN and you see the result you want).
.how to see these macros. There is a ribbon at the top "Developer" probably at the end. (This Developer ribbon is available only in xlsm files Click this , the submenus change. On the almost extreme left there is a submenu "macros". Click that sub menu the macro window will open. In that you can see the NAMES of the two macros.. Highlight one of them and chick "Edit" on the right side and you see the two macros
.how to run the macros. As the macros have already been run in your two sheets. you have to bring back the old sheets before running the macro as soon as you open the file copy the two sheets. I have copied the two sheets in the same file now called "delete dup(2)" and "fill empty(2)". You must be knowing how to copy the sheets easily.
Right click the tab or each sheet and click "move or copy" click that. And in the window which pops up tick or check "create a copy" at the bottom and highlight "move to end" in the middle section and click ok repeat for the other sheet.
You shall know the reason for copying the sheets. As in the first two sheets the two macros have been run what you see the end result. Suppose you want to recheck.
1. You got "duplicate dup(2)" and highlight cell A1 and hit control +A. the whole sheet will be highlighted
2. Now hit control+C
3. Goto the first sheet"Duplicate Dup" and highlight cell A1 in this sheet and hit control+V. you shall get the original sheet before running the macro. Do the same for the second sheet from "fill entry(2)" to "fill entry" sheet.
4. Now click Developer. Click "macros" on the almost extreme left in the sub menus . you will see the two macro names. Highlight the first macro "scenarioa" and chick "Run" at the right top. Again click submenu "macros" and highlight the second macro "scenariob" and click Run.
. now how to use these two macros to your original file . FIRST AS A CAUTIONARY STEP SAVE THE FILE SOMEWHERE SO THAT THE FILE CAN BE RETRIEVED IF THERE IS A MESS UP"
Now in your original file
1.Your original file must be having a suffix .xlsx. which is not macro enabled. You have to convert this file into macro enabled. For this first click the excel icon (the icon at the extreme top left-left of save icon and move the cursor over "save as"(do not click)and move to the right side and click the "as macro enabled workbook" and give a name and follow instructions(folder etc). now your file will have suffix .xlsm and macro can be written and enabled.
If there is a problem post back telling clearly the problem
the macros are repeated for others' use
2.click "developer" ribbon (if it is not already open)
3.clcik "visual basic" (extreme left submenu)
4. A window will open. Hit control+R. on the left side you see the propjet window where all the open files are listed. Highlight your file name there and click insert(menu) at the top and click "module".
A blank window will open on the right. There you copy the two macros (originally you can copy these two macros temporarily in a notepad) from this notepad. Now you are ready for running the macro . follow steps how to run the maro earlier
If you are dealing with excel you (or your office) better buy one book on general subjects on excel 2007 and another book "on excel programming for excel 2007"-preferbaly by John Walkenbach.
.
http://www.speedyshare.com/files/25233769/angel1_DeleteDup_Fill_Empty.xlsm
You have not told me how far you are familiar with macro. If I send a macro do you know how to park it, how to run it etc
I assume you are NOT familiar and the following are instructions (if you are well versed in macros ignore these instructions)
.The macros are automated way of doing repeated operations.
.the macros can be RECORDED following the keyboard steps and edit it to suit it for general purposes or you can write the macro from the scratch for which you need some expertise on macros.
For your experimental file, .I have created two macros one for the scenario A(first sheet) and second macro for the scenario B (second sheet).
. I am sending the file back to you which is now called "angel 1 delete dup..............". now you see the suffix is xlsm and not xlsx.. That means the file contains macros and the macros can be enabled. . When you open the file a pop up box will come up asking you whether you want to enable macros or disable macros. Click enable macros. (by the by if a file has suffix .xlsx that means it is not macro-enabled. In the two sheets the macros have ALREADY BEEN RUN and you see the result you want).
.how to see these macros. There is a ribbon at the top "Developer" probably at the end. (This Developer ribbon is available only in xlsm files Click this , the submenus change. On the almost extreme left there is a submenu "macros". Click that sub menu the macro window will open. In that you can see the NAMES of the two macros.. Highlight one of them and chick "Edit" on the right side and you see the two macros
.how to run the macros. As the macros have already been run in your two sheets. you have to bring back the old sheets before running the macro as soon as you open the file copy the two sheets. I have copied the two sheets in the same file now called "delete dup(2)" and "fill empty(2)". You must be knowing how to copy the sheets easily.
Right click the tab or each sheet and click "move or copy" click that. And in the window which pops up tick or check "create a copy" at the bottom and highlight "move to end" in the middle section and click ok repeat for the other sheet.
You shall know the reason for copying the sheets. As in the first two sheets the two macros have been run what you see the end result. Suppose you want to recheck.
1. You got "duplicate dup(2)" and highlight cell A1 and hit control +A. the whole sheet will be highlighted
2. Now hit control+C
3. Goto the first sheet"Duplicate Dup" and highlight cell A1 in this sheet and hit control+V. you shall get the original sheet before running the macro. Do the same for the second sheet from "fill entry(2)" to "fill entry" sheet.
4. Now click Developer. Click "macros" on the almost extreme left in the sub menus . you will see the two macro names. Highlight the first macro "scenarioa" and chick "Run" at the right top. Again click submenu "macros" and highlight the second macro "scenariob" and click Run.
. now how to use these two macros to your original file . FIRST AS A CAUTIONARY STEP SAVE THE FILE SOMEWHERE SO THAT THE FILE CAN BE RETRIEVED IF THERE IS A MESS UP"
Now in your original file
1.Your original file must be having a suffix .xlsx. which is not macro enabled. You have to convert this file into macro enabled. For this first click the excel icon (the icon at the extreme top left-left of save icon and move the cursor over "save as"(do not click)and move to the right side and click the "as macro enabled workbook" and give a name and follow instructions(folder etc). now your file will have suffix .xlsm and macro can be written and enabled.
If there is a problem post back telling clearly the problem
the macros are repeated for others' use
Sub scenarioa()
Dim r As Range, c As Range
Worksheets("DeleteDup").Activate
Set r = Range(Range("C2"), Cells(Rows.Count, "C").End(xlUp))
For Each c In r
If c = c.Offset(0, 1) Then c.Offset(0, 1).Clear
Next c
End Sub
Sub scenariob()
Dim r As Range, c As Range
Worksheets("Fill Empty").Activate
Set r = Range(Range("D2"), Cells(Rows.Count, "D").End(xlUp))
For Each c In r
If c = "" Then c = c.Offset(0, -1)
Next c
End Sub
2.click "developer" ribbon (if it is not already open)
3.clcik "visual basic" (extreme left submenu)
4. A window will open. Hit control+R. on the left side you see the propjet window where all the open files are listed. Highlight your file name there and click insert(menu) at the top and click "module".
A blank window will open on the right. There you copy the two macros (originally you can copy these two macros temporarily in a notepad) from this notepad. Now you are ready for running the macro . follow steps how to run the maro earlier
If you are dealing with excel you (or your office) better buy one book on general subjects on excel 2007 and another book "on excel programming for excel 2007"-preferbaly by John Walkenbach.
.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 17, 2010 at 05:52 AM
Nov 17, 2010 at 05:52 AM
quote
However, I got (Scenario B) to work exactly what I wanted to do beside one little problem. The macro does all the copying above the last data in column D. The macro stopped at the end of the last data in column D. For example: If I have data from C2 to C10 and the last data is on D5, then it will not do the copying from D6 to D10 unless I put a character on D11. Anyway around this?
unquote
try this modified macro which I called "scenarioB1" if this successful you can delete the macro "scenariob"
post feedback
However, I got (Scenario B) to work exactly what I wanted to do beside one little problem. The macro does all the copying above the last data in column D. The macro stopped at the end of the last data in column D. For example: If I have data from C2 to C10 and the last data is on D5, then it will not do the copying from D6 to D10 unless I put a character on D11. Anyway around this?
unquote
try this modified macro which I called "scenarioB1" if this successful you can delete the macro "scenariob"
Sub scenrioB1()
Dim r As Range, c As Range
Worksheets("fill empty").Activate
Set r = Range(Range("c2"), Cells(Rows.Count, "c").End(xlUp))
For Each c In r
If c.Offset(0, 1) = "" Then c.Offset(0, 1) = c
Next c
End Sub
post feedback
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 17, 2010 at 08:30 PM
Nov 17, 2010 at 08:30 PM
1. the statement
worksheets("....").activate
is given only for safety. By mistake if you are in some other sheet and you run the macro it may give problem. So that statement is not necessary. you can either delete it or put a single apostrophe(') at he beginning of the statement so that that statement is non operable. Only caution is you keep the correct sheet open . I have already put the single apostrophe in the two macros. you just copy these as these are
2. I am repeating the two macros
worksheets("....").activate
is given only for safety. By mistake if you are in some other sheet and you run the macro it may give problem. So that statement is not necessary. you can either delete it or put a single apostrophe(') at he beginning of the statement so that that statement is non operable. Only caution is you keep the correct sheet open . I have already put the single apostrophe in the two macros. you just copy these as these are
2. I am repeating the two macros
Sub scenarioa()
Dim r As Range, c As Range
'Worksheets("DeleteDup").Activate
Set r = Range(Range("C2"), Cells(Rows.Count, "C").End(xlUp))
For Each c In r
If c = c.Offset(0, 1) Then c.Offset(0, 1).Clear
Next c
End Sub
Sub scenrioB1()
Dim r As Range, c As Range
'Worksheets("fill empty").Activate
Set r = Range(Range("c2"), Cells(Rows.Count, "c").End(xlUp))
For Each c In r
If c.Offset(0, 1) = "" Then c.Offset(0, 1) = c
Next c
End Sub
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 14, 2010 at 08:35 PM
Nov 14, 2010 at 08:35 PM
try this macro
Sub test()
Dim r As Range, c As Range
Set r = Range(Range("B1"), Range("B1").End(xlDown))
For Each c In r
If c = c.Offset(0, -1) Then c = ""
Next c
End Sub
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
Nov 15, 2010 at 08:18 PM
Nov 15, 2010 at 08:18 PM
sorry perhaps you are not familiar with macros. The solution I have given is NOT formula but automotive solution . ok do not worry.
try this (read the instructions carefully or take a print out and keep it before you)
you have data from A1 B1 down
1.in C1 type this formula
=IF(A1=B1,"",A2)
2. now copy C1 down as far as data is available
3. highlight data in column C (from C1 down whether there is data or not)
hit control+C and now select C1 and click edit(menu)-pastespecial-values and then click ok at the bottom.
4. .now highlight column B and right click and click "delete" in the middle section
is this what you want?
try this (read the instructions carefully or take a print out and keep it before you)
you have data from A1 B1 down
1.in C1 type this formula
=IF(A1=B1,"",A2)
2. now copy C1 down as far as data is available
3. highlight data in column C (from C1 down whether there is data or not)
hit control+C and now select C1 and click edit(menu)-pastespecial-values and then click ok at the bottom.
4. .now highlight column B and right click and click "delete" in the middle section
is this what you want?
Hello Venkat1926,
I followed the instruction you provided and it appears to bring the phone number in column A and moving it up one row in column C or maybe I not doing this right.
I need the data in column A and B to be in the same order, but deleting the data in column B if its the same as column A. Duplicating in the same column is ok but not on the same row.
For example:
Exhibit A Exhibit B
A B A B
Steve Steve Steve
Joe Bobby Joe Bobby
Steve Jamie Steve Jamie
5555 5555 5555
Winter Fall Winter Fall
I need a macro formula so I can paste into module 1. Looking for for a code to make my life easier. Thanks
I followed the instruction you provided and it appears to bring the phone number in column A and moving it up one row in column C or maybe I not doing this right.
I need the data in column A and B to be in the same order, but deleting the data in column B if its the same as column A. Duplicating in the same column is ok but not on the same row.
For example:
Exhibit A Exhibit B
A B A B
Steve Steve Steve
Joe Bobby Joe Bobby
Steve Jamie Steve Jamie
5555 5555 5555
Winter Fall Winter Fall
I need a macro formula so I can paste into module 1. Looking for for a code to make my life easier. Thanks
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 16, 2010 at 02:26 AM
Nov 16, 2010 at 02:26 AM
what is meant by "macro formula" It is either formula or macro
I suggest you upload a small extract of your file in
https://authentification.site
and post the web page where it is uploaded (remove password)
and then explain what you want giving two or three examples
But you have to decide whether you want a formula solution or macro solution.
I suggest you upload a small extract of your file in
https://authentification.site
and post the web page where it is uploaded (remove password)
and then explain what you want giving two or three examples
But you have to decide whether you want a formula solution or macro solution.
Here the link:
https://authentification.site/files/25230923/DeleteDup_Fill_Empty.xlsx
Maybe you can help me put the code so that I can run the macro.
Thanks for being patience with me.
https://authentification.site/files/25230923/DeleteDup_Fill_Empty.xlsx
Maybe you can help me put the code so that I can run the macro.
Thanks for being patience with me.
Hello Venkat,
First of all I would like to thank you for taking so much time helping me and secondly, I want to apologized for providing you with the wrong column number in scenario A.
Scenario A works Great!
However, I got (Scenario B) to work exactly what I wanted to do beside one little problem. The macro does all the copying above the last data in column D. The macro stopped at the end of the last data in column D. For example: If I have data from C2 to C10 and the last data is on D5, then it will not do the copying from D6 to D10 unless I put a character on D11. Anyway around this?
I got your codes to work on my other spreadsheets after changing the name to reflect the current work sheet.
Thanks for making my daily tasks so much easier!
First of all I would like to thank you for taking so much time helping me and secondly, I want to apologized for providing you with the wrong column number in scenario A.
Scenario A works Great!
However, I got (Scenario B) to work exactly what I wanted to do beside one little problem. The macro does all the copying above the last data in column D. The macro stopped at the end of the last data in column D. For example: If I have data from C2 to C10 and the last data is on D5, then it will not do the copying from D6 to D10 unless I put a character on D11. Anyway around this?
I got your codes to work on my other spreadsheets after changing the name to reflect the current work sheet.
Thanks for making my daily tasks so much easier!
That works!
You are awesome!!! Can you help me revise the code for scenario A and B, so that it can work on any active file? All my changes that I need to do are all on the same columns. This way I dont have to change the worksheet name every time to the current worksheet that I working on and just need to have the worksheet with the macro open.
You are awesome!!! Can you help me revise the code for scenario A and B, so that it can work on any active file? All my changes that I need to do are all on the same columns. This way I dont have to change the worksheet name every time to the current worksheet that I working on and just need to have the worksheet with the macro open.
Well, there are some kind of Ekstension that you can use to delete the files which are unwanted or you don't want to do.And if you like want to save the data before deleting then may be ekstensions are there for your help.
http://www.linella.dk/
http://www.linella.dk/
