excel 2010

Posted on 2014-03-05
Last Modified: 2014-03-14
I feel like a total idiot because I spent an entire day trying to figure out how to group data in an excel document.
I have a column of the departments at my work, and a column of the type of monitor attached to the computers. I want to group all the departments together, and then total all of the different monitor types in each department,
so for example in department 1 if there are 10 17" monitors, and 20 20" monitors. I would like to sort the data like that.
I've tried subtotal, group, pivot tables, consolidate. Nothing I do seems to group the data how I would like it.
So, any ideas how to help a hopeless excel user?
Question by:JeffBeall

Assisted Solution

CraigHerbert25 earned 167 total points
ID: 39907868
How are you representing the data? If you put it in a table with columns "Group" & "Monitor Size" you can highlight the data, go to the Sort option and add two sort criteria. First sort descending by Group, then "add level" to sort descending by Monitor Size.
You cant do this just by sort individual columns. You must go to the sort button under the Data toolbar.
LVL 14

Assisted Solution

by:Zac Harris
Zac Harris earned 166 total points
ID: 39907878
Have you tried putting your data in a table? Doing that will let you "sort" your data in different ways. I have attached an example for reference.example.xlsx
LVL 19

Expert Comment

ID: 39908050
it depends on how the data is laid out - here's two examples, one with a pivot table one with formulas based on a couple of guesses as to layout - if these are no good then some sample data would help.

The pivot table is pretty much default (at least in 2013) the sumifs uses formulas
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.


Author Comment

ID: 39917912
this is what the data looks like - also I had it as a table, but changed it so it is NOT in a table

Department      Old Computer Name      New Computer Name      Monitor
NAD      D01NADD020      D012EADS0004A      HP L1706
2CON      D01ADMD004      D01ADMDS2001A      HP L1702
BIO      D01BIOD008      D01BIODS0001A      SDM-S73
BIO      D01BIOD007      D01BIODS0002A      DELL 1905P
BIO      D01BIOD002      D01BIODS0003A      HP L1710
BIO      D01BIOD010      D01BIODS0004A      HP 1702
BIO      D01BIOD004      D01BIODS0005A      DELL 1708FP
BIO      D01BIOD011      D01BIODS0006A      
CAM      D01CAMW003      D01CAMCS0001A      HP LE2201W
CAM      D01CAMW005      D01CAMCS0002A      HP LE2201W
CAM      D01CAMW006      D01CAMCS0003A      HP LE2201W
CAM      D01CAMW002      D01CAMCS0005A      HP LE2201W
LVL 19

Expert Comment

ID: 39918014
try the attached

I suspect some of your problems might stem from there being a Char(160) at the beginning od the monitor names so I had to manipulate the data a little. If this is close to what you want let me know and I'll re-jig to cope with the non-ascii char if needed

Author Comment

ID: 39918260
regmigrant - I like how you sorted the data. how did you do this? did you use a pivot table?
LVL 19

Expert Comment

ID: 39918616
cut and paste from ee to excel. Then data tab, convert text to table, using space as a delimiter, corrected the headings (new<>Computer<>Name etc). then when I did the monitor column I noticed that the 'HP' etc were indented but when I tried to reformat to remove the left indent it stayed. So I used "=code(left(a14,1))" in helper cell and saw that it returned 160 which is non-ascii so I used "=substitute(a14,char(160),"") to replace them (in a separate block) then 'cut and paste values' to put them back in your table.

Added a column that combined the manufacturer with model number to get back to a combined name then copied THAT  to a separate block and 'data, remove duplicates' to get the unique names, copy that range and paste special, transpose to get a set of columns headed by the monitor names. Did the same (remove duplicates) to get a unique list of departments  then put the countif in the first intersection and copied across and down. - simples :)

I considered a pivot table but it would need a helper column with a countif anyway because pivots are only good at analysing values and with only two dimensions to the data (dept, monitor) it's unnecessary

Author Comment

ID: 39918842
Wow! you know Excel. Did you pick this stuff up over time, or take a class? I've always wanted to

learn about Excel because it seems so powerful.

Also, I take it I have add columns like you did.

"Added a column that combined the manufacturer with model number to get back to a combined name"


you just copy the data from the manufacturer and then the model number into one column? or is there

some fancy Excel thing that does this?
LVL 19

Accepted Solution

regmigrant earned 167 total points
ID: 39918858
the & character will do string addition (called concatenation) so =a1 & b1 with give a result combining two string "HP" & "L2170" to make "HPL2170". in this case I put that in against the first line of the table and then dragged down using the handle at the bottom right which gave me a new column of all the model names. Then I would normally selected the new column, copy and 'paste values' so I had a list which isn't dependent on a formula.

In  this case I didn't do it so you can see the formula in F26 of the spreadsheet I posted, the 'TRIM()' function removes any spaces before and after the string - when doing comparisons excel will fail to match "a" with "a " because the space is there but its frequently  difficult to see - this is why I went to the trouble of removing the leading character (160) from the text so that when I compared it in the countif formula I wouldn't  be confused by a failed match when it looked ok,

I've been using excel and other spreadsheets a long time and mostly I learned from reading the help text, trying stuff out and asking others - it also helped that I had a background in software so I knew what should be possible and common function names such as Trim, left, char, code etc

Author Closing Comment

ID: 39929565
thank you for the help

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question