Can I fetch multiple rows back using VLOOKUP?
 

I have been asked to try to fix an excel spreadsheet for my Brother-in-
law. The problem is I knwo very little about Excel really, but don't
want to let him down...

His problem is this...

He has created a spreadsheet with several sheets. The object of this
sheet is to sort out loads for delivery loads etc to clients and
customers. There are 14 sheets with load information and one sheet
which holds all the data.

On this last 15th sheet he types in the following fields down the
page, Load number, Job number, Customer, Town, county, Postcode,
Pallet size, Weight, Delivery method (Van, lorry etc), Distance and
finally Cost.

Each of the 14 load sheets needs to have the following fields
populated using a lookup value to the Load Number on the 15 sheet...

Order number (Job number os sheet15), Customer, Town, County and
Postcode. All other fields on these sheets will be filled in manually.

My question is this...

How do I populate fields in these 14 sheets by looking up the load
number, given that there may be more than one row per load? Tables
shown below...

Sheet15
LoadNo,JobNo,Cust,Town,County,Postcode,PalletSize, ApproxWeight,DelMethod,Distance,Cost
1,1,Stevens,Plymouth,Devon,PL1 2ER,,250,Van1,,£100
2,2,Smith,Ashford,Kent,TN24 9HS,,,Van2,,£150
2,3,Webb,Hornchurch,Essex,RM11 1RS,,,Van2,,£130
2,4,MKM,,London,ec3,,,,,
2,5,Wates,Northolt,Middlesex,ub5 6ag,,,,,
3,6,Wates,Kings Lynn,Norfolk,,,,Park A,,-£92
3,7,Wates,Kings Lynn,Norfolk,,,,,,
4,8,G Hurst,Retford,Notts,dn22 7px,,,Park 7.5 A,180est DR,£180
4,9,G Hurst,Retford,Notts,dn22 7px,,,,,
5,10,Rouco,,Leicester,le2 7pj,,,Park 7.5 B,,

Sheet1-14
LoadNumber (1, 2, 3, 4, etc) EG 2
OrderNumber, Customer, TownCounty,Postcode
2,Smith, Ashford,Kent,TN24,9HS
3,Webb,Hornchurch,Essex,RM11 1RS
4,MKM,,London,EC3
5,Wates,Kings Lynn,Norfolk,

I Hope Ive made sense.
As I said, I dont know excel very well and hope someone out there
knows the answer.

Can I fetch multiple rows back using VLOOKUP?
 

I would use VBA and just scan through sheet 15 and load accordingly

Sub ProcessData()
Dim iLastRow As Long
Dim iLastTargetRow As Long
Dim i As Long
Dim iSheet As Long

With Worksheets("Sheet15")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If IsNumeric(.Cells(i, "A").Value) Then
iSheet = .Cells(i, "A").Value
.Rows(1).Copy Worksheets("Sheet" & iSheet).Range("A1")
iLastTargetRow = Worksheets("Sheet" & iSheet). _
Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(i).Copy Worksheets("Sheet" & iSheet).Range("A" &
iLastTargetRow)
End If
Next i
End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karl Rhodes" wrote in message
oups.com...
I have been asked to try to fix an excel spreadsheet for my Brother-in-
law. The problem is I knwo very little about Excel really, but don't
want to let him down...

His problem is this...

He has created a spreadsheet with several sheets. The object of this
sheet is to sort out loads for delivery loads etc to clients and
customers. There are 14 sheets with load information and one sheet
which holds all the data.

On this last 15th sheet he types in the following fields down the
page, Load number, Job number, Customer, Town, county, Postcode,
Pallet size, Weight, Delivery method (Van, lorry etc), Distance and
finally Cost.

Each of the 14 load sheets needs to have the following fields
populated using a lookup value to the Load Number on the 15 sheet...

Order number (Job number os sheet15), Customer, Town, County and
Postcode. All other fields on these sheets will be filled in manually.

My question is this...

How do I populate fields in these 14 sheets by looking up the load
number, given that there may be more than one row per load? Tables
shown below...

Sheet15
LoadNo,JobNo,Cust,Town,County,Postcode,PalletSize, ApproxWeight,DelMethod,Distance,Cost
1,1,Stevens,Plymouth,Devon,PL1 2ER,,250,Van1,,£100
2,2,Smith,Ashford,Kent,TN24 9HS,,,Van2,,£150
2,3,Webb,Hornchurch,Essex,RM11 1RS,,,Van2,,£130
2,4,MKM,,London,ec3,,,,,
2,5,Wates,Northolt,Middlesex,ub5 6ag,,,,,
3,6,Wates,Kings Lynn,Norfolk,,,,Park A,,-£92
3,7,Wates,Kings Lynn,Norfolk,,,,,,
4,8,G Hurst,Retford,Notts,dn22 7px,,,Park 7.5 A,180est DR,£180
4,9,G Hurst,Retford,Notts,dn22 7px,,,,,
5,10,Rouco,,Leicester,le2 7pj,,,Park 7.5 B,,

Sheet1-14
LoadNumber (1, 2, 3, 4, etc) EG 2
OrderNumber, Customer, TownCounty,Postcode
2,Smith, Ashford,Kent,TN24,9HS
3,Webb,Hornchurch,Essex,RM11 1RS
4,MKM,,London,EC3
5,Wates,Kings Lynn,Norfolk,

I Hope Ive made sense.
As I said, I dont know excel very well and hope someone out there
knows the answer.