Results 1 to 8 of 8

Thread: VLOOKUP help....

  1. #1

    VLOOKUP help....



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone, hopefully you can help me with a major headache i'm having at the moment.

    I'm not really a heavy excel user but managed to get landed with creating a workbook to help us perform some semi automatic audit tasks.

    From an auto generated Altiris software report we need to determine if a detected package is:

    A: part of a group that we don't want re-deployed (I've created a sheet called CORE and any that don't get deployed we add them to this sheet)

    and

    B: if it is part of our INSTALL packages it needs to pull up what SCCM package needs applied. (I've created another sheet called SCCM GROUPS and add both the detected package matching the report in one column and the relevant SCCM group in the next column)

    So as an example using this formula =IFERROR(VLOOKUP(A3,CORE!$A$1:$B$4985,2,FALSE)," - ADD THIS - ")

    I can query the CORE sheet and if it finds a match it adds a field which i can then use to filter them out leaving only those apps not on the CORE sheet.

    With this formula in another column i can then query the SCCM sheet and pull in the SCCM package needed =IFERROR(VLOOKUP(A3,CORE!$A$1:$B$4985,2,FALSE)," - ADD THIS - ")

    So i end up with a front screen that looks something like this...

    Click image for larger version. 

Name:	sccm.png 
Views:	9 
Size:	18.8 KB 
ID:	3126

    In the example you can see the fields pulled in to say CORE and the ones pulling in the SCCM group - the top two with #N/A would be apps we need to investigate and decide if its CORE or SCCM.

    Works well enough but as users are free to DL and install anything they like on the estate we have multiple entries for slightly different releases of things like Chrome, Firefox, itunes, spotify etc. etc.

    We only have one corp SCCM packaged version of these things so ideally i want to group these all under one umbrella so i only need to have one 'Firefox' entry instead of 27 different version entries.

    I've tried a few things with wildcards from various other posts/sites but can't get anything to work and not sure if these are specific to Excel 2013 or just i'm doing it wrong?

    Happy to supply a sample sheet if needed.

    Any help much appreciated.
    Last edited by sheppy2000; 2015-02-03 at 03:05 PM.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Not sure if it is easily possible, but I think a sample workbook would be more helpful. Also show the expected results (and why).


  3. #3
    TESTER Software Audit Spreadsheet V2 - Copy.xlsm

    Thanks NBVC - Think that's a sample added.

    The data in Report sheet, column 'APP' is a sample of the report we would have, at the moment i paste that in (100+ lines or so) and the spreadsheet sorts it out based on the 2 formulas details above.

    My life would be a lot easier if i could somehow incorporate 'wildcards' into these lookups (or another method/command if that would be a better way) so that i would only have to enter say 'Adobe Flash Player' in one cell in the SCCM Groups sheet, then add the relevant SCCM package across from it - and it would flaf the same thing for any version of that app - at the moment i need to add every flavor of whatever app i'm dealing with into either the CORE/SCCM sheets so they report corectly, and with a lot of these freeware packages there is inherently loads of slightly different numbered versions of them.

    Thanks again anyone who can assist.

    Scot
    Last edited by NBVC; 2015-02-03 at 06:47 PM. Reason: Fixed spelling of my name

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    If you add "Adobe Flash Player" to your list in column A of SCCM Groups sheet, then you change the below formula in E3 of the Report sheet, you should get the corresponding value from column B.

    =LOOKUP(10^10,SEARCH(" "&'SCCM GROUPS'!$A$1:$A$1435&" "," "&A3&" "),'SCCM GROUPS'!$B$1:$B$1435)

    The formula searches for the substrings in your SCCM Groups sheet within the individual cells of Reports sheet to define a match. Does that work for you?


  5. #5
    Quote Originally Posted by NBVC View Post
    If you add "Adobe Flash Player" to your list in column A of SCCM Groups sheet, then you change the below formula in E3 of the Report sheet, you should get the corresponding value from column B.

    =LOOKUP(10^10,SEARCH(" "&'SCCM GROUPS'!$A$1:$A$1435&" "," "&A3&" "),'SCCM GROUPS'!$B$1:$B$1435)

    The formula searches for the substrings in your SCCM Groups sheet within the individual cells of Reports sheet to define a match. Does that work for you?

    Cheers NBVC that works an absolute treat from the test i can do at home, copied it to F3 and edited the relevant bit of the formula so it pulls in the 2nd 'Machine' SCCM group along with the User one - I'll confirm tomorrow from the office on a larger report as soon as i get in. There shouldn't be any limits on repeating the formula down the rows as things grow yeh?

    All i'm missing now is a similar formula in Column D 'Status', of the Report Sheet to search the CORE sheet and return either a CORE or ADD THIS result so i can filter the results for printing/export/copying and i'd be a very happy dude.

    Thanks for the help NBVC

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    It would be a similar formula with an If error around it

    Eg

    =Iferror(Lookup(...),"Add This")

    Sorry for the brevity. I am posting from a smartphone.

  7. #7
    Quote Originally Posted by NBVC View Post
    It would be a similar formula with an If error around it

    Eg

    =Iferror(Lookup(...),"Add This")

    Sorry for the brevity. I am posting from a smartphone.
    Thanks again NBVC - gave it a try but haven't got the skill set to make it work by simply adding to the code already there :-(

    =IFERROR(VLOOKUP(A10,CORE!$A$1:$B$4985,2,FALSE)," - ADD THIS - ")

    Last attempt was this

    =IFERROR(LOOKUP(10^10,SEARCH(" "&CORE!$A$1:$A$1352&" "," "&A16&" "),CORE!$B$1:$B$1352)," - ADD THIS - ")

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    The last one looks right as far as I can see. Remember that the text in one of the cells of range CORE!$A$1:$A$1352 needs to be an exact substring of the text in A16.

    If you still have issues, please post a sample workbook, but if it worked for the SCCM Group sheet, then it should work here if you are following the same rules.


Posting Permissions

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