Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel 2010

Posted on 2014-02-18
7
Medium Priority
?
246 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
  • 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
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.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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