excel 2010
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Copy-of-monitors.xlsx
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
ASKER
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)
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you for the help
The pivot table is pretty much default (at least in 2013) the sumifs uses formulas
monitors.xlsx