|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 31st, 2009, 01:18 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Having a repeated concept ones
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 don’t 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 
|
|
#2
December 31st, 2009, 01:49 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
|
#3
December 31st, 2009, 01:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
|
#4
December 31st, 2009, 01:53 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
|
#5
December 31st, 2009, 02:03 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
|
#6
December 31st, 2009, 02:26 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
|
#7
December 31st, 2009, 02:44 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
|
#8
December 31st, 2009, 02:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Having a repeated concept ones
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 don’t 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
