# Solved Capturing Data in Excel

Microsoft Office excel 2003
July 24, 2013 at 13:10:50
Specs: Windows Vista, Pentium 2.6 GHz; 4GB RAM
Data Set in Excel
 Good afternoon all.I have a spreadsheet with data for several sports and the number of players enrolled in various camps. What I need to do is be able to have a totals section at the bottom summarizing the unique sports in the data set and total number of players enrolled for each unique sport. The screen shot outlines best what I'm dealing with. I tried to figure this out by doing some research on the Internet and in Excel help but, I haven't had any success.Any help would be appreciated! Thanks so much!

See More: Capturing Data in Excel

July 25, 2013 at 11:45:20
 I started with a sheet that looks exactly like your Screen Capture.Sports in A1, your list of Sports in A2:A10, Players in B1, your number values in B2:B10Totals in A12I then copied the formula used in the downloaded spreadsheet into A13 and modified it to match your ranges:=IF(SUMPRODUCT((A2:A10<>"")*ISERROR(MATCH(A2:A10,\$A\$12:A12,0)))<>0,INDEX(A2:A20,MATCH(TRUE,ISERROR(IF(ISBLANK(A2:A10),FALSE,MATCH(A2:A10,\$A\$12:\$A12,0))),0),1),"")I entered it using Ctrl-Shift-Enter since it is an array formula. This added the brackets { } around it.I dragged it down to A18 to allow room for 2 new sports and got this:``` A 12 Totals 13 Hockey 14 Baseball 15 Football 16 Soccer 17 18```I modified Mike's SUMIF formula to account for blank cells in Column A and entered this in B13, then dragged it down to B18.=IF(A13="","",SUMIF(\$A\$2:\$A\$10,A13,\$B\$2:\$B\$10))I now have this:``` A B 12 Totals 13 Hockey 575 14 Baseball 48 15 Football 225 16 Soccer 290 17 18```I then replaced Hockey in A9 with Ultimate and ended up with this: ``` A B 12 Totals 13 Hockey 395 14 Baseball 48 15 Football 225 16 Soccer 290 17 Ultimate 180 18```There you go...A live list that updates with changes to your main list for both the Sport names and the Player values.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#1
July 24, 2013 at 13:30:10
 Looks like you need the =SUMIF() function, something like:=SUMIF(A2:A10,"Hockey",B2:B10)We used the text string Hockey, so you need to change this string for your other sports.or=SUMIF(\$A\$2:\$A\$10,A13,\$B\$2:\$B\$10)We used the cell containing the text string, so you need only drag the formula down three more cells and you should have what your looking for.See how that works for you.MIKEhttp://www.skeptic.com/

Report •

#2
July 24, 2013 at 13:38:57
 Mike,Thanks very much. That works for the calculation of the total number of players. But, what formula would actually figure out the unique sports and place them in the list under Totals? For example, I listed the 4 unique sports under Totals but, I need a formula that would figure this out for me.Again, any help would be awesome. Thanks.Steve

Report •

#3
July 24, 2013 at 13:45:05
 Using the example you posted, put the formula:=SUMIF(\$A\$2:\$A\$10,A13,\$B\$2:\$B\$10)In cell B13 of your example and drag down three cells, you should get the totals for each sport.Is that what you want, or am I missing something?MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
July 24, 2013 at 13:55:30
 I did that and it works great. But, the sports listed in the Totals area I typed in. But, imagine I have 10 unique sports listed in the data but, 100 rows of data with multiple occurrences of each sport. I want a formula in the Totals section that will figure out the unique sports listed in the data and list them in the Totals section. So, in A13 where it currently has Hockey a formula would figure that out for me. Something that would analyze the data in A2:A10 and then list each unique sport without multiples. I hope I explained it better this time.

Report •

#5
July 24, 2013 at 14:07:52
 You need to do some Advance Filtering.This is for 2007, your using 2003, so you may need to hunt for the correct buttons.As an example, place your cursor in cell D13.Select the Data TabSelect the Advance button under Sort & FilterA small pop up window should appear.Look for the Action: button and change it to:Copy to Another LocationIn the List Range: box enter the range: \$A\$2:\$A\$10Leave the Critera Range: box blankIn the Copy To: box enter you current cell location D13Here is the important part, make sure you Check the box nextto Unique Records OnlyClick OK and you should have your list of unique sports.See how that works.MIKEhttp://www.skeptic.com/

Report •

#6
July 24, 2013 at 14:11:16
 Just a quick note:This filtering is CASE Sensitive, So Hockey is not the same as hockey.MIKEhttp://www.skeptic.com/

Report •

#7
July 24, 2013 at 15:11:56
 Sorry, I don't do this enough to remember all the tricks.If you having problems with the filtering, do a sort of your original lists first.Select column A2:B10 and then SortNow when you do the Advance Filtering you should get only the unique values.MIKEhttp://www.skeptic.com/

Report •

#8
July 24, 2013 at 15:55:34
 A couple of comments on Advanced Filtering....1 - AFAIK, you do not need to sort the original data to get a list of unique values.2 - AFAIK, Advance Filtering is not case sensitive. e.g. with a list like this...```Softball hockey softball Hockey```You will get...```Softball hockey```AF should return the first occurrence of each string, retaining the case of that occurrence, but ignoring all other occurrences, regardless of the case.3 - You may not need this info now, but it's something to keep it in your back pocket:When you use "Filter to another location", you can't push the filtered list to another sheet, but you can pull it to another sheet. If you start the AF on Sheet2, you can reference a List Range on Sheet1 and use a Copy To location on Sheet2.In other words, the location of the filtered list must be the active sheet, but the unfiltered data can be on any sheet.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#9
July 24, 2013 at 16:34:22
 The only way I could get it to filter correctly was by sorting the data first.When I filter your list:``` A 19) Softball 20) hockey 21) softball 22) Hockey ```I get:``` B 19) Softball 20) hockey 21) softball ```Not sure what's going on.Steps:With cell B19 selected.On the Ribbon, Select Data TabClick AdvancedClick Button, Copy to Another LocationSelect Range of cells: \$A\$19:\$A\$22Skip Criteria boxSelect Copy to: Sheet1!\$B\$19Check Box: Unique Records OnlyClick OKWhat am I missing?MIKEhttp://www.skeptic.com/

Report •

#10
July 24, 2013 at 17:58:59
 Sorry, I wasn't very clear. I didn't include the Label in my list.If you select A19:A22 ``` A 19) Softball 20) hockey 21) softball 22) Hockey```...the filter will assume A19 is a label and not data and return exactly what you posted.``` B 19) Softball 20) hockey 21) softball```B19 is a Label. (Select B19 and look in the Name box.)Change the List to this, and filter it:``` A 19 Games 20 Hockey 21 hockey 22 Football 23 football 24 acrobatics 25 Acrobatics ```You should get this:``` B 19 Games 20 Hockey 21 Football 23 acrobatics ```You'll note that the data was not sorted.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#11
July 24, 2013 at 18:30:44
 Now that you explain it, I remember you need a Label, it's not something that is obvious at first glance.But oddly enough, it is part of the error message you get if you do it wrong.Like I said, I don't use this function very often.So now we change the instructions for the OP to:In the List Range: box enter the range: \$A\$1:\$A\$10

Report •

#12
July 24, 2013 at 19:35:08
 Why wouldn't the OP want to use the label?His screencast has a label of Sports in Column A.Use the Sports label above the unique list of sports and Totals or Players above the SUMIF formulas.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#13
July 25, 2013 at 05:17:56
 Use the Sports label above the unique list of sports and Totals or Players above the SUMIF formulas.Sounds like a winner to me.MIKEhttp://www.skeptic.com/

Report •

#14
July 25, 2013 at 06:52:48
 Let's hope that the OP likes the combination of our 2 suggestions.Teamwork!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#15
July 25, 2013 at 07:39:21
 I have to thank both of you big time! This really helped and it worked. Although, ideally I was looking for a solution whereby the "filtering" would be real time. In other words, if a sport was changed from Football to say Wrestling then the unique list below would automatically update. Does that take some macro or VB programming or is there a feature built into Excel that can do this? If that cannot be done then what you both have provided me is great and essentially solves my problem.My additional issue is how do I select a "Set Best Answer" when you both have been great? Can I set two best answers? I doubt it. There should be a way to set best answer AND rate the person for their knowledge and assistance.

Report •

#16
July 25, 2013 at 08:34:00
 To see an example of a "Live Filtered List" using formulas, go to the Data Validation section of this website:http://www.contextures.com/excelfil...and download this file:DataValFlexList.zipPerhaps you can adapt the method used in that spreadsheet.BTW...it can also be done with VBA.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#17
