excel 2010

JeffBeall used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Zac HarrisSystems Administrator
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
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


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


regmigrant - I like how you sorted the data. how did you do this? did you use a pivot table?
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


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


thank you for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial