Unique list from multiple columns with advanced filter

juanbolas

New Member
Joined
Dec 3, 2014
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Hello

I'm trying to get a unique list of names using the advanced filter but my code is just showing the first unique item.

Can someone help me please?

Thanks in advance

VBA Code:
Sub Proveedores_UniqueList()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long

Set wsSource = ThisWorkbook.Sheets("DB_PDP").Range("PDP_ProveedoresRange")
Set wsTarget = ThisWorkbook.Sheets("Proveedores_UniqueList")

Set rngTopCell = wsSource.Offset(1, 0)
Set rngBottomCell = wsSource.Offset(wsSource.CurrentRegion.Rows.Count - 1, 0)
Set rngEntireRange = Range(rngTopCell, rngBottomCell)

rngEntireRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsTarget.Range("A:A"), Unique:=True

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Can you post a sample of data that you want to show unique names for. There may be alternatives to re-engineering your non working VBA if we can see a representative sample. Use XL2BB and no pictures. Also, please update your profile to show the version of Excel you are using so that we can cater the response to your specific version.
 
Upvote 0
Hello,

Here is the Mini Sheet. The other sheet is blank.

for Mr. Excel 2.xlsm
ABDEFGKLMNOQUVWXYZAAABACADAEAFAG
1134561011121314162021222324252627
2Pedido de Precios
3
4
5Filtrar por
6
7N° de OrdenFecha Prioridad SolicitanteÁrea DescripciónCantidadMonedaPrecio ReferenciaImporteAutorizaEstadoAutorizado porAutorizado fechaProveedor 1Proveedor 2Proveedor 3ObservacionesPedido porPedido fechaPDP#PDP IndexPDP porPDP Fecha
8Aprobado
9
10N° de OrdenFecha Prioridad SolicitanteÁrea DescripciónCantidadMonedaPrecio ReferenciaImporteAutorizaEstadoAutorizado porAutorizado fechaProveedor 1Proveedor 2Proveedor 3ObservacionesPedido porPedido fechaPDP#PDP IndexPDP porPDP Fecha
111dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 1
123dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 2
133dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 3Prov 10
143dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 4Prov 11
154dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 1Prov 12
164dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 6Prov 13Prov 1
174dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 7Prov 14Prov 2
185dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 1Prov 15Prov 3
195dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 9Prov 16Prov 4
205dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 10Prov 17Prov 5
216dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 11Prov 18Prov 6
226dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 12Prov 19Prov 7
236dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
2410dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
2510dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
2611dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
2711dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
2811dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
2912dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
3012dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
3112dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
3213dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
3313dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
3413dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
3514dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
3614dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
3715dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
3815dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
3916dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
4016dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
4117dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
4217dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
43
44
DB_PDP
Cell Formulas
RangeFormula
D1:G1,K1:O1,Q1,U1D1=C1+1
V1:AB1V1=+U1+1
 
Upvote 0
What is Range("PDP_ProveedoresRange")?

What is your expected result for that sample data? Can you post that with XL2BB too please?
 
Upvote 0
What is Range("PDP_ProveedoresRange")?

What is your expected result for that sample data? Can you post that with XL2BB too please?j

Hi,

PDP_Prooevedores is a named range of the used range of thee columns including their headers. The columns are named Proveedored1,Proveedores2, and Proveedores 3.

The output is to start at A2 (A1) on a Sheet named Proveedores_UniqueList.

Thanks again
 
Upvote 0
i´m posting the mini table gain as the formatting got messed up.

by the way the range ("PDP_ProveedoresRange") is to the headers of the section with all the Ptoveedores (suppliers) names.

Thanks

