Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Can an Excel guru help me out?

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Can an Excel guru help me out?

Old 06-02-10, 09:45 AM
  #1  
Member
Thread Starter
 
Join Date: Jan 2003
Posts: 162
Likes: 0
Received 0 Likes on 0 Posts
Can an Excel guru help me out?

I'd appreciate any help from someone in the know on Excel. Here's my situation...


Number Demerits
551232 3
551232 1
551232 4
551232 2
551232 5
551232 4
551232 6
551102 2
551102 1
551102 5
551102 2

Column A contains an identifier ID for a person, and column B contains demerits for each incident a person has caused. I'd like to generate a new data set that will tally that data for me... like this:

Number Demerits
551232 25
551102 10

Any ideas on how I could get that to happen? I'm woefully inept on Excel.

Thanks!
Old 06-02-10, 10:07 AM
  #2  
DVD Talk Legend
 
spainlinx0's Avatar
 
Join Date: Dec 2000
Location: NJ
Posts: 15,106
Received 9 Likes on 3 Posts
Re: Can an Excel guru help me out?

Can you just sort by ID number, then run a subtotal, summing up the demerits by ID number?
Old 06-02-10, 10:28 AM
  #3  
Member
Thread Starter
 
Join Date: Jan 2003
Posts: 162
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Hmm... not real sure... I'm dealing with about 8000 rows of data. The subtotal function looks like it may be pretty messy to view with that much data. I'd ideally like to just have a new column created for total demerits that has a single ID and single field for cumulative demerits.

Forgive me if I'm overlooking something obvious with the subtotal function.
Old 06-02-10, 10:37 AM
  #4  
DVD Talk Legend
 
Join Date: Mar 2004
Location: MN
Posts: 23,936
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Just make a pivot table.

(Go to Insert, pivot table. Select the entire range, add Demerits to the value and Number to the Row Labels.)

Last edited by starman9000; 06-02-10 at 10:40 AM.
Old 06-02-10, 11:58 AM
  #5  
Member
Thread Starter
 
Join Date: Jan 2003
Posts: 162
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Originally Posted by starman9000 View Post
Just make a pivot table.

(Go to Insert, pivot table. Select the entire range, add Demerits to the value and Number to the Row Labels.)
Thanks, Starman... that did the trick!
Old 06-02-10, 12:02 PM
  #6  
DVD Talk Legend
 
Join Date: Mar 2004
Location: MN
Posts: 23,936
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

NP, just make sure if you change any of the data, you need to refresh the pivot table to see the updates.
Old 06-03-10, 01:14 PM
  #7  
DVD Talk Ultimate Edition
 
Join Date: Dec 1999
Location: Orlando, FL
Posts: 4,160
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

OK, I have a question too, and I am even more woefully inept with Excel.

I have a list of property records in a single column that I would like to break up into several columns. The list goes:

name
address
city, st
phone


And it repeats those elements, every 4 lines. I would like to put all of those records into 4 columns. Is Excel even the right tool to use for this?
Old 06-03-10, 01:55 PM
  #8  
DVD Talk Hall of Fame
 
Join Date: Sep 2003
Location: Arizona
Posts: 8,003
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

I'm sure there's a much easier way to do this in Excel, but what I would do would be to have all of your data in Column A, then in cell B1 (assuming you have no header rows, so the first name is in cell A1, first address in A2, and so on), paste the following formula: =INDIRECT("A"&1+4*(ROW()-1))

Paste the same formula in cell C1, except change the "1+4" part to read "2+4", then again in D1, changing it to "3+4" and then finally in E1, changing it to "4+4". Then just fill the formulas down each column until they've pulled in all the data. Then copy columns B through E and then Paste-Special as Values, and finally you can delete column A.
Old 06-03-10, 02:01 PM
  #9  
DVD Talk Legend
 
Join Date: Mar 2004
Location: MN
Posts: 23,936
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Never mind, I read that wrong.
Old 06-03-10, 02:02 PM
  #10  
HN
DVD Talk Hall of Fame
 
Join Date: Oct 1999
Location: Los Angeles, CA
Posts: 8,359
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Originally Posted by jdodd View Post
OK, I have a question too, and I am even more woefully inept with Excel.

I have a list of property records in a single column that I would like to break up into several columns. The list goes:

name
address
city, st
phone


And it repeats those elements, every 4 lines. I would like to put all of those records into 4 columns. Is Excel even the right tool to use for this?
Assuming you have everything in column A, and data starts in A1:

1. select cell B1
2. add this formula: =Indirect(Address((Row(A1)-1)*4+Column(A1),1))
3. put cursor over the corner of B2 so that it's a "+" symbol and drag copy over to E1
4. With B1 to E1 still selected, drag copy from there all the way down to however much you want.
5. rows / columns will be populated with column A data
6. extra rows / columns will be populated with '0'. Those will be populated when you add more data into column A.
Old 06-03-10, 02:02 PM
  #11  
DVD Talk Hall of Fame
 
Join Date: Sep 2003
Location: Arizona
Posts: 8,003
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Originally Posted by starman9000 View Post
Copy -> Paste Special -> Transpose.

Try that.
That will just end up with all the data going across in one row instead of being in one column.
Old 06-03-10, 02:06 PM
  #12  
DVD Talk Legend
 
Join Date: Mar 2004
Location: MN
Posts: 23,936
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Yep, I reread it after posting and realized my mistake.
Old 06-03-10, 02:11 PM
  #13  
DVD Talk Hall of Fame
 
Join Date: Sep 2003
Location: Arizona
Posts: 8,003
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Too late, my quote will live on to shame you forever.
Old 06-03-10, 02:13 PM
  #14  
DVD Talk Legend
 
Join Date: Mar 2004
Location: MN
Posts: 23,936
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Well, I just wanted him to try it, I didn't say it would work.
Old 06-03-10, 04:07 PM
  #15  
DVD Talk Ultimate Edition
 
Join Date: Dec 1999
Location: Orlando, FL
Posts: 4,160
Likes: 0
Received 0 Likes on 0 Posts
Re: Can an Excel guru help me out?

Thanks WallyOPD and HN, that worked brilliantly.

First thing I tried was seeing if there was some trick to try with Transpose. Couldn't find anything that wouldn't result in a couple thousand columns on one row.

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.