Buying/Selling System

  • Hi,
    As you can see on ebay4.xls I have created a little system, but it aint owkring right, as for example I want to lookup what items I ahve bought from sleler 4(id 4), and he/she has more then 1 item. How do I do a look up so I can see each item spereately or froma lsit that each item and its details.
    Could some 1 pls help me on this issue,

    My system can be found at

  • Hi Mo

    generally VLOOKUP will only grab one item out of a list.

    It sounds like you need to use the autofilter on your data rather than VLOOKUP.


    There are three types of people in this world.
    Those who can count and those who can't.

  • You've got the start of a good approach here but you need to re-think your structure. There is no need to keep repeating the User ID column.

    Step 1. Go through your tables. Delete blank columns

    Step 2. Delete 0s shown instead of blank records

    Step 3. Apply a dynamic named range to complete tables...say ItemsBought, ItemsSold, Sellers, Buyers (look through the OzGrid site - there are great references to Dynamic Named Ranges).

    Step 4. Set up some VBA Code

    'Read Named Ranges into Arrays
    ArrayBought() = Range _("ItemsBought").value

    ArraySold() = Range _

    ArrayBuyers() = Range _

    ArraySellers() = Range _

    ' Code to display all items bought for say
    ' USER_ID = 2
    ' and display in an area starting from say
    ' Cell A35

    UserID = range("C5").value
    MaxCol = ubound(ArrayBought,2)

    for X = 2 to ubound(ArrayBought,1)
    If ArrayBought(X,1) = UserID then
    For Y = MaxCol
    Range("A35").offset(X,Y) = ArrayBought(X,Y)
    Next Y
    End if
    Next X

    Note: Ubound identifies number of elements in the Array

    Hope this helps

    Robert Hind
    Perth - Western Australia

  • Neales suggestion of an autofilter is also right.

    This can also work very well with vba code

    Robert Hind
    Perth - Western Australia

  • Mo
    It doesn't matter which of the suggested solutions (in previously posted replies) you're going to use you'll need to follow at least steps 1 and 2 from my posting and preferably also Step 3.

    When you've carried out those check out the help files on Pivot Tables and AutoFilter. Next learn about the Match and Index functions. When you've done that if you need more help leave a message.

    Robert Hind
    Perth - Western Australia

  • Mo

    By the way I'm assuming, as I think others are, that you're using this forum because you want to learn and improve your skills and not that you expect a solution on a plate.

    Robert Hind
    Perth - Western Australia

  • Mo
    Was just looking through the messages posted prior to your own post of 3:18pm.

    Every single one of the messages posted provided good, valuable information that should help you work towards your solution…but I sense you're disappointed no one has given you the answer. If you take the time to work through them you will learn a lot and surprise yourself with your new found skills.

    In my opinion you've already conquered two of the more important concepts (Vlookup and Named Ranges) although if you explored Vlookup further you would find it more helpful.

    You've already discovered that (fixed) named ranges won't change in size (which is why you've created a lot of blank entries to fill up the rest of the named ranges). Take the time to learn about Dynamic Named Ranges and you'll be forever grateful. A Dynamic Named Range will expand or shrink as you add or delete entries from a list ..and with no need to "pad out" with blank entries or zeros. There are some excellent instructions on Dynamic Named Ranges on the OzGrid site see:

    It doesn't matter what technique you use (vlookup, autofilter, match, index or pivot tables) you should work with contiguous block of data (ie no blank or empty columns or rows). The same applies VBA Arrays (as opposed to Excel Arrays).

    For the solution you're looking for (returning multiple values matching the same criteria) you're going to find AutoFilter or Excel's built in database functions the best to work with.

    Robert Hind
    Perth - Western Australia

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!