1. Attention: We have put together a thread with tips and a tutorial video to help with using the new software. Please take a moment to check out the thread here: Trapshooters.com Tutorial & Help Video.
    Dismiss Notice

A fun Excel programming challenge (Winston)

Discussion in 'Uncategorized Threads' started by Neil Winston, Jun 6, 2007.

Thread Status:
Not open for further replies.
  1. Neil Winston

    Neil Winston Well-Known Member

    Joined:
    Jan 29, 1998
    Messages:
    7,848
    Minnesota had about a thousand shooters in 2005, about the same in 2006 but some had left, some joined.

    To make it easy, 2005 is on one Excel spreadsheet, ATA numbers only. Same for 2006, on a second spreadsheet.

    The task: make one list of who left, a second list of who joined.

    I did it one way but it's crude and inelegant. Give us the slickest way.

    Show your work.

    The winner will be decided by vote on this thread.

    Neil
     
  2. miketmx

    miketmx Well-Known Member

    Joined:
    Jan 29, 1998
    Messages:
    2,966
    The easy way is to import the Excel files into a Microsoft Access database and the Query wizards would do it. The harder way is to use the Query features of Excel and brush up on your SQL expertise for Inner joins and outer joins. I use to work at this database stuff but ever since my certification as "Brain Dead" I forgot all about it.
     
  3. Pocatello

    Pocatello Active Member

    Joined:
    Jan 29, 1998
    Messages:
    1,547
    Here's my attempt, Neil:

    Let's move the ATA numbers to one sheet just to make it easy, say the 2005 numbers in column A, 2006 numbers in column B. Just for the sake of argument let's suppose there were 2381 shooters in 2005, 1965 shooters in 2006. We are going to use the Excel Match function. In cell C1 enter the following formula:

    =Match(A1,B$1:B$1965,0)

    Next highlight cells C1 through C 2381 and do an Edit - fill down

    What we see in column C is a listing of numbers or #N/A. If we have a number (example - 385) in cell C1, that means that the particular ATA number in cell A1 is found as the entry in cell B385, i.e. that particular ATA number is present both years. If cell C2 has #N/A in it (for example), then the ATA number in cell A2 is not present in the listings in column B, i.e. every #N/A in column C identifies a drop-out from 2005 to 2006.

    Now in cell D1 enter the formula:

    =Match(B1, A$1:A$2381,0)

    Highlight cells D1 through D1965, and do an Edit - fill down. A similar result applies, where the #N/A entries in column D identifies the new shooters for 2006, i.e. those who were members in 2006 but not 2005.

    How does that strike you?
     
  4. Harold

    Harold TS Member

    Joined:
    Jan 29, 1998
    Messages:
    247
    I have MS Access, so the way I would do it would be to import the spreadsheet data into Access, creating two tables, 2005 and 2006. Then I would click on queries and select the New menu then select Find Unmatched Query wizard. Then I would use the wizard to make a query called "2005 without matching 2006." I would repeat the process except selecting the 2006 initially which will create a query called "2006 without matching 2005"
     
  5. joe kuhn

    joe kuhn Furry Lives Matter TS Supporters

    Joined:
    Jan 29, 1998
    Messages:
    7,162
    Location:
    Naperville, IL
    Ok. 2 lists - 2005, 2006. Make one list of who left, a second list of who joined. Each have one field: ATA_NUM.

    I don't know Excel, but in sql the set based solution is:

    Those who left:

    SELECT ATA_NUM FROM 2005 WHERE ATA_NUM NOT IN (SELECT ATA_NUM FROM 2006)

    Those who joined:

    SELECT ATA_NUM FROM 2006 WHERE ATA_NUM NOT IN (SELECT ATA_NUM FROM 2005)

    To be thorough. Those who stayed:

    SELECT ATA_NUM FROM 2006 WHERE ATA_NUM IN (SELECT ATA_NUM FROM 2005)

    This is elegant because sql is a very elegant and powerful language. Don't know if this is adaptable to Excel.

    Joe
     
  6. Neil Winston

    Neil Winston Well-Known Member

    Joined:
    Jan 29, 1998
    Messages:
    7,848
    It should be clear that sql and access and all that are as much cheating as would be using a short-shotstring choke at trap. Next someone will be suggesting: "Pay someone to do it; that's elegant." This is an Excel problem, as the thread's title specifies. Only Excel entries will be considered. That means lumper's entry that seems to be about Excello sponges and Gargoyle's: "Put'em all in AAA and you won't have to worry about sandbaggers anymore!" have both been removed by the administrator as non-germane.

    It's gotta be Excel.

    That's nice, Pocatello, and I didn't know about the MATCH function, but you really haven't quite given be what I asked for, a list of one, a list of the other. You still have them all, just tagged differently, don't you?

    Neil
     
  7. C E Demulling

    C E Demulling Member

    Joined:
    Jan 29, 1998
    Messages:
    88
    Neil, you have mail.
    Start with the list of 2006 Membership numbers in column A on on sheet and the list of 2005 Membership numbers in column A on another sheet. On the third sheet you can get a Joined list and Left list by using the IF Then Else function combined with the Sumif function.
    For the Joined List in the second row enter =IF(SUMIF('2005'!A:A,'2006'!A2,'2005'!A:A)=0,'2006'!A2,0). This give you the number if there is no match in the 2005 list, telling you that it is a new member.
    For the Left List in the second row enter =IF(SUMIF('2006'!A:A,'2005'!A2,'2006'!A:A)=0,'2005'!A2,0). Again this gives you a number if the number is in the 2005 list and not in the 2006 list, meaning that member left.
    Then sort the columns in descending order and you have your membership numbers at the top of each list.
    Enjoy!
     
  8. joe kuhn

    joe kuhn Furry Lives Matter TS Supporters

    Joined:
    Jan 29, 1998
    Messages:
    7,162
    Location:
    Naperville, IL
    I'm not going to say it, but it won't be elegant. The problem is a database problem. Excel is for counting beans. Right problem, wrong tool.

    Say what? Joe
     
  9. joe kuhn

    joe kuhn Furry Lives Matter TS Supporters

    Joined:
    Jan 29, 1998
    Messages:
    7,162
    Location:
    Naperville, IL
    Hey CE.

    I have two worksheets in the same workbook. One called '2005', the other called '2006'. Both have some data as Neil has described.

    I pasted your formula in the first column first cell of the third worksheet and it just sits there. What am I missing?

    Ok, I went back in and took out the equals sign and clicked on the ready made equals sign and a formula editor opened up. Looked ok. I hit enter and nothing happened.

    What am I still missing?

    Joe
     
  10. C E Demulling

    C E Demulling Member

    Joined:
    Jan 29, 1998
    Messages:
    88
    Hi Joe,
    Are the ATA numbers stored as text or as values? They have to stored as values for this to work. If you have an ATA number that is 57-000344 enter it in the list as the number 57000344. Also, put the formula on the second row of the third sheet. Or, I can e-mail it if you want to provide an address.
    Cheryl
    p.s. I am a bean counter.
     
  11. Neil Winston

    Neil Winston Well-Known Member

    Joined:
    Jan 29, 1998
    Messages:
    7,848
    Clearly, both Pocatello and CE know more about Excel than I do. Still - I'm getting to like my method better and better. I'll post it tomorrow.

    Neil
     
  12. Pocatello

    Pocatello Active Member

    Joined:
    Jan 29, 1998
    Messages:
    1,547
    OK, this isn't elegant, Neil, but it does answer your objection:

    Put the header "Number" into cell F1. Copy column A into cells F2 on down. Put the header "Match" into cell G1. Do a copy of column C into cells G2 on down using an Edit - Paste Special - Values. Now highlight columns F and G, including the headers, and do a Data - Sort -Descending on "Match". What results is the ATA numbers in column F, some #N/A and then numbers in column G, and the shooter numbers of those who left are the initial bunch matched with the #N/A. A similar strategy applied to columns B and D gives you the new shooters in 2006.
     
  13. Harold

    Harold TS Member

    Joined:
    Jan 29, 1998
    Messages:
    247
    Here is a modification of Pocatello's first spreadsheet idea. Add a header row and label column A as "2005" and column B as "2006." make columns C and D as stated in pocatello's first post. Create a new column E and label it "Quitter." Enter the formula =iserror(c2) in cell e2 and copy the formula down the column. This will return the value of true for any shooter in the 2005 but not in the 2006 column and false for the matched shooters. Create a new column F and label it New Shooter. Enter the formula =iserror(d2) in cell f2 and copy it down the column. Now if you filter the data by the Quitter column for the value of true it will show only the rows where the shooter in 2005 column is a quitter. Filter for New Shooter = true and the shooters in the 2006 column are the new shooters.
     
  14. Neil Winston

    Neil Winston Well-Known Member

    Joined:
    Jan 29, 1998
    Messages:
    7,848
    I think Harold has taken a big lead with that one. It's short, self-explanatory, interesting.

    Neil
     
  15. Gargoyle!

    Gargoyle! TS Member

    Joined:
    Apr 27, 2007
    Messages:
    980
    Isn't this the typical way Neil handles thing. Make it as hard and or confusing as he can. He does not want to give a straight answer or and answer in black and white for all to see. I still have not forgotten what he said about the ATA money report. "ITS NONE OF YOUR DAMN BUISNESS". He does not want change he does not want us to know the info we ask for. Hell some lawyer and CPA's could not understand what the report was saying. Here again there are people who don't understand what he just put out. Theres a pattern here of you are not going to get what you want in the way of info. Standard Neil W work for ATA members.
     
  16. joe kuhn

    joe kuhn Furry Lives Matter TS Supporters

    Joined:
    Jan 29, 1998
    Messages:
    7,162
    Location:
    Naperville, IL
    CE,

    Converting the column to a number did the trick. Then I started seeing zeroes in the right spot. New ata numbers appear in the correct spot and those who left do too.

    How would you do this if the field was text as in a person's name?

    Millions of people all over the world solve their set based problems with databases, but I suppose if Neil wants to solve his with a number cruncher, that's ok. After all, look what I use to shoot trap with.

    Best Regards. Enjoyed the thread.
     
  17. joe kuhn

    joe kuhn Furry Lives Matter TS Supporters

    Joined:
    Jan 29, 1998
    Messages:
    7,162
    Location:
    Naperville, IL
    Garg - maybe you're just dumb and don't understand anything Neil says. I didn't think this was confusing at all. I thought it was rather well put to tell you the truth. The money report, I've never seen, nor do I care...

    Your post comes off as a personal attack. You leave my friend Neil alone, or the name calling will commence.
     
  18. Pocatello

    Pocatello Active Member

    Joined:
    Jan 29, 1998
    Messages:
    1,547
    Another typical Gargle rant.

    Personally, I think this is one of the most interesting threads I've read here in a long time. I learned about a new (to me) Excel function, Match, in trying to rise to Neil's challenge, and will investigate Harold's ideas further. I agree that his solution is the most elegant so far.
     
  19. handlepuller

    handlepuller Well-Known Member

    Joined:
    Jan 29, 1998
    Messages:
    1,554
    Location:
    St. Augusta, MN
    Thanks for not letting me down Gargoyle(!)!

    I knew you'd throw your lame old "It's none of your damn business" quote into a thread on here sooner or later.

    Get over it and grow up.
     
  20. Ertz

    Ertz Member

    Joined:
    Jan 29, 1998
    Messages:
    289
    Well Neil your idea of "fun" differs from mine. :^) If it was me I would import the spreadsheets into a database and use T-SQL to get whatever information I would like.

    BTW is the communist coming over this summer?
     
Thread Status:
Not open for further replies.