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
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
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.
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.
With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
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
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.