Excel and removing duplicate rows

[perlmonk]

Gentlemen and Ladies:
a quick question....i have an excel sheet, with two columns having numeric content.
I want to compare every row from column B against the whole column A to scan if its in that list...if it is, then it should be placed in column C, and if its not, thenin Column D...you get the idea.

Other solution might be, if I can scan for duplicate rows in column A and delete them....does anyone have any scripts or any code that might help me out?

Any help would be highly appreciated...cheers!

[nagasrikanth]

hi
chk the code..
as im also new to VB ,i might used more number of variables
it might also possible that total procedure that i fallowed was wrong..
but finally what i get was xtly what u want..

thanx &regards
anu..

PS: Please rate this post..


Private Sub CommandButton1_Click()
Dim x As Integer, y As Integer
Dim flag As Boolean
Dim Cincr As Integer 'if u want u can use this or u can directly
Dim Dincr As Integer 'use x also
Cincr = 1
Dincr = 1
For x = 1 To 23
flag = False
For y = 1 To 23
If Range("b" & x & "").Value = Range("a" & y & "").Value Then
flag = True
GoTo loopbreak
End If
Next y
loopbreak:
If flag = True Then
Range("c" & Cincr & "").Value = Range("b" & x & "").Value
Cincr = Cincr + 1
Else
Range("d" & Dincr & "").Value = Range("b" & x & "").Value
Dincr = Dincr + 1
End If
Next x
End Sub

[perlmonk]

How should I use this code? do i need to create a form for that?
I believe I will have to create a macro..so, what would the steps be...create a macro, lets say ABC, and simply paste this code in there?

[VBAhack]

Try this:

1. Looking at a spreadsheet, pick menu Tools -> Macro -> Visual Basic Editor
2. You should be in VBE screen. Pick menu Insert -> Module
3. Paste the code above into the main screen. You can rename the sub to something else if you like, let's say ABC
4. Go back to the spreadsheet window and pick View->Toolbars->Forms. The Forms toolbar should now be visible
5. Click the botton icon on the toolbar, then drag a rectangle on your spreadsheet with the cursor to create a button. A window pop's up asking you to pick a macro. Choose ABC (or whatever name you used) and click OK
6. Close the toolbar (x in upper right hand corner)
7. Clicking the button you just created executes the macro

VBAhack

Instead of using a button, you can also (from spreadsheet menu) pick Tools->Macro->Macros, pick the name of the macro, then click Run. Also, you can use forms (Userform in VBAspeak) if you want, but the button is much more simple if all you want to do is execute a macro.

[nagasrikanth]

hey....
y this much of bothering..

under view menu
"view>>>toolbars>>>>control toolbox " will be there,

select command button and place it on the sheet..

by double clicking it..code window opens..

just paste the code..

and check it now..

regards
anu..

[VBAhack]

Correct. Many ways......

VBAhack

[perlmonk]

Thanks y'all....I'll try this out.

[perlmonk]

Code works great...however, does anyone know of any formula that i can use to select the cells and compare every row (every value) in column B to all the rows (all the values) in Column A and put the unique rows from col B in col C.

I had a spreadsheet earlier with this embedded formula, but I seem to have lost it. If anyone could come up with such a formula..that'd be great.

Thanks!!

[sciguyryan]

Code works great...however, does anyone know of any formula that i can use to select the cells and compare every row (every value) in column B to all the rows (all the values) in Column A and put the unique rows from col B in col C.

I had a spreadsheet earlier with this embedded formula, but I seem to have lost it. If anyone could come up with such a formula..that'd be great.

Thanks!!

By compare whay do you mean?

And what result are you intending to add to column C? The average of the two, the mean...?

Cheers,

RyanJ

[perlmonk]

By Comparing I mean that lets consider we have two columns --col A and col B.


Col A Col B Col C Col D
10 20
20 30
30 35
40 40


Now, I want to compare every element of Col B to Col A. If any element of Col B is not found in Col A, then that element should be placed in Col C. Essentially I want to find out the unique elements in Col B that are not found in Col A.

In the abolve example, I should see 35 placed in Col C.

[Ecniv]

vlookup

One which checks from column 1 in column two (make sure the ranges are static ($)), and one that does the reverse will need the columns reordered or a copy of the column.

Attached is a .xls file demonstarting this. D/l and rename :)

[westconn1]

this may not be the best way of doing it, but it certainly works

myrow = 2
mytargrow = 2

Do While Not Range("b" & myrow) = isNothing
myval = Range("b" & myrow)
f = False
On Error Resume Next
f = Range("A1:A20").Find(What:=myval, LookIn:=xlValues).Activate
If f = False Then Range("c" & mytargrow).Value = myval: mytargrow = mytargrow + 1
On Error GoTo 0
myrow = myrow + 1

Loop


pete

[perlmonk]

vlookup

One which checks from column 1 in column two (make sure the ranges are static ($)), and one that does the reverse will need the columns reordered or a copy of the column.

Attached is a .xls file demonstarting this. D/l and rename :)

yea...this works....thanks a lot for your help!

Is there any other excel formula that can also be used to do the kind of comparison I want? I know for sure there is, as I had an excel earlier that did it, but i somewhoe managed to lose it. if someone knows some other excel formula, that'd be great to know as well!

[perlmonk]

this may not be the best way of doing it, but it certainly works

myrow = 2
mytargrow = 2

Do While Not Range("b" & myrow) = isNothing
myval = Range("b" & myrow)
f = False
On Error Resume Next
f = Range("A1:A20").Find(What:=myval, LookIn:=xlValues).Activate
If f = False Then Range("c" & mytargrow).Value = myval: mytargrow = mytargrow + 1
On Error GoTo 0
myrow = myrow + 1

Loop


pete

Hey pete...I appreciate your effort in writing/finding this code for this issue...however, i already have a code submitted above, that works correctly. Im looking to find some built in excel formula that would help me in getting the results im looking for.

Thanks again!

[cssriraman]

Hi,

I think there is no need to use macros to find duplicate values. If you want you can use it. You can use the excel formulas to do that.

--------------------------------------------
Dates in column A Text in Column B
--------------------------------------------
03/10/2003 | AAA
03/15/2003 | BBB
03/20/2003 | CCC
03/25/2003 | AAA
03/30/2003 | BBB
04/04/2003 | CCC
03/25/2003 | AAA
03/30/2003 | BBB
04/04/2003 | CCC
03/25/2003 | AAA
03/30/2003 | BBB
04/04/2003 | CCC

Enter the formula : =A1&B1 to cell C1 and copy / paste the formula to cells C2:C12

Enter the formula : =IF(COUNTIF($C$1:C1,C1)>1,"Duplicate","Unique")
to cell E1 and copy / paste the formula to cells E2:E12

You can change the above formula as per your need.

Thanks,

CS.