Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Vlookup with more than one result


(!)

Moll45's Avatar
Moll45 Moll45 is offline
Member with 90 posts.
THREAD STARTER
 
Join Date: Jun 2003
Experience: Beginner
17-May-2011, 11:54 AM #1
Question Solved: Vlookup with more than one result
Good afternoon,

I hoping some kind soul can help with my problem.

I've attached an example file so hopefully if I'm explaining anything in words you will be able to have a look at the file

What I would like to do is (not even sure if its possible)

Select a batch number from the drop down list and then have the corresponding data shown in a table below this. there could be upto 10 lines of the same batch number and product amounts.

Heres hoping !

With thanks,

Moll )
Attached Files
File Type: xlsm Test Enquiry screen.xlsm (172.7 KB, 121 views)
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
17-May-2011, 12:13 PM #2
Hi Moll! *waves*

I only have "old" Excel. I see "Batch No: A454545" but that number's not in column A on "BatchList" ...

Where you have ABC123/ABC123/ABC123 in BatchList!A10:A12, are they always grouped together like that?

Also, there's no "Rotn No." column on "BatchList" ...
Moll45's Avatar
Moll45 Moll45 is offline
Member with 90 posts.
THREAD STARTER
 
Join Date: Jun 2003
Experience: Beginner
18-May-2011, 04:28 AM #3
Hi Bomb,

Waving back at ya ! - haven't had to ask for help in a long time - you lot have taught me so much but I just can't get my head around this one. Anyway sorry to take so long in getting back to you..... had visitors last night so was forced to be sociable !!

Sorry about confusing you - I had to keep deleting data from my test file to get it to right size so probably thats from the original original test file if you see what I mean. The file as it is now is how it will appear

Batches aren't always grouped together I could have a list of 400 items and they'd be all mixed up.

Is this what you mean?

Ta,

Moll

p.s. Thanks for taking the time to look at my problem by the way.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
18-May-2011, 11:14 AM #4
I'm still lost.

Sheet3 of the attached is a pivot table to pull the unique values from Sheet2!A:A.

Then there's a little code in the worksheet module for Sheet1 so that when you select from E12 the list under "Data to show here" updates. The list draws from "Ref ID" cos I still don't see "Rotn No.". The main thing is "Batches aren't always grouped together I could have a list of 400 items and they'd be all mixed up" means you will need a code loop of some description.

Hope this moves it forward a bit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$12" Then Exit Sub
If Range("E" & Rows.Count).End(xlUp).Row > 16 Then
Range("E17:E" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents
End If
For Each Cell In Sheets("Sheet2").Range("pt_data")
If Cell = Target Then
Range("E" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, 1)
End If
Next Cell
End Sub
Attached Files
File Type: xls moll.xls (30.0 KB, 35 views)
Moll45's Avatar
Moll45 Moll45 is offline
Member with 90 posts.
THREAD STARTER
 
Join Date: Jun 2003
Experience: Beginner
18-May-2011, 11:54 AM #5
Thanks Bomb,

I'll check this out later on at home.

Sorry if I still confuse you, I tried to amend an old file that had all kinds of stuff in it and on it ) I just should've typed the file again, I means it not as if it had lots of data on it - what a wally eh!!


Thank you,

Moll
Moll45's Avatar
Moll45 Moll45 is offline
Member with 90 posts.
THREAD STARTER
 
Join Date: Jun 2003
Experience: Beginner
19-May-2011, 05:09 AM #6
Hi Bomb,

Thanks for your reply. I know I haven't explained myself fully.
On the last attachment (Moll(1)) What I ideally wanted was to select the batch number from the drop down list and then the table underneath to be populated with the data. i.e. Select batch DEF345 then the 6 columns would be filled with the matching data from the data on Sheet 2

This data (Sheet 2) is also updated everytime the file is run.

Is this possible?

Thanks ever so much

Moll )
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
19-May-2011, 05:29 AM #7
Filling "the 6 columns" -- this one does Rotn No./Product Description/Location.

I can't see where you'd be pulling "Allocated/Frozen/Available" from.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$12" Then Exit Sub
If Range("E" & Rows.Count).End(xlUp).Row > 16 Then
Range("E17:J" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents
End If
For Each Cell In Sheets("Sheet2").Range("pt_data")
If Cell = Target Then
Range("E" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, 1)
Range("E" & Rows.Count).End(xlUp).Offset(, 1) = Cell.Offset(, 3)
Range("E" & Rows.Count).End(xlUp).Offset(, 5) = Cell.Offset(, 16)

End If
Next Cell
End Sub
Attached Files
File Type: xls moll.xls (31.5 KB, 52 views)
Moll45's Avatar
Moll45 Moll45 is offline
Member with 90 posts.
THREAD STARTER
 
Join Date: Jun 2003
Experience: Beginner
24-May-2011, 11:36 AM #8
Hi Bomb,

Thank you - you are my hero. I've finally altered your code to suit my file (and learnt so much while doing it too!).

The only thing thats not working (you just knew there was going to be something else didn't you !) is clearing the previous enquiry - it just adds on to the previous enquiry....... does this make sense?

If I enquire on Prod A I would get three rows of results, if I then enquire on Prod B expecting only 1 result, I get the right answer but just added onto the end of the previous enquiry

I can see a line there to clear contents but nothing happens.

Much appreciated.

Moll )
Moll45's Avatar
Moll45 Moll45 is offline
Member with 90 posts.
THREAD STARTER
 
Join Date: Jun 2003
Experience: Beginner
31-May-2011, 10:21 AM #9
Bomb - can you help ?
Hi bomb,

I can't get the previous row to delete it just keeps adding the new enquiry onto the end - even though I notice the code has a line in it to delete.

Any ideas - what I might have done?

Thanks very very much

Moll 45
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