?
Solved

excel 2010

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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