Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 26 Posts

·
Registered
Joined
·
285 Posts
Discussion Starter · #1 ·
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.
 

Attachments

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

Attachments

·
Registered
Joined
·
285 Posts
Discussion Starter · #3 ·
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.
 

·
Trusted Advisor
Joined
·
6,911 Posts
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?
 

·
Registered
Joined
·
285 Posts
Discussion Starter · #5 ·
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.
 

·
Trusted Advisor
Joined
·
6,911 Posts
... and the attachment?
 

·
Trusted Advisor
Joined
·
6,911 Posts
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?
 

·
Registered
Joined
·
285 Posts
Discussion Starter · #9 ·
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.
 

·
Trusted Advisor
Joined
·
6,911 Posts
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.
 

Attachments

·
Registered
Joined
·
285 Posts
Discussion Starter · #11 ·
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.
 

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

·
Registered
Joined
·
285 Posts
Discussion Starter · #13 ·
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.
 

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

·
Registered
Joined
·
38 Posts
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.
 

·
Registered
Joined
·
38 Posts
So you can try looking at the VBA (Alt-F11) and adjusted values for next year but the VBA code relies on the following:
1) That "Sheet1" is where the bowls are, and "Sheet2" are where the conferences are
2) Within Sheet1
a) Name of bowl must start on cell C3
b) List of conferences start on D3 and M3
3) Within Sheet2
a) List of conferences start on A4
b) # of Bowls is in column B (if you're savvy enough, you can remove this requirement from the VBA, but I added it as a check)

You can add/remove bowls/conferences, but you need to adjust some numbers in the VBA. Anyways, hope this is what you were looking for!
 

Attachments

·
Registered
Joined
·
285 Posts
Discussion Starter · #18 ·
Thank you Center for your reply,
however, this is giving me a numerical count. What I am wanting is a way to list in text format the Bowls that were played in by teams of the conference in column A of the same row.
Thanks again.
 
1 - 20 of 26 Posts
Status
Not open for further replies.
Top