Return a List of Unique Values form another list

juajar

New Member
Joined
Aug 25, 2006
Messages
20
Hi, I have some oinfo which looks like this:
BaseSecundarioSep2006 - VF.xls
ABCDEFGH
2MnemotcnicoEmisorPrecioRentabilidad % EAValor nominal (pesos)Volumen (pesos)TipoTipo de Tasa
3BDCU2999CVADPTO DE CUNDINAMARCA500,000,000605,478,500.00IPC
4BDCU2999CVADPTO DE CUNDINAMARCA1,450,000,0001,633,928,150.00IPC
5BDCU2999CVBDPTO DE CUNDINAMARCA1,000,000,0001,072,001,000.000.00%
6BDCU2999CVBDPTO DE CUNDINAMARCA1,000,000,0001,111,651,000.000.00%
7BDCU2999CVBDPTO DE CUNDINAMARCA200,000,000222,330,200.000.00%
8BDCU2999CVBDPTO DE CUNDINAMARCA200,000,000221,300,200.000.00%
9BDCU2999CVBDPTO DE CUNDINAMARCA200,000,000221,398,200.000.00%
10BDCU2999CVBDPTO DE CUNDINAMARCA500,000,000602,810,500.000.00%
11BDCU2999CVBDPTO DE CUNDINAMARCA500,000,000561,530,500.000.00%
12BDIT03180307BOGOTA D.C.2,000,000,0002,052,854,000.00TF
13BDIT03180307BOGOTA D.C.1,000,000,0001,026,547,000.00TF
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
ABCD
2MnemotcnicoEmisorPrecioRentabilidad % EA
3BDCU2999CVADPTO DE CUNDINAMARCA
4BDCU2999CVBDPTO DE CUNDINAMARCA
5BDIT03180307BOGOTA D.C.
6BDIT04230909BOGOTA D.C.
7BDVT07150510BOGOTA D.C.
8BEPB102B9VAEPM BOGOTA
CONTINUO PUBLICO


Thanks for your help!!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

juajar

New Member
Joined
Aug 25, 2006
Messages
20
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

Well-known Member
Joined
Oct 17, 2002
Messages
841

ADVERTISEMENT

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.
 

juajar

New Member
Joined
Aug 25, 2006
Messages
20

ADVERTISEMENT

...

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

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279
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

Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
Code:
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 ( :LOL: ) you could do the same thing using the advanced
filter method (without the named range and all) with this:
Code:
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.) :unsure:
 

Forum statistics

Threads
1,141,630
Messages
5,707,520
Members
421,512
Latest member
jc364698

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top