excel 2010
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thank You
Sorting.xlsx
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the help
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.