July 25, 2013 at 09:41:15
 I tried it but, couldn't get it to work. All I get is either a blank cell or #NAME? or N/A. Coderre's spreadsheet is a little different as it employs the drop-down List function which I am not doing. I do not have advanced enough knowledge of Excel to understand the mechanics of the formulas. However, what I did understand (input range definition and cell references) I replaced to suit my spreadsheet and unfortunately the result was not successful.Thanks again to both of you for your help with this. I think I'll settle for the Advanced Filter function and not live filtering. Take care.

Report •

#18
July 25, 2013 at 11:45:20
 I started with a sheet that looks exactly like your Screen Capture.Sports in A1, your list of Sports in A2:A10, Players in B1, your number values in B2:B10Totals in A12I then copied the formula used in the downloaded spreadsheet into A13 and modified it to match your ranges:=IF(SUMPRODUCT((A2:A10<>"")*ISERROR(MATCH(A2:A10,\$A\$12:A12,0)))<>0,INDEX(A2:A20,MATCH(TRUE,ISERROR(IF(ISBLANK(A2:A10),FALSE,MATCH(A2:A10,\$A\$12:\$A12,0))),0),1),"")I entered it using Ctrl-Shift-Enter since it is an array formula. This added the brackets { } around it.I dragged it down to A18 to allow room for 2 new sports and got this:``` A 12 Totals 13 Hockey 14 Baseball 15 Football 16 Soccer 17 18```I modified Mike's SUMIF formula to account for blank cells in Column A and entered this in B13, then dragged it down to B18.=IF(A13="","",SUMIF(\$A\$2:\$A\$10,A13,\$B\$2:\$B\$10))I now have this:``` A B 12 Totals 13 Hockey 575 14 Baseball 48 15 Football 225 16 Soccer 290 17 18```I then replaced Hockey in A9 with Ultimate and ended up with this: ``` A B 12 Totals 13 Hockey 395 14 Baseball 48 15 Football 225 16 Soccer 290 17 Ultimate 180 18```There you go...A live list that updates with changes to your main list for both the Sport names and the Player values.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#19
July 25, 2013 at 13:09:35
 Here are some instruction on converting the DataValFlexList.xlsto somthing you could use.It's mostly just Cut & Paste with a bit of editing formulas.Open a new workbookOpen the DataValFlexList.xlsIn the DataValFlexList.xls, select columns A, B, C, & DSelect CopyGo to your new work book and Paste to column AYou should now have new columns A, B, C, & D fromthe DataValFlexList.xls.Next, in your new workbook, select Column D, which is the User Input column and Cutnext, select Column A and Paste.Next, select Column B,which is the List Items column and Cutnext, select Column D and Paste.Select cell A1 and change the heading to: SportSelect cell B1 and input the heading: PlayersSelect cell D1 and change the heading to: Sport ListSelect cell E2 and input TotalsIn cell E3 enter the formula:=IF(D3="","",SUMIF(\$A\$2:\$A\$100,D3,\$B\$2:\$B\$100))NOTE: you will need to modify this if you go beyond 100 rowsin column ANow we need to change at least one of the Named Ranges:In 2003, on the tool bar select Insert, Select Name, DefineThere should be two Named Ranges:First look at the named range: rngUserInputin the Refers To box at the bottom, it should show: =Sheet1!\$A\$2:\$A\$20if it does not show that, change it.Next look at the named range: LU_FlexibleListin the Refers To box at the bottom, were going to delete the current formulaand insert this formula:=Sheet1!\$D\$2:INDEX(Sheet1!\$D:\$D,COUNTIF(Sheet1!\$D:\$D,"?*")+COUNT(Sheet1!\$D:\$D),1)Click Close and were done.Now, hopefully, when you enter a Sport in Column A and the number of players in Column BColumn D will show the sport, and column E the total numberof players.MIKEhttp://www.skeptic.com/

Report •

#20
July 25, 2013 at 13:12:18
 THAT'S IT! DerbyDad that worked like a charm! Thank you so much for the assistance.Mike, you deserve honourable mention for going above and beyond too. I tried to find a way to give you kudos on the site but, it doesn't have any such feature. I do appreciate the help though. You both have gotten me to where I need to get to.Thanks!

Report •