Combine data

[devilfish]

I'm really not sure if what I'm trying to do is possible. Logically (at least to me), it should be. Let me begin by explaining.

I have 3 worksheets inside my file that I'm trying to work with and incorporate. The worksheets are titled "Referees", "T-Shirts" and "T-Shirt Totals". Each worksheet contains the following information: Team#, Name, and t-shirts sizes from YS - A4X. The range of columns is from A to M. Two worksheets only use 98 rows while my "Referees" worksheet uses 224. Now on to my problem.

What I'm trying to do is combine data from "T-Shirts" and "Referees" based on whether or not information in Column A - Team# for both sheets matches that of columnA - row2 of "T-Shirt Totals". I'm trying to get the "T-Shirt Totals" sheet to scan a range for each size and report back a total if the "Team#" is the same for both "T-Shirts" and "Referees". Each size t-shirt should have it's own total depending on whether the "Referees" and the "T-Shirts" match the "Team#" selected. I need to compile a total for all t-shirt sizes based on each team.

Here's what I'm using:

=IF(AND('T-Shirts'!A2:A98='T-Shirt Totals'!A2,Referees!A2:A224='T-Shirt Totals'!A2), 'T-Shirts'!J2:J98+Referees!J2:J224, "0")

Sometimes for some rows it reports the appropriate number, but most of the time, it doesn't report anything. I might be thinking to "databasey" for Excel, but this file was set-up by my mom for a non-profit youth soccer organization and I'm just trying to streamline it. She has to turn the file in tonight and unfortunately I just got to look at it last night. I'll apologize now for my bulky code. I'm not an advanced programmer. I've included the file because I know that I didn't explain myself properly.

Thanks for any and all help provided!!

Cheers,
DF

[BigBas]

try this:

on the "T-Shirt Total" sheet, in cell C2, enter this formula, and fill it to the other cells in the range:

=INDEX('T-Shirts'!$A$2:$M$98,MATCH('T-Shirt Totals'!$A2,'T-Shirts'!$A$2:$A$98,0),MATCH('T-Shirt Totals'!C$1,'T-Shirts'!$A$1:$M$1,0))+SUMIF(Referees!$A$2:$A$224,'T-Shirt Totals'!$A2,Referees!C$2:C$224)

Let me know if it works.

[devilfish]

try this:

on the "T-Shirt Total" sheet, in cell C2, enter this formula, and fill it to the other cells in the range:

=INDEX('T-Shirts'!$A$2:$M$98,MATCH('T-Shirt Totals'!$A2,'T-Shirts'!$A$2:$A$98,0),MATCH('T-Shirt Totals'!C$1,'T-Shirts'!$A$1:$M$1,0))+SUMIF(Referees!$A$2:$A$224,'T-Shirt Totals'!$A2,Referees!C$2:C$224)

Let me know if it works.

I did as you said and it works much better than my horrible code. However, the sheet isn't providing me with a total of both "T-Shirts" and "Referees" in some fields. It gives me one or the other and in some cases, a total of two, but not all three fields in the "Referees" sheet.

I really, REALLY appreciate the help!!!

*Edit* Removed the file only because it contains contact information which should be private. I totally forgot to remove that information. Not that anyone here would take that information and inappropriately use it, but I need to be sensitive to that material.

[BigBas]

Im not sure that I fully understand what you want to do. The way I have it setup is that it takes the total per team from the "T-Shirts" sheet, and adds that to the total and adds that to the total per team from the referees page.

If you need it to calculate something else, or calculate differently, please let me know and I will try to accomodate.

Ideally, if you told me a few of the cells that are not working correctly, perhaps I can work in reverse to figure out the problem.

[devilfish]

BigBas, I really appreciate the help. The formula that you devised works well, but in some cells, it doesn't seem to add the totals from "T-Shirts" and "Referees". An example is in "T-Shirt Totals", cell I5. There are two referees for BU08-1345A that are AL size t-shirts, but in cell I5, it's not calculating the totals. If you look to cell J5, it's not calculating that size either.

Did I explain that properly? I believe that you're doing what I'd like to do, but it's not computing for some reason. Man do I need to touch-up my Excel knowledge. I feel really dumb right now.

Again, thanks very much for the help!!!

[vane0326]

Here you go just input formula in cell C2 and copy across and copy down.


=SUMPRODUCT(('T-Shirts'!$A$2:$A$98=$A2)*('T-Shirts'!$C$1:$M$1=C$1)*('T-Shirts'!$C$2:$M$98))+SUMPRODUCT((Referees!$A$2:$A$224=$A2)*(Referees!$C$1:$M$1=C$1)*(Referees!$C$2:$M$224))

[devilfish]

Here you go just input formula in cell C2 and copy across and copy down.


