Solved

excel 2010

Posted on 2014-03-05
10
302 Views
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?
0
Comment
Question by:JeffBeall
10 Comments
 

Assisted Solution

by:CraigHerbert25
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.
0
 
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
0
 
LVL 19

Expert Comment

by:regmigrant
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
monitors.xlsx
0
 
LVL 1

Author Comment

by:JeffBeall
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
0
 
LVL 19

Expert Comment

by:regmigrant
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
Copy-of-monitors.xlsx
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

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

Expert Comment

by:regmigrant
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
0
 
LVL 1

Author Comment

by:JeffBeall
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"

do

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?
0
 
LVL 19

Accepted Solution

by:
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
0
 
LVL 1

Author Closing Comment

by:JeffBeall
ID: 39929565
thank you for the help
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now