for Mr. Excel 2.xlsm
BDEFGKLMNOQUVWXYZAAABACADAEAFAG
1134561011121314162021222324252627
2Pedido de Precios
3
4
5Filtrar por
6
7N° de OrdenFecha Prioridad SolicitanteÁrea DescripciónCantidadMonedaPrecio ReferenciaImporteAutorizaEstadoAutorizado porAutorizado fechaProveedor 1Proveedor 2Proveedor 3ObservacionesPedido porPedido fechaPDP#PDP IndexPDP porPDP Fecha
8Aprobado
9
10N° de OrdenFecha Prioridad SolicitanteÁrea DescripciónCantidadMonedaPrecio ReferenciaImporteAutorizaEstadoAutorizado porAutorizado fechaProveedor 1Proveedor 2Proveedor 3ObservacionesPedido porPedido fechaPDP#PDP IndexPDP porPDP Fecha
111dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 1
123dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 2
133dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 3Prov 10
143dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 4Prov 11
154dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 1Prov 12
164dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 6Prov 13Prov 1
174dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 7Prov 14Prov 2
185dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 1Prov 15Prov 3
195dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 9Prov 16Prov 4
205dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 10Prov 17Prov 5
216dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 11Prov 18Prov 6
226dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022Prov 12Prov 19Prov 7
236dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
2410dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
2510dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
2611dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
2711dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
2811dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
2912dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
3012dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
3112dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
3213dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
3313dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
3413dd/mm/aaaaNormalMr. SmithOfficeTorta de chocolate2USD00Mr. SmithAprobadoJua11nB7/9/2022
3514dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
3614dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
3715dd/mm/aaaaNormalMr. SmithOfficePintura1USD00Mr. SmithAprobadoJua11nB7/9/2022
3815dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
3916dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
4016dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
4117dd/mm/aaaaNormalMr. SmithOfficePan2USD00Mr. SmithAprobadoJua11nB7/9/2022
4217dd/mm/aaaaNormalMr. SmithOfficeLibros y revistas3USD00Mr. SmithAprobadoJua11nB7/9/2022
DB_PDP
Cell Formulas
RangeFormula
D1:G1,K1:O1,Q1,U1D1=C1+1
V1:AB1V1=+U1+1
 
Upvote 0
the range ("PDP_ProveedoresRange") is to the headers of the section with all the Ptoveedores (suppliers) names.
I'm not certain what you mean by that.
What is the actual range in your post 6 mini-sheet? Perhaps it is X11:Z22?


As far as I can see, you did not address this question
What is your expected result for that sample data? Can you post that with XL2BB too please?
 
Upvote 0
Hello, I did address your question before reposting the mini-sheet.
SQL:

What is Range("PDP_ProveedoresRange")? IT's range X10:Z10 - the headers of the proveedores data I want to search.

What is your expected result for that sample data? Can you post that with XL2BB too please? Here is the mini-sheet of the output

for Mr. Excel.xlsm
A
1Prov 1
2Prov 10
3Prov 11
4Prov 12
5Prov 13
6Prov 14
7Prov 15
8Prov 16
9Prov 17
10Prov 18
11Prov 19
12Prov 2
13Prov 3
14Prov 34
15Prov 4
16Prov 5
17Prov 6
18Prov 7
19Prov 9
Proveedores_UniqueList


it's just plain output to a blank sheet named "Proveedores_UniqueList".

Thanks in advance
 
Upvote 0
What is Range("PDP_ProveedoresRange")? IT's range X10:Z10 - the headers of the proveedores data I want to search.
Hmm, only giving the headers gives us no idea how big the range is to search - but perhaps that can be overcome.

Expected results: I assume that Prov 34 is an error?

Does this have to be vba? What about this formula solution?

juanbolas.xlsm
A
1Prov 1
2Prov 2
3Prov 3
4Prov 4
5Prov 6
6Prov 7
7Prov 9
8Prov 10
9Prov 11
10Prov 12
11Prov 13
12Prov 14
13Prov 15
14Prov 16
15Prov 17
16Prov 18
17Prov 19
18Prov 5
19
Proveedores_UniqueList
Cell Formulas
RangeFormula
A1:A18A1=LET(a,DB_PDP!X11:Z100,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>"")))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top