=SUMPRODUCT(('T-Shirts'!$A$2:$A$98=$A2)*('T-Shirts'!$C$1:$M$1=C$1)*('T-Shirts'!$C$2:$M$98))+SUMPRODUCT((Referees!$A$2:$A$224=$A2)*(Referees!$C$1:$M$1=C$1)*(Referees!$C$2:$M$224))

Thanks so much!! For almost all the columns, the formula computes correctly. In columns AL and AXL in sheet "T-Shirt Totals", the totals don't match what the other two worksheets total up to. They're only off by a few numbers, so I'm gonna see if I can find where the difference is. Again, thanks so much!!

Just for reference, what's the purpose of $ in the forumula?

[BigBas]

that is weird. I went through, and noticed the mistakes that you noted. However, I re-entered the formulas into the cell, and filled it, and it seems to have worked. I wonder if the formula was copied incorrectly somehow or something. Either way, I have attached the workbook, with the formulas added. I went through some cells, and it looks correct. Let me know.

[devilfish]

that is weird. I went through, and noticed the mistakes that you noted. However, I re-entered the formulas into the cell, and filled it, and it seems to have worked. I wonder if the formula was copied incorrectly somehow or something. Either way, I have attached the workbook, with the formulas added. I went through some cells, and it looks correct. Let me know.

Thanks to you too BigBas!! Your formula works. However, I still have the same inconsistencies in totals for columns I and J in sheet "T-Shirt Totals". Column I is off by 4 and J is off by 12. I'm looking for the difference now. Seriously, thank you very much for your help. I can't begin to tell you how grateful I am.

[vane0326]

Thanks so much!! For almost all the columns, the formula computes correctly. In columns AL and AXL in sheet "T-Shirt Totals", the totals don't match what the other two worksheets total up to. They're only off by a few numbers, so I'm gonna see if I can find where the difference is. Again, thanks so much!!

Just for reference, what's the purpose of $ in the forumula?

Your Welcome. Here is a link for the explanation of the $.


http://www.cpearson.com/excel/relative.htm

[devilfish]

Well, beats me where the differences in the column totals are coming from. I've looked and looked. The only thing I can do I guess is go down each team and see if the totals match up.

Thanks again you guys for all the help!!!! I've added this forum to my favorites and will recommend you guys to anyone!

Cheers,
DF

[BigBas]

I'm not sure if you went with Vane's approach or mine (they both seem to do the job), but I think I found your error as far as my formula is concerned. I opened the file that you have attached and noticed that in the "sumif" part of the equation, the part that says 'T-Shirt Totals'!$A5, you have a space between ' and the T.... that is why that formula doesnt work. If you remove that space, my formula would work. Good luck

[devilfish]

I'm not sure if you went with Vane's approach or mine (they both seem to do the job), but I think I found your error as far as my formula is concerned. I opened the file that you have attached and noticed that in the "sumif" part of the equation, the part that says 'T-Shirt Totals'!$A5, you have a space between ' and the T.... that is why that formula doesnt work. If you remove that space, my formula would work. Good luck

I have both versions, but I don't remember which one I used. I'm very grateful to both of you for taking the time to help me out and correct my code. I'm probably gonna go out and buy a book on Excel programming so that I can learn more. I've already learned a lot just trying to get this formula to work on my own before coming here and now that I'm here, I'll probably be here a lot soaking up the knowledge. I hope you guys don't mind me here.

Cheers,
DF

[devilfish]

Your formula work BigBas, but columns I and J come up a bit short as far as the end data is concerned. I've added those columns up in the "T-Shirts" and "Referees" sheets and column I calculates 4 short and column J calculates 12 short. I have yet to find where the missing sizes went. The rest of "T-Shirt Totals" is correct in the end result.

Is there a reason that it would calculate wrong or leave some data out?

[BigBas]

I think I have found your problem. It seems that you do not have all of the "team #s" listed on your totals page that you do on the other two sheets. For example, BU10-7A is on your referees page, but I do not see it on your Total Page.

Using a Vlookup function, these are the team #s that seem to be missing:

BU10-7A
BU12-7A
BU12-7B
BU14-7A
GU14-18D
GU16-7A
GU19-34A

Let me know if this helps

[devilfish]

I think I have found your problem. It seems that you do not have all of the "team #s" listed on your totals page that you do on the other two sheets. For example, BU10-7A is on your referees page, but I do not see it on your Total Page.

Using a Vlookup function, these are the team #s that seem to be missing:

BU10-7A
BU12-7A
BU12-7B
BU14-7A
GU14-18D
GU16-7A
GU19-34A

Let me know if this helps

BigBas, thank you so much for finding that. It was right in front of my face the entire time! I just called my mom at her work only to find out that some teams have dropped but have not yet been taken off the list. That's probably where some of those teams have gone. We're gonna sit down and go over the file tonight. I'm trying to help her get everything automated so that she doesn't have to do it by hand every year.

Thanks so much for your help!!

[BigBas]

Glad I could help!

I just hope it works out for you.