Vlookup for all the row?

[neditheg]

Hi, I'm Andrew and I need some help.

(my english sucks I hope you understand my problem)

So .... I have 2 DBs. The first one has 20 000 rows and the second has 45000 rows.
In the first, are the names of the companies and in the second are the company`s phone numbers. This DBs can be matched after a unique code (codice_opec).

I've attached a part of this DBs if you'll watch you'll understand what I want. Please watch my example ... I need an answer.

[vzachin]

not sure if this is what you want

zach

[Aussiebear]

Hi Andrew, Welcome to vbaexpress. Zach's vlookup only provides the first number available. It seems that we might need to have a multi value lookup required here. If you can be patient, someone will be along shortly with an alternative for you to consider

[nedy_03]

tnx guys ... i hope i'll get a solution :)

[mikerickson]

Since the entries in 1st DB are unique, the straight VLookUp should work.

[mdmackillop]

Have a look at this article (http://office.microsoft.com/en-gb/excel/HA012260381033.aspx)
This array formula (enter with Cntrl+Shift+Enter) works for the first number only. Still looking why it fails with the remainder

=IF(ISERROR(INDEX('2nd DB'!$A$1:$C$74,SMALL(IF('2nd DB'!$A$1:$A$74=A2,ROW('2nd DB'!$A$1:$A$74)),ROW('2nd DB'!1:1)),3)),"",INDEX('2nd DB'!$A$1:$C$74,SMALL(IF('2nd DB'!$A$1:$A$74=A2,ROW('2nd DB'!$A$1:$A$74)),ROW('2nd DB'!1:1)),3))

[mdmackillop]

The Row numbering 1:1 needs to reset at each change in codec number, but how to do this?
I'll look at VBA toimorrow.

[neditheg]

not sure if this is what you want

zach



it do not work because i need diferent phone numbers for the same company ... for example .. in the first db i have 2 companies

NrCrt. |codice_opec| company_name| company_owner |company_adress
1 |311 |AAAAA |Albert |Str. 34
2 |625 |BBBBBB |Maria |Str. St. James

in the second db I have 3 or 4 phone numbers for each company so the db looks like :

NrCrt. |codice_opec |Phone
1 |311 |07289987
2 |311 |05576231
3 |311 |09834525
4 |311 |06341261
5 |625 |21156789
6 |625 |86425632
7 |625 |76543352

My final db must look like :

codice_opec| phone |company_name |company_owner |company_adress
311 |07289987 |AAAAA |Albert |Str. 34
311 |05576231 |AAAAA |Albert |Str. 34
311 |09834525 |AAAAA |Albert |Str. 34
311 |06341261 |AAAAA |Albert |Str. 34
625 |21156789 |BBBBBB |Maria |Str. St. James
625 |86425632 |BBBBBB |Maria |Str. St. James
625 |76543352 |BBBBBB |Maria |Str. St. James

hope u get want I want now :)

[mdmackillop]

This creates a new sheet called EndProduct

Sub Test()
Dim ws As Worksheet
Dim LRw As Long
Application.ScreenUpdating = False
Sheets("2nd DB").Copy Before:=Sheets(1)
Set ws = ActiveSheet
ws.Name = "EndProduct"
With ws
'Get last used row
LRw = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("B:D").Delete
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
cel.Offset(, 2).Resize(, 10) = _
Sheets("1st DB").Columns(1).Find(cel).Offset(, 1).Resize(, 10).Value
Next
.Columns("A:L").AutoFit
End With
Application.ScreenUpdating = True
End Sub

[nedy_03]

ub Test()
Dim ws As Worksheet
Dim LRw As Long
Application.ScreenUpdating = False
Sheets("2nd DB").Copy Before:=Sheets(1)
Set ws = ActiveSheet
ws.Name = "EndProduct"
With ws
'Get last used row
LRw = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("B:D").Delete
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
cel.Offset(, 2).Resize(, 10) = _
Sheets("1st DB").Columns(1).Find(cel).Offset(, 1).Resize(, 10).Value
Next
.Columns("A:L").AutoFit
End With
Application.ScreenUpdating = True
End Sub


CODICE_OPEC3113532004136614CAL1966A0966/21240SCIDONE ROCCO0966/413489RC3113532004136614CAL1966A0966/21240SCIDONE ROCCO0966/413489RC3113532004136614CAL1966A0966/21240SCIDONE ROCCO0966/413489RC6257554775547CAM181A081/3305543I CIRCOLO DIDATTICO STATALE DI GIUGLIANO081/3306382NA6257554775547CAM181A081/3305543I CIRCOLO DIDATTICO STATALE DI GIUGLIANO081/3306382NA883103719148800CAM181A081/7577652S.D.G.S.SRL081/5405544NA883103719148800CAM181A081/7577652S.D.G.S.SRL081/5405544NA108889738208561CAM181A081/9368208MATSU****A ELECTRIC INDUSTRIAL CO. LTD UFFICIO DI RAPPRESENTANZA081/5145240SA108889738208561CAM181A081/9368208MATSU****A ELECTRIC INDUSTRIAL CO. LTD UFFICIO DI RAPPRESENTANZA081/5145240SA1283265260267420CAM1824A0824/865961ORSO GIOVANNI0824/817787BN1283265260267420CAM1824A0824/865961ORSO GIOVANNI0824/817787BN1397305448305448BAS1835A0835/542214MUNICIPIO0835/540111MT1397305448305448BAS1835A0835/542214MUNICIPIO0835/540111MT1397305448305448BAS1835A0835/542214MUNICIPIO0835/540111MT1472316927316990CAL1961A0961/904987ARTRADE SRL0961/904029CZ1472316927316990CAL1961A0961/904987ARTRADE SRL0961/904029CZ1481318141318152CAL1961A0961/960966EURO SHOPPING SAS DI PROCOPIO DOMENICO & C.0961/960043CZ1481318141318152CAL1961A0961/960966EURO SHOPPING SAS DI PROCOPIO DOMENICO & C.0961/960043CZ1570335364335367CAL1964A0964/384110SAN PAOLO IMI SPA0964/384622RC1570335364335367CAL1964A0964/384110SAN PAOLO IMI SPA0964/384622RC1570335364335367CAL1964A0964/384110SAN PAOLO IMI SPA0964/384622RC1656356619357002CAL1966A0966/941994MULTIMEDIA POINT' DI NASSO EMANUELA0966/933133RC1656356619357002CAL1966A0966/941994MULTIMEDIA POINT' DI NASSO EMANUELA0966/933133RC1663358156358156CAL1967A0967/23408POSTE ITALIANE SPA0967/537811CZ
this is the result of the code ... and it isn't the good one :)

[nedy_03]

run time error 91

and stil not work

[mdmackillop]

This is what I get.

[neditheg]

so there is no code for this :) I use vlookup function for each column and i'll have my final DB ... but this will take a while :)

tnx guys ... now I have a new demand....

I have the DB with phone numbers .. it looks like this :

311 | 07214356
311 | 09625272
311 | 09615542
625 | 09765622
625 | 09624234

now I want to get this

311 | 07214356 | 09625272 | 09615542
625 | 09765622 | 09624234

.... how can I do this?

:)

[RonMcK]

nedy_03,

Take a look at the following thread, I believe it answers your new question:

http://www.vbaexpress.com/forum/showthread.php?t=18800

Regards,