[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

excel 2010

Posted on 2014-02-18
7
Medium Priority
?
244 Views
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

d01nicds0001a
d01nicds0002a

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

d01we1ds0001a
d01we1ds0001a
d01nicds0001a
d01nicds0002a
d01nadds0001a
d01nadds0002a

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?
0
Comment
Question by:JeffBeall
[X]
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
7 Comments
 
LVL 23

Expert Comment

by:NBVC
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:

=COUNTIFS(C:C,C2,B:B,B2)

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

Assisted Solution

by:Dave Gould
Dave Gould earned 664 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.
0
 
LVL 8

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
Sorting.xlsx
0
Technology Partners: 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!

 
LVL 1

Author Comment

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

NADds0001a
NADds0002a

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

Assisted Solution

by:Naresh Patel
Naresh Patel earned 668 total points
ID: 39869642
Change Formula in Cell J2
=COUNTIFS($D$1:$D$16,I2,$B$1:$B$16,17)

Open in new window

& Drop down the formula.

See attached.



Thanks
Sorting.xlsx
0
 
LVL 23

Accepted Solution

by:
NBVC earned 668 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).
0
 
LVL 1

Author Closing Comment

by:JeffBeall
ID: 39881286
thanks for the help
0

Featured Post

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!

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

656 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