# 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

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.

#### Attachments

• 20.7 KB Views: 100

#### Keebellah

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,

#### Attachments

• 20.7 KB Views: 410

#### floydcojacket

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
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

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.

#### Keebellah

Hans
... and the attachment?

So sorry.
Trying again.

#### Attachments

• 21.1 KB Views: 183

#### Keebellah

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'?

#### floydcojacket

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.

#### Keebellah

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.

#### Attachments

• 38.1 KB Views: 31

#### floydcojacket

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.

#### Keebellah

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

#### floydcojacket

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
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

#### Center

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.

As Seen On