macro to search column

  • Thread starter Thread starter Andy Brown
  • Start date Start date
A

Andy Brown

We're pretty good at writing macros ...
We a have columns (sic) containing duplicate information ...
write macro that will "simply" say; if a value in the
column exists then copy it to the next column, but only copy it once
Click to expand...

You'll probably want to write something that incorporates Advanced Filter
then.

Rgds,
Andy
 
We're pretty good at writing macros but not that good.
We a have columns containing duplicate information - for example:
A
A
A
C
C
F
F
F
F and so on
We're trying to write macro that will "simply" say; if a value in the
column exists then copy it to the next column, but only copy it once (we
only need to know if it appears once not x number of times in a column. OR
goto the first cell; its value is "A" ; search the rest of the column for
"A" , delete all OTHER instances of "A" (but keep the first instance); then
go to cell below that and do the same. The column can then be sorted to
produce this:
A
C
F and so on

Any assistance would be greatly appreciate
Thanks
Richard
 
Instead of a macro, select your column, choose Data/Filter/Advanced
Filter. Select the Copy to another location radio button and check
the Unique records only checkbox. click in the destination box and
select your column. Click OK. All the unique records will be copied
to the new column.

Whole process takes about 10 seconds once you've done it a time or
two.

You can do it with a macro, too:

Public Sub MoveUnique()
Columns("A:A").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Columns( _
"B:B"), Unique:=True
End Sub
 
I don't think you really need a macro to do this.
Enter your data in Column A.
Then, just enter this formula in cell B1 and copy down

=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

For the first occurence it will print the data in cell B1 but if the same
data is in A2, A3 or A4 then it will show a blank.
This way you will get only the first occurence in Column B without
duplicates.
===================
column exists then copy it to the next column, but only
copy it once (we only need to know if it appears once not x number of times in a column. OR
Click to expand...
"A" , delete all OTHER instances of "A" (but keep the
first instance); then go to cell below that and do the same. The column
Click to expand...
can then be sorted to produce this:
First sort your initial column to get all like values together. Then, I
Click to expand...
think if you use the following formula
in the adjacent column, you will get your desired results:
Click to expand...
=IF(A2=A1,"",A2). Copy this formula the length
of the column. Then do a Copy/Paste Special in this column to paste back
Click to expand...
the values instead of the formulas.
Once this is done, you have a column containing only one occurrence of
Click to expand...
each value in the other column.
 
Back
Top