1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Formula or Function

Discussion in 'Business Applications' started by floydcojacket, Jan 11, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    What formula or function should I use in order to automatically list all of the bowls for each conference.
    Starting in cell "u 24" and going down, I would like to list all bowl games that the conference listed in cell "u 23" is represented in.
    This will be "clicked and dragged" across to catch all of the other conferences as well.
    Thank you for your help.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I think you should write some vba code, or record a macro that copies the column and past special, transpose and values to the row you need.

    I just did = and cell reference for each that way the values are automatically updated,
    I returned your attached file
     

    Attached Files:

  3. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    Thank you for your reply Keebeelah, but I'm not up to snuff on vba or macro.
    my attached file only shows the numbers of teams represented from ea conference.
    Can you advise how to write a code / vba / etc. in order to list the bowl games played by ea conference?
    Thank you.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I'll do my best but just some questions:
    On row 23 you have the column U values U2 through U13 transposed ( row U23-AE23)
    Which column has to fill row 24? is that V?
    Will the number of rows in Column U vary or is this the maximum? In this case the layout will always remain the same as regrads the tables presently in your sheet, correct?
     
  5. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    1-I'm not sure how to answer your questions, so I have adjusted the attachment.
    2-On sheet #2, I have manually listed the conferences in column A, cells A-3 thru A-13, as well as the bowl games that the SEC was represented in from sheet #1.
    3-What I really wanted is a formula or function that I could use to show all bowl games that each of the other conferences were represented in as well. In cells B-4 thru I-4, all of the bowl games played in by the Big Ten conference will be listed.

    The hardest part for me is wording a question so others may understand what I am asking and I do appologize for this.
    Why am I wanting this? Simply due to the fact that I am so wanting to learn different ways to use Excel.
    Thank you for your help.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    ... and the attachment?
     
  7. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    So sorry.
    Trying again.
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I think we should stick to Sheet1 and th the table
    On that sheet you have a table listing A-R, let's leave the totals you have to rest for now.

    You seem to have created a second table that lists the conferences (unique values taken from column M) of the first table.
    There is a confusion because you have 2 (two) columns named "Conference"

    The table in Sheet2 is based upon 10 'Bowls' in column C (what is the selction of the Big 10 based upon?, the list consists of 35!
    And you want the tabel to show the count of the unique values in column M of sheet 1 under the corresponding 'Bowl'?

    Is this about correct?
     
  9. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    Sheet #1, column C list all the bowl games played this year.
    Sheet #1, columns D & M are the conferences from which the teams are members of that are playing in the bowl games listed in Column C.
    Sheet #2, list each conference in Column A.
    Out to the right side of Cell A-4 in Sheet #2, are all of the bowl games that had a SEC team represented.
    I'd like to have a formula or function if there is one, that I may use to pull all of the bowl games that have teams from cell A-5, (Big Ten) and have them listed in cells B5,B6,B7, etc.
    Then I may use this formula or function for the rest of the conferences listed in column A.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I wrote a simple Function named BowlCount()

    It's in the attached file

    See if this is what you meant, I'm off to bed now so I'll read your answer tomorrow morning.
     

    Attached Files:

  11. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    Thank you for your help Keebellah, but this is not what I'm wanting.

    If you notice in sheet 2, cell A-4, it is "SEC". This an abbreviation for Southeastern Conference.
    Cells B-4, thru K-4, list all of the bowl games that the SEC Conference teams participated in this year. I manually entered these bowls in row 4 so you could get an idea of what I am attempting to accomplish.

    I'm looking to find a way to have Excel list the "bowl games" that each of the other conferences listed in column A have teams playing in. This will be just as the model that I used in row 4, except the others will be in rows 5 thru 15.

    Row 5 will reflect the bowls that the "Big Ten" conference has teams playing in. These bowls will be reflected in cells C-5, D-5, E-5, etc.

    Row 6 will show the bowl games that have teams from the "ACC" conference playing in. These bowls will be reflected in cells C-6, D-6, E-6, etc.

    I'm looking for a "function" or a "formula" that I may use to have Excel perform this feat for me without my manually doing it as I did in row 4, the SEC row.

    Thank you for your help.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Then I think you'll have to look into functions like Index of similiar under the functions menu unde lookup.

    These can do a lot. I write my own functions (vba code like the example) but then I'll have to do some more coding to see if I can figure it out.
    I'll get back to you but ├Żou'll need to be patient, in the meanwhile somebody on the board might look at it too, I'check there for you
     
  13. floydcojacket

    floydcojacket Thread Starter

    Joined:
    Jul 4, 2008
    Messages:
    278
    Thanks again Keebellah,
    I do appreciate your help even tho this querry is not an emergency like others may have.
    In my older age, I suppose I've just started looking for more ways to use Excel.
    I've tried using the Index function previously as well as lookup & match, but to no avail.
    I just really need to learn about macros and vba.
    Thanks again.
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Well may macro use and coding started about 4-5 years ago, I don't know if age is an issue, I'm turning 62 next month and I think it's just a question of logical thinking and a lot of trial and error.
    Recording a macro and then 'disect' it is what started me going and it's fun, I enjoy the challenges and use it intensively at work.
    You'll have to give me a little time to work something out
     
  15. Center

    Center

    Joined:
    Jan 13, 2009
    Messages:
    38
    floydcojacket, I'll take a crack at it later (if it's not resolved). I have stuff at work to complete but in my down time I'll see if I can write VBA that'll do what you need. I think I can do it using COUNTIF's (to count the # of bowls by conference) then using a FOR loop to go through the list.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/973974

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice