Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Look for item in a list (Excel 2002)

    Hi,
    I'm in wanting to find a formula that will review my data and let me know if the database has a NEW item in it that is not defined in a predefined list I have located in a different sheet. For example, the following items show up in my long database:

    HEALTH
    FLEX
    VACATION
    BONUS

    These items may each show up hundreds of times in the large database. I'd like a formula that would tell me if there was a NEW item in my database. I have a LIST of these items in a different sheet, of course listing all the different types of items only ONCE EACH. Every now and then a NEW item shows up in my database and then I have to go and add it to my list (I perform a bunch of vlookup's based on this list, so I can tell they need to be added to the list due to the #N/A that results). I'm trying to create a macro that translates all my data for me, as opposed to the gazillion formulas I used to use, and now my macro STOPS when it can't find a vlookup match... sooo, I thought I'd create a macro to FIRST review my database for these new items, then add them to my list, then run my translation macro. Hope this makes sense... to recap, I was wondering if there was another way to search my database for new items OTHER THAN using a vookup which results in an #N/A... or could I add an ISNA type function in my macro and have the result say "ADD THIS ITEM TO LIST"??????
    Thanks!!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for item in a list (Excel 2002)

    Instead of searching for missing items, you could use Data | Filter | Advanced Filter with the 'Copy to another location' and 'Unique records only' options to regenerate the list of unique items.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Look for item in a list (Excel 2002)

    Ahh... this is an awesome tool... I've used the advanced filter before, but not in this way. I used the data/filter/advance filter, then I used the "filter the list, in place"... highlighted the large database, highlighted my list, then selected the "unique records only" box and the lonely little item that doesn't match anything in my list shows up... this is perfect! I tried the copy to another location and selected the "unique records only" box and it summarized my database and copied them all to the new location... I'm thinking your suggestion was that it would just copy that one little lonely item over... this would be better than what I did... I must be doing something wrong. I'll keep trying.
    Thanks Hans!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for item in a list (Excel 2002)

    I don't think I understand what you're doing. If you need more help, please provide more detailed information.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Look for item in a list (Excel 2002)

    This advanced filter is quite sensitive... I think I've tried it a least 50 times, and I've finally found out what its' sensitivities are. If you look at my macro below, the ranges selected are uniquely defined... what I've found out is that I can't say range( "G2:G65536") OR range("F1:F65536"). The ranges have to be the exact length of the database or the list (no blanks allowed). Also, the field heading/title of the database range is to be left OUT of the range, AND the field heading/title of the lookup list has to IN the range. Without these particulars, the filter does not work. I did finally figure out the "copy to another location" method... it wasn't working originally because of the sensitivities I mentioned above. Okay now that I've rambled on about how I finally got it to work, below is the macro I recorded... since I can NOT use 65,536 in my range (my database will vary in size every time), how can I get both the ranges in the macro below to read the data I want... can I incorporate Range("A65536").End(xlUp).Offset(1, 0) into my macro below somehow?? I tried to incorporate it into the macro code below the recorded macro... it didn't work, but it didn't error out either, so I figured I must be on the right track!! Any help would be awesome!
    Thanks!
    Lana

    RECORDED MACRO
    Range("G2:G1398").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Sheets("vlookup").Range("F1:F76"), Unique:=True


    NEW ATTEMPT
    Range("G2").Select
    Range(Selection, Selection.End(xlDown)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Sheets("vlookup").Range("A65536").End(xlUp).Offset (1, 0), Unique:=True

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for item in a list (Excel 2002)

    You can use something like this:

    Dim lngMaxRow As Long
    Dim lngMaxLookupRow As Long

    lngMaxRow = Range("G65536").End(xlUp).Row
    lngMaxLookupRow = Worksheets("vlookup").Range("A65536").End(xlUp).Ro w

    Range("G2:G" & lngMaxRow).AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=Worksheets("vlookup").Range("A1:A" & lngMaxLookupRow), Unique:=True

    (I'm not sure why your criteria range shifted from column F to column A between attempts - make sure you refer to the correct column)

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Look for item in a list (Excel 2002)

    Thanks Hans... I need to switch gears off of the macro, and back to the logic... I've attached a worksheet example of what I'm trying to do... I thought I got the advanced filter to work (manually, before I recorded the macro) and I was wrong... it wasn't doing what I thought. Anyway, I've attached a worksheet with an example (which is what I should have done in the first place) and it has a note in it explaining my questions/thoughts... I think maybe I'm trying to get the advanced filter to do something it can't?? I'm confused now.
    Thanks!!
    Lana
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for item in a list (Excel 2002)

    Is your end goal to determine if there are new items in the database list that are not in the criteria list (and if so, which ones they are)?
    Or is your end goal to update the criteria list so that it contains all unique items from the database list?

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Look for item in a list (Excel 2002)

    My end goal is to determine if there are new items in the database list that are not in the criteria list (and if so, which ones they are). And as you'd suspect... I'd like to utilize a macro to find/identify these "unlisted" items.

    Thanks!
    Lana

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for item in a list (Excel 2002)

    The attached version contains a macro that first creates a list of all unique items from column C on the database sheet, then removes the items that occur in the criteria list. What remains are the unlisted items. I added a command button to call the macro.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Look for item in a list (Excel 2002)

    Works perfect! I can apply this macro not only to this particular project, but many others as well!
    Thanks for the solution/macro Hans, and an evern bigger thanks for your help in teaching me how to use VBA! I've only scratched the surface based on what I've seen you do... I love this stuff!
    Thanks again!
    Lana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •