Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple responses from a look up table
maybe some more info on how the data you're looking up is laid out.
-- Gary "mickn74" wrote in message ... I want to key in a number to return multiple responses. Im using a look up a table on a secondary sheet yet the number has multiple responses. What formula do I use or where in help is it. I found look up One value and return multiple corresponding Values yet I need to return multiple worlds not numerical values. Can anoyone point me in the right direction Cheers Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple responses from a look up table
Garry
Thanks for you time more info Sheet 1 entering the data of a post code 4000 on sheet 1, I want it to return QCBD/QBMH and QBMH by typing in a single post code Sheet 2 is the table listed below it needs to search the post code and return multiple values say QCBD/QBMH and QBMH as there are 2 * 4000 postcode 4000 QCBD/QBMH Brisbane 4000 QBMH Spring Hill 4004 QBMH All suburbs 4005 QBMH All suburbs 4006 QBMH/QAGW All suburbs 4007 QSPN All suburbs 4008 QSPN All suburbs I hope that sheds some light on it Cheers, Mick "Gary Keramidas" wrote: maybe some more info on how the data you're looking up is laid out. -- Gary "mickn74" wrote in message ... I want to key in a number to return multiple responses. Im using a look up a table on a secondary sheet yet the number has multiple responses. What formula do I use or where in help is it. I found look up One value and return multiple corresponding Values yet I need to return multiple worlds not numerical values. Can anoyone point me in the right direction Cheers Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple responses from a look up table
here's some basic code you can maybe modify. not sure what you want to do, but
this will find whatever is on sheet1 a1 in sheet2 column a. Option Explicit Sub find_post() Dim ws1 As Worksheet, ws2 As Worksheet Dim rng1 As Range, rng2 As Range, rngfound As Range Dim firstaddress As String Dim i As Long Dim lastrow2 As Long lastrow2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") On Error Resume Next Set rng1 = ws1.Range("A1") Set rng2 = ws2.Range("A1:A" & lastrow2) With rng2 Set rngfound = .Find(What:=rng1, LookIn:=xlValues, lookat:=xlWhole) If Not rngfound Is Nothing Then firstaddress = rngfound.Address Do rngfound.Offset(0, 1).Resize(1, 2).Copy rng1.Offset(i, 1) Set rngfound = .FindNext(rngfound) i = i + 1 Loop Until rngfound.Address = firstaddress End If End With End Sub -- Gary "mickn74" wrote in message ... Garry Thanks for you time more info Sheet 1 entering the data of a post code 4000 on sheet 1, I want it to return QCBD/QBMH and QBMH by typing in a single post code Sheet 2 is the table listed below it needs to search the post code and return multiple values say QCBD/QBMH and QBMH as there are 2 * 4000 postcode 4000 QCBD/QBMH Brisbane 4000 QBMH Spring Hill 4004 QBMH All suburbs 4005 QBMH All suburbs 4006 QBMH/QAGW All suburbs 4007 QSPN All suburbs 4008 QSPN All suburbs I hope that sheds some light on it Cheers, Mick "Gary Keramidas" wrote: maybe some more info on how the data you're looking up is laid out. -- Gary "mickn74" wrote in message ... I want to key in a number to return multiple responses. Im using a look up a table on a secondary sheet yet the number has multiple responses. What formula do I use or where in help is it. I found look up One value and return multiple corresponding Values yet I need to return multiple worlds not numerical values. Can anoyone point me in the right direction Cheers Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with multiple responses | Excel Discussion (Misc queries) | |||
How do I get multiple Vlookup responses? | Excel Worksheet Functions | |||
Multiple responses for a column | Excel Discussion (Misc queries) | |||
Multiple responses from a look up table | Excel Programming | |||
Input Box Asking for Multiple Responses | Excel Programming |