Solved: Formula or Function

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

floydcojacket

Thread Starter
Joined
Jul 4, 2008
Messages
285
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

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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

floydcojacket

Thread Starter
Joined
Jul 4, 2008
Messages
285
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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?
 

floydcojacket

Thread Starter
Joined
Jul 4, 2008
Messages
285
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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?
 

floydcojacket

Thread Starter
Joined
Jul 4, 2008
Messages
285
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.
 

floydcojacket

Thread Starter
Joined
Jul 4, 2008
Messages
285
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

floydcojacket

Thread Starter
Joined
Jul 4, 2008
Messages
285
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top