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

Report •


✔ Best Answer
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:B10
Totals in A12

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

MIKE

http://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?

MIKE

http://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 Tab
Select the Advance button under Sort & Filter

A small pop up window should appear.

Look for the Action: button and change it to:
Copy to Another Location

In the List Range: box enter the range: $A$2:$A$10

Leave the Critera Range: box blank

In the Copy To: box enter you current cell location D13

Here is the important part, make sure you Check the box next
to Unique Records Only

Click OK and you should have your list of unique sports.

See how that works.

MIKE

http://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.

MIKE

http://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 Sort

Now when you do the Advance Filtering you should get only the unique values.

MIKE

http://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 Tab
Click Advanced
Click Button, Copy to Another Location
Select Range of cells: $A$19:$A$22
Skip Criteria box
Select Copy to: Sheet1!$B$19
Check Box: Unique Records Only
Click OK

What am I missing?

MIKE

http://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 <Must Include Label

but, unfortunately this also copies the Label.

So we either just delete the label row
or
instead of doing a Copy to Another Location,
do a Filter the List In-Place.

The Filter in Place does NOT delete any info, it simply hides it.

After the Filter in Place
then Copy just the sports
Paste to location you want

Finally, Click Clear button in the Sort & Filter section to un-hide the data.

How does that sound?


MIKE

http://www.skeptic.com/


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.

MIKE

http://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.zip

Perhaps 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
✔ Best Answer
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:B10
Totals in A12

I 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.xls
to somthing you could use.

It's mostly just Cut & Paste with a bit of editing formulas.

Open a new workbook

Open the DataValFlexList.xls

In the DataValFlexList.xls, select columns A, B, C, & D
Select Copy

Go to your new work book and Paste to column A

You should now have new columns A, B, C, & D from
the DataValFlexList.xls.

Next, in your new workbook, select Column D,
which is the User Input column and Cut
next, select Column A and Paste.

Next, select Column B,
which is the List Items column and Cut
next, select Column D and Paste.

Select cell A1 and change the heading to: Sport
Select cell B1 and input the heading: Players

Select cell D1 and change the heading to: Sport List
Select cell E2 and input Totals

In 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 rows
in column A

Now we need to change at least one of the Named Ranges:

In 2003, on the tool bar select Insert, Select Name, Define

There should be two Named Ranges:

First look at the named range: rngUserInput
in the Refers To box at the bottom, it should show: =Sheet1!$A$2:$A$20
if it does not show that, change it.

Next look at the named range: LU_FlexibleList
in the Refers To box at the bottom, were going to delete the current formula
and 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 B
Column D will show the sport, and column E the total number
of players.

MIKE

http://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 •


Ask Question