Simple question envolving VBA

C

chrisdarl

Hi, i have two columns. A and B. column A contains the data. column
doesnt contain anything yet. i want to make a macro so that column a i
searched, and only one value of each duplicate value found is put int
column b. i have shown the origional table and the table that i wan
after the macro has been run to try and help explain what i am wantin
to do. Many thanks chris.

ORIG IONAL:

| a | b |
---------
| 1 | |
---------
| 2 | |
---------
| 1 | |
---------
| 2 | |
---------
| 2 | |
 
F

Frank Kabel

Hi
1. a non macro solution / only using worksheet functions:
- In cell B1 put the formula
=A1
- in cell B2 put the following array formula (entered with
CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(0,COUNTIF($B$1:$B1,$A$1:$A$100),0)),"",IF(INDEX($A$1:$A$
1
00,MATCH(0,COUNTIF($B$1:$B1,$A$1:$D$100),0))="","",INDEX($A$1:$A$1
00,MATCH(0,COUNTIF($B$1:$B1,$A$1:$D$100),0))))
and copy this formula down for as many rows as required

2. You may also select column a and use 'Data - Filter - Advanced
Filter'. Check 'Unique entries' and choose column B as target location
 
G

Greg Wilson

Perhaps this:

Sub TransferDups()
Dim Rng1 As Range, Rng2 As Range
Dim C As Range, Rw As Long
Rw = Range("A65536").End(xlUp).Row
Set Rng1 = Range("A1:A" & Rw)
Set Rng2 = Range("B1:B" & Rw)
For Each C In Rng1
If Application.CountIf(Rng1, C) > 1 And _
Application.CountIf(Rng2, C) = 0 Then _
C.Offset(, 1) = C
Next
End Sub

Regards,
Greg
 
R

Ron Rosenfeld

Hi, i have two columns. A and B. column A contains the data. column B
doesnt contain anything yet. i want to make a macro so that column a is
searched, and only one value of each duplicate value found is put into
column b. i have shown the origional table and the table that i want
after the macro has been run to try and help explain what i am wanting
to do. Many thanks chris.
Click to expand...

IF A1 contains a Title (and not data which might be duplicated further down)
you could use the AdvancedFilter method:


====================
Sub Uniques()
Range("A1:A100").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), _
Unique:=True
End Sub
====================



--ron
 
Top