excel 2010

Posted on 2014-02-18
Last Modified: 2014-02-23
I have an excel 2010 document that I would like to sort in a specific way. I have been looking in excel and googleing but haven't found what I am looking for.
One of the columns have data that looks like this


and so on, the 3 digits after the first 3 is what I want to group things by, so in the above example  nic  is what I want to group. And there are several entries, so for example


so I want to group we1, nic, and nad together. Also there is another column that list the monitors attached to these computers, and I only want the 17 monitors. So it would look like

d01we1ds0001a     19
d01we1ds0001a     17
d01nicds0001a       17
d01nicds0002a       17
d01nadds0001a      19
d01nadds0002a      21

so after grouping the data it will look like

d01we1ds0001a     17

d01nicds0001a       17
d01nicds0002a       17

and what would be great would be a final total column

d01we1ds0001a     17        1

d01nicds0001a       17        2
d01nicds0002a       17        

So any ideas how to do this?
Question by:JeffBeall
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 23

Expert Comment

ID: 39867723
You can add a helper column that extract those 3 characters.

E.g. =MID(A2,4,3)  

where A2 contains original code.

Then sort on that column.

And do a normal Autofilter on 17 in the monitors column.

A totals column could be to use this formula:


where column C is where you placed the helper column, and column B is the Monitors column.

Assisted Solution

by:Dave Gould
Dave Gould earned 166 total points
ID: 39867740
You could create a "dummy" column based on the real one but starting from the 4th character. You then sort your spreadsheet based on this column.

ie suppose that your data is in column A1 to A10

create a new cell (for example E1) with the formula    =MID(A1;4;10)     then copy this to cells E2 to E10.
Your new column (E) will now be the same as column A but without the first 3 characters so you can do a normal sort on it.

The formula =MID(A1;4;10)   ===> reference A1, start at char4 and print the next 10 chars.

Expert Comment

ID: 39867761
I had created WB. based on your criteria. see it is useful or not. In that I had slit whole string in 3 parts so you can sort as per your requirement. as well as there is one column of unique values.

Thank You
Independent Software Vendors: 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: 39868766
ok, so the =MID(A2,4,3)  thing works, but I can't seem to group the names in my new column like I want.

I even tried a pivot table but all of the data is treated as one row. for example it's not grouping all of the cells starting with NAD. So the following


each row is seperate, when i would want these two rows together to total all rows starting with NAD

Assisted Solution

itjockey earned 167 total points
ID: 39869642
Change Formula in Cell J2

Open in new window

& Drop down the formula.

See attached.

LVL 23

Accepted Solution

NBVC earned 167 total points
ID: 39870026
If you followed my instructions, and sorted by the new column, they should be grouped together.

Then in the next column use the formula I gave:  =COUNTIFS(C:C,C2,B:B,B2)

where it is assumed column C is the helper column with the MID() formula and column B is the column with the numbers (like 17).  copied down.

Then it should give you the counts of the groups (they will be duplicated for each column C variable).

Author Closing Comment

ID: 39881286
thanks for the help

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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 …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 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