Forum Discussion
Formula Help for Sorting
Hi all! I am looking to find a formula that will do a multitude of things. One is to sort a column of numbers by the last 2 digits of the values input but by a particular order such as 00-14, 15-29, 30-42. Secondly, once sorted by this grouping, I want to assign it to a particular department based on said sort (Dept 1, 2, 3 and so forth. I have attempted to use the VLookup formula that Google AI gave me to no avail. I received an error. Also with a IF formula and haven't been able to figure out how to make this work. Any help would be greatly appreciated!
2 Replies
You are looking for a single formula that will do all that--both sort and then assign?
You don't really give enough info for a full understanding here, with just three examples. Are you creating (not just assigning) those departments, such that what you're ending up with is a 1:1 ratio between sorted cells and departments? That would simply be a sort.
Anyway, my own approach would be to create "a helper column" which would extract the value of those last two digits from the texts ("00-14" is in fact a text value), doing so with this formula: =VALUE(RIGHT(A4,2)), and then sort on the basis of those extracted values. See the attached.
I chose to sort in descending order just to demonstrate that it changed it.
If you have something more extensive--e.g., multiple and random lines assigned to individual apartments--then come back with a more complete description.
I can give an example. Such as like file numbers that could potentially end in 00-99 but sorted as groups 00-14,15-29,30-42 and so forth. After those are sifted through, I'd like to then assign them a department where they would be assigned, Dept 1, Dept 2 etc based on what group they were sorted by.
IE: 00-14 = assigned to Dept 1. So my end result would be the assigned department
Does that make any sense at all? 😀 I'm a little terrible at trying to describe what's in my brain LOL
