Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Maybe this is a simple question | Excel Worksheet Functions | |||
Simple Question | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
simple question, hopefully a simple answer! | Excel Programming |