[Webinar] Learn how to a build a cloud-first strategyRegister Now


excel 2010

Posted on 2014-03-05
Medium Priority
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 668 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 664 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 668 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

864 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