Simple question envolving VBA
 

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

NEW - AFTER MACRO RUN:
| a | b |
---------
| 1 | 1 |
---------
| 2 | 2 |
---------
| 1 | |
---------
| 2 | |
---------
| 2 | |
---------
| 1 | |
--------

--
Message posted from http://www.ExcelForum.com

Simple question envolving VBA
 

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


--
Regards
Frank Kabel
Frankfurt, Germany


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.

ORIG IONAL:

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

NEW - AFTER MACRO RUN:
a | b |
---------
1 | 1 |
---------
2 | 2 |
---------
1 | |
---------
2 | |
---------
2 | |
---------
1 | |
---------


---
Message posted from http://www.ExcelForum.com/

Simple question envolving VBA
 

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

Simple question envolving VBA
 

On Sun, 16 May 2004 13:34:11 -0500, chrisdarl
wrote:

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.


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