1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Vlookup with more than one result

Discussion in 'Business Applications' started by Moll45, May 17, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    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 :eek:

    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 :eek:)
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    Hi Moll! :D *waves*

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

    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" ... :confused:
     
  3. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    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.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    I'm still lost. :D

    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:

  5. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    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 :eek:) 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!! :eek:


    Thank you,

    Moll
     
  6. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    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 :eek:)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    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:

  8. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    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 :eek:)
     
  9. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    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:D
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/997371