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

by:Naresh Patel
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
Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar


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

by:Naresh Patel
Naresh Patel 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

Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

732 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