Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

Hi,

Right click your sheet tab, view code and paste the code below in. When ever
a new value is added to column A a unique list is created in column B. To
make it work for the first time enter something in column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Column < 1 Then Exit Sub
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LastRow).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Cells(1, 2), Unique:=True
Application.EnableEvents = True
End Sub

Mike

"Maracay" wrote:

Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

In B1 insert:
=IF(COUNTIF(A$1:A1,A1)1,"",A1)

Drag it down completely. However, ignore the blanks or "".

--
Thanx in advance,
Best Regards,

Faraz


"Maracay" wrote:

Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

Forgot to mention, it assumes the first row is a header



"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste the code below in. When ever
a new value is added to column A a unique list is created in column B. To
make it work for the first time enter something in column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Column < 1 Then Exit Sub
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LastRow).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Cells(1, 2), Unique:=True
Application.EnableEvents = True
End Sub

Mike

"Maracay" wrote:

Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

One way, using a change event to add to the bottom of col B
Right click sheet tabview codeinsert this. Sort if desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Or Target.Count 1 Then Exit Sub
Set mv = Columns(2).Find(What:=Target, After:=Cells(1, 2), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If mv Is Nothing Then _
Target.Copy Cells(Rows.Count, 2).End(xlUp).Offset(1)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Maracay" wrote in message
...
Hi Guys

I have this information in column A, the information is repeated and what
I
want to have a second column with the information just ones, but I need
this
to be synchronized, as soon the user include a new concept in column A I
want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

If you don't like gaps and/or Macros but don't mind an helper-column that
can be hidden - take a look at he
http://img710.imageshack.us/img710/21/nonamev.png
Micky


"Maracay" wrote:

Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

Hi Mike,

I did what you said but it doesn't work, I copy the code and start including
new data, but nothing happend in column B.

Thanks

"Mike H" wrote:

Forgot to mention, it assumes the first row is a header



"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste the code below in. When ever
a new value is added to column A a unique list is created in column B. To
make it work for the first time enter something in column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Column < 1 Then Exit Sub
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LastRow).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Cells(1, 2), Unique:=True
Application.EnableEvents = True
End Sub

Mike

"Maracay" wrote:

Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck

Hi Faraz,

It partially works, is important that in column B all the concepts appear
one after the other one, without blank lines in between, your application
look like in the sample below, the only think I need are the concepts one
after the other without blank lines like my initial sample

Column A Column B
Plane Plane
car car
moto moto
bici bici
moto
truck Truck
truck
skii skii
truck
skii
truck
truck

Thanks

"Faraz A. Qureshi" wrote:

In B1 insert:
=IF(COUNTIF(A$1:A1,A1)1,"",A1)

Drag it down completely. However, ignore the blanks or "".

--
Thanx in advance,
Best Regards,

Faraz


"Maracay" wrote:

Hi Guys

I have this information in column A, the information is repeated and what I
want to have a second column with the information just ones, but I need this
to be synchronized, as soon the user include a new concept in column A I want
that concept in column B, if include a concept that already exist nothing
happen because is already in column B, I dont want the users to go thru
filter option.

I would really appreciate if someone can give me a hand with this.

Thanks



Column A Column B
Plane Plane
car car
moto moto
bici bici
moto Truck
truck skii
truck
skii
truck
skii
truck
truck