Return a List of Unique Values form another list

[juajar]

Hi, I have some oinfo which looks like this:

BaseSecundarioSep2006 - VF.xls
	A	B	C	D	E	F	G	H
2	Mnemotcnico	Emisor	Precio	Rentabilidad % EA	Valor nominal (pesos)	Volumen (pesos)	Tipo	Tipo de Tasa
3	BDCU2999CVA	DPTO DE CUNDINAMARCA			500,000,000	605,478,500.00		IPC
4	BDCU2999CVA	DPTO DE CUNDINAMARCA			1,450,000,000	1,633,928,150.00		IPC
5	BDCU2999CVB	DPTO DE CUNDINAMARCA			1,000,000,000	1,072,001,000.00		0.00%
6	BDCU2999CVB	DPTO DE CUNDINAMARCA			1,000,000,000	1,111,651,000.00		0.00%
7	BDCU2999CVB	DPTO DE CUNDINAMARCA			200,000,000	222,330,200.00		0.00%
8	BDCU2999CVB	DPTO DE CUNDINAMARCA			200,000,000	221,300,200.00		0.00%
9	BDCU2999CVB	DPTO DE CUNDINAMARCA			200,000,000	221,398,200.00		0.00%
10	BDCU2999CVB	DPTO DE CUNDINAMARCA			500,000,000	602,810,500.00		0.00%
11	BDCU2999CVB	DPTO DE CUNDINAMARCA			500,000,000	561,530,500.00		0.00%
12	BDIT03180307	BOGOTA D.C.			2,000,000,000	2,052,854,000.00		TF
13	BDIT03180307	BOGOTA D.C.			1,000,000,000	1,026,547,000.00		TF
CONTINUO PUBLICO

The list goes on for aproximately 5000 rows. If you look at the first column, you will notice that some of the values repeat themselves.

I need excel to show me a list on another sheet but without repeating the same values over and over, in other words, I need a list of UNIQUE values for that column.

An example would be this result list on another sheet:

BaseSecundarioSep2006 - VF.xls
	A	B	C	D
2	Mnemotcnico	Emisor	Precio	Rentabilidad % EA
3	BDCU2999CVA	DPTO DE CUNDINAMARCA
4	BDCU2999CVB	DPTO DE CUNDINAMARCA
5	BDIT03180307	BOGOTA D.C.
6	BDIT04230909	BOGOTA D.C.
7	BDVT07150510	BOGOTA D.C.
8	BEPB102B9VA	EPM BOGOTA
CONTINUO PUBLICO

Thanks for your help!!!

 

[scu77]

deleted

 

[babycody]

You can use advanced filter as described here: http://www.contextures.com/xladvfilter01.html#FilterUR

 

[juajar]

Advanced Filters can´t be used

Hi, the problem of using advanced filters is that if I change the data the output doesn´t refresh, so I would have tod do it manually everytime I change the info.... I would prefer it if I didn´t have to do it manually everytime I change the data.

Also, to answer the first post, I don´t want any subtotals, I just want a list of the UNIQUE values in column A.

Thanks everyone

 

[Joe C]

What I have done in the past is convert to numbers,
Then use the Largest or smallest functtion, and then a countif function to find each unique record, and by countif os thae column for the largest, I know that 1+countif will be the next record.

 

[sailepaty]

How do you change or update the source data?

 

[juajar]

...

I copy the data from another worksheet and then paste as a value into the sheet on the example

 

[sailepaty]

Ok, you can try this.

1.- Create a dynamic range Insert->Name->Define
a) Name = rngSource
b) in the reference it could be =OFFSET(!$A$1,0,0,COUNTA(!$A:$A),1) change it as your requirements.

2.- Insert the following macro in the sheet you copied the data

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("rngSource")) Is Nothing Then
        Range("rngSource").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("P1"), Unique:=True
    End If
End Sub

Again changes the destination range (“P1”) as you need.

I hope it helps.

 

[HalfAce]

If you want to simply loop down through column A of sheet1 and copy one instance of each
value to (for example, column A of sheet2) then this code will do that for you.

Sub CopyUniqueList()

Dim Rng As Range, List As Object, Rw As Long, CpyRng As Range
Set List = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
  For Each Rng In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
    If Not List.Exists(Rng.Value) Then
      List.Add Rng.Value, Nothing
      If CpyRng Is Nothing Then
        Set CpyRng = Rng
      Else
        Set CpyRng = Union(CpyRng, Rng)
      End If
    End If
  Next
End With

If Not CpyRng Is Nothing Then CpyRng.Copy Sheets("Sheet2").Range("A1")
'///IF YOU WANT THE ENTIRE ROW(S) COPIED THEN REPLACE THE LINE ABOVE _
    WITH THIS
'If Not CpyRng Is Nothing Then CpyRng.EntireRow.Copy Sheets("Sheet2").Range("A1")

Set List = Nothing
End Sub

Took my machine about 0.625 second to work on almost 7000 rows.

[Edit:]
Although, if you're in a real hurry ( ) you could do the same thing using the advanced
filter method (without the named range and all) with this:

Sub CopyUniqueList2()
Range("A3", Range("A" & Rows.Count).End(xlUp)) _
  .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=True
End Sub

This only takes 0.15625 second on my machine.

(AdvancedFilter was a good idea sailepaty.
I hardly ever think to use the built in functions first.)