Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup Multiple Data Rows | Excel Worksheet Functions | |||
vLookup - display multiple rows | Excel Worksheet Functions | |||
Get Vlookup to look at multiple Rows for the answer | Excel Worksheet Functions | |||
VLOOKUP to return multiple rows | Excel Worksheet Functions | |||
vlookup multiple text rows | Excel Discussion (Misc queries) |