As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel help

DavoidDavoid Registered User regular
edited July 2011 in Help / Advice Forum
Uh oh, I bit off more than I could chew! I need to figure out some excel formulas and I don't know where to start.

I need it to do the following:
Look through a column in a worksheet for a value that matches the value the user inputs
Displays text found in the 4 cells to the right of that value (the worksheet it needs to look through have product numbers and 4 product categorizations next to them)

I am by no means an excel guy, so I don't know what functions could do this (The fun part will happen after I get this part down, when I need to make a series of cells that take the 4 text values displayed and give outputs based on those values).

Any excel gurus around?

rqv6.png
Davoid on

Posts

  • Inquisitor77Inquisitor77 2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
    edited July 2011
    Sounds like you want to use VLOOKUP. Excel's built-in help should be good enough to explain that one for you. If not, Google is your friend.

    Inquisitor77 on
  • DavoidDavoid Registered User regular
    edited July 2011
    I've actually almost got it using the LOOKUP function, but something is wrong with the results vector, it isn't giving me the result I'm looking for

    It's giving me a result that is actually 2 rows lower than the row I want it to draw from for some reason

    Ah, I get it, I can just offset the result to adjust it manually. How do I adjust it to go upwards? It keeps giving me a reference error if I try a negative

    Hn, there is no problem with negatives when I look it up, I must be doing something wrong


    =LOOKUP($D$1,Catalog!$A:$A,OFFSET(Catalog!$A:$A,0,2))

    So I need it to look for the value in D1 in the first sheet in the A column, then show the result that is 2 columns to the right. However, it displays the results 2 rows below the ones I want as well. Not sure what's going on there.

    Yet an earlier formula worked well

    D1=LOOKUP(C1,Catalog!B:B,Catalog!A:A)

    Input the number in C1, and the product name shows up by looking for the number in column B of the sheet, and then outputing the name next to it from column A (I'm not sure why it works).

    Wow, ok, nevermind, it just seems to be grabbing a row of values randomly. If I change the input value, it just seems to grab the row value from wherever

    And I solved it in a stupid way, got it.

    Now I need it to take the keywords, and give a "True" or "False" statement if certain keywords are present
    The problem now is, some cells I want it to be true if a word is present OR another word is present
    and some cells I want to be true only if more than one keyword is present (that's not too bad, I can just go "=IF(COUNTIFS($A$4:$E$4,"Keyword1",$A$4:$E$4,"Keyword2")>0,"Yes","No")"

    Ah, nevermind, got it. I was doing it a stupid way.

    Fixed code: "=IF(AND($A$4="Physics",$C$4="Wave",OR($B$4="Energy",$B$4="Force")),"Yes","No")"

    I guess this can be locked up. Sorry!

    Davoid on
    rqv6.png
  • schussschuss Registered User regular
    edited July 2011
    Use this:
    VLOOKUP(LookupValue, Columns1-4ofRangeToSearch/ReturnIn, 4, FALSE)
    or =VLOOKUP(D1, Catalog!$A:$D, 4, FALSE)

    schuss on
  • DavoidDavoid Registered User regular
    edited July 2011
    Thanks! It's working now, but I want to fix up the interface a bit

    Before, every applicable item would produce a "yes" or "no" response under a criteria box. Unfortunately, there are like, 200 criteria boxes, and searching through them for "yes" or "no" is a pain.

    Is there a way I can search the criteria matrix and display only the headings (the cell above the yes or no) that meet the "yes" requirement on a separate page?

    I'd also like it if it could check to see if it already has counted a "yes", and would display the next available "yes" in the next cell.

    Is this possible?

    Davoid on
    rqv6.png
  • schussschuss Registered User regular
    edited July 2011
    Pivot Table!
    EDIT - with filter.

    schuss on
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    edited July 2011
    pivot tables give me a boner

    also people look at me like i just summoned a demon from hell to do my bidding when i show them one.

    Dr. Frenchenstein on
  • DavoidDavoid Registered User regular
    edited July 2011
    I will do some research! Thanks! If I get stuck again, I'll come back, but it's coming along nicely.

    Basically, I am checking all the assets we have on a website, and we have some deals with various scientific textbook companies to link our content to their chapters.
    So I have like, 30 textbooks all with various chapters, if you look up an asset number it returns a handful of rough keywords, the sheet should look through the keywords, each textbook chapter has criteria, if the keywords meet the criteria, then I want it to the display the chapter title.

    So far, I have the keyword look up and "yes", "no" criteria working. Now I want to make it easy to read before I move on.

    EDIT: Ah, I figured out how to make it recognize "yes" as the criteria, but it's only showing the sum of yes. How do I make it show the box below the "yes"? (I moved the chapter headings beneath the results headings to make it easier for the table to recognize the criteria.)

    EDITEDIT: Ah, pivot tables don't do that, they just sum, count, etc. Hnnnn....

    Davoid on
    rqv6.png
  • schussschuss Registered User regular
    edited July 2011
    Pull in the categories as the side labels, and drag the yes to a filter condition.

    schuss on
  • DavoidDavoid Registered User regular
    edited July 2011
    Would it help if I uploaded the sheet somewhere? I am not sure I am visualizing it correctly.

    It seems like this is for numeral values, which I am not really looking for at all. For example, if 2 chapters in the textbook are "yes", it seems to give me "2" as my output, but I want the chapter titles those "yes" are attributed to instead.

    Oh I got it to display it, that's cool, but, unfortunately, it doesn't seem to update automatically as the input is changed... hnnnn..

    Fixed that with a macro.

    Last thing to figure out: How I keep the pivot table always looking for "Yes"

    If it comes up that there are no "yes", the pivot table stops searching for it when a new input with "yes" appears

    EDIT: apparently I solved that in an ugly way using a place holder cell that is always set to "yes"


    And now last problem: I don't really know how worksheet coding works, is there a way I can save the workbook so the coding for the worksheet is always present? Right now, if I save then leave, when I re-open it, the code is gone again and I have to recode it.

    aaand figured that out
    Ok! My first serious excel file ready to go! Thanks everyone!

    Davoid on
    rqv6.png
  • BobbleBobble Registered User regular
    edited July 2011
    Well, if you can give us a better idea of the data structure here. I'm not sure how the original data is layed out.

    So the first sheet you worked with went: ID#, word, word, word, word, and those were the five cells you needed to look at, right?

    but I'm not sure what things look like on your next question(s).

    Bobble on
  • DavoidDavoid Registered User regular
    edited July 2011
    Yea, initially I laid it out like that, but I found a system that works, and used some macros to update the pivot tables automatically, now it looks pretty good and gives good data

    This was actually a lot of fun to make, to think about how all the different parts of this "machine" work together as a system to give the outputs based on the keyword inputs.

    Davoid on
    rqv6.png
  • AlphariusAlpharius Registered User regular
    edited July 2011
    I'm late to the party but my 2c is index matching is better than vlookup

    Alpharius on
    Check out my 40k blog: WarHamSandwich
  • schussschuss Registered User regular
    edited July 2011
    pivot tables give me a boner

    also people look at me like i just summoned a demon from hell to do my bidding when i show them one.

    You should see what you can do with tools like Hyperion/Cognos. Data is my bitch.

    EDIT - if you upload a sample of the sheet with fake data in it, we can work through it. Also - Excel 2007-10 or Excel 2003?

    schuss on
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    edited July 2011
    I've used Hyperion, oh i know. my company is picking up Prophix (which is allegedly just like Hyperion), which is hopefully replacing bullshit-ass FRx

    Dr. Frenchenstein on
Sign In or Register to comment.