Deleting DuplicateSolved/Closed

Question

Hello, 


I have a worksheet of data, but need help with the data in column A and B.   I am looking for a formula in excel macro to delete the data in column B if the data is the same for column A and B on the same row.  Duplicating in the same column is ok,  but not on the same row for column A and B.

Any help will be appreciated.   Thank you

venkat1926 · Answer

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

venkat1926 · Answer

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?

Cindy · Answer

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

venkat1926 · Answer

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.

Cindy · Answer

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.

venkat1926 · Answer

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

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.




.

Angel · Answer

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!

venkat1926 · Answer

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"

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

Angel · Answer

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.

venkat1926 · Answer

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 

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

ekstensions clips · Answer

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/

Deleting Duplicate

11 responses

Similar discussions

Subscribe To Our Newsletter!