Sorting based on matching characteristics?

[qwea]

Hi All,

I am wondering if this kind of sorting is possible with an excel formula.

The scenario is as follows:

Each row describes a physical object. Each object has several columns for various physical characteristics. Each object is in one of several numbered bins (1, 2, 3, ...). One column on the end combines the rest of the characteristics into one line {=B2&C2&D2...}

Here's what I would like to happen:

I would like one last column to be a "new bin number" for each object. If the combined characteristics column of a given object is unique in the list, I want "bin new" to not change. If the combined characteristics column of a given object is not unique, but the matching object has the same value for the original bin number as the object in question, then "bin new" again does not change. If, however, there are matching objects in other bins, I need "bin new" for that object to return the lowest "old bin" value of all of the matching objects. Consider the simple example:

mVBbUpP.png
Link: http://i.imgur.com/mVBbUpP.png

From there, I can sort the list by the "new" bin column, and that would preserve the existing list, except matching objects would get bumped up to the earliest instance of that object occurring in the list. In essence, my final sorted list needs to keep the original bin groups together, except in the case of matches where the matched objects in higher bin groups get placed with their lower bin group counterparts. This "new bin" scheme is the clearest way that I see that can accomplish this. I hope that makes sense.


My attempt at a solution:

xls file: binsort.xls

I thought of using arrays. For the example above, I would highlight G2 through G10 and input the following formula:

{=Min(if($E$2:$E$10=E2,$B$2:$B$10))}

and then hit ctrl+shift+enter. The issue is that the =E2 part does not iterate throughout, so it is always comparing the combined values in the 2nd row with itself, and thus returning "1" for every value in the "bin new" column. You can see this in the .xls file provided: column F is what I am trying to reproduce via the function, but column G is what I end up with instead.

That's my issue! Thank you to anyone in advance who tries to help, and please let me know if I can clarify anything or if you have any other questions.

[FDibbins]

Hi quea and welcome to the forum

not every1 can open .png files, myself included. It would be far better if you could upload a sample of your file, showing what you have, and an example of your expected outcome. Thanks
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[qwea]

Thank you for the warm welcome, and for the bit of advice regarding the providing of a sample file. I have edited my original post to include just that!

[FDibbins]

Hi again

I am trying to understand your logic here...

I would like one last column to be a "new bin number" for each object. If the combined characteristics column of a given object is unique in the list, I want "bin new" to not change. If the combined characteristics column of a given object is not unique, but the matching object has the same value for the original bin number as the object in question, then "bin new" again does not change. If, however, there are matching objects in other bins, I need "bin new" for that object to return the lowest "old bin" value of all of the matching objects.

can you walk me through a few examples of how you arrived at the values you did? (keep it simple lol, its getting later here)

[qwea]

@FDibbins - Alrighty! I realize now that I was unclear about what the scenario is. Consider the following: binsort1_unique.xls

I have a bunch of objects in a list. Each object has a unique number (column A), and each object is contained in a bin (column B); as you can see, each bin can hold multiple objects. Each object has several defining characteristics, and in this example I have chosen color and shape (columns C and D).

When all objects are entered in, I want to be able to sort them in various ways. I want to keep the bins in order so that the first objects in the list are still in bin 1, the next objects are still in bin 2, etc. so I first sort by bin and then by whatever else I want. That's all well and good for when every object is unique, as in the above example. However, this changes when I have duplicate items.

Here is another example: binsort1_nonunique.xls

It is similar to the previous example, except now I have duplicate items (objects 1, 3, 5, and 9 are blue circles, for example). When I go to sort, I still want the bins to stay in order, however I need duplicate items to be placed among the bin group containing the earliest instance of that item. For the objects above, it would look something like this: binsort1_nonunique_sorted.xls

I still need the freedom to be able to sort the objects in each bin based on whatever characteristic I want, so having a "bin new" column seems like the best way to help with that. Unique items would keep their old bin number, and duplicate items would be assigned the lowest old bin number of all the items in the matching item set.

So for the unsorted list (the second file in this post), the addition of the "bin new" (column F) would look like this: binsort2_nonunique.xls

And the sorted list (third file here) would look like this: binsort2_nonunique_sorted.xls

Continued on next post due to attachment limit.

[qwea]

My attempted solution first combined all characteristics of each object into a single cell: binsort3_nonunique.xls

Then I attempted to craft a function that would return the "bin new" number of each item. For each item, I imagine comparing its "combined" (column E) value with every other element in column E. If there are no matches, "bin new" = "bin old". If there are matches, it compiles a list of the "bin old" values of each match, and returns the lowest of those values for the "bin new" number of that object. I thought that I could do this with arrays, via the function in my original post, which someone elsewhere helped me out with. By highlighting cells G2 through G10, inputing the function {=Min(if($E$2:$E$10=E2,$B$2:$B$10))}, and hitting ctrl+shift+enter, I thought it would work.

Result: binsort4_nonunique.xls

As you can see, all of the "bin new" values are 1. This is because the condition after the first array "=E2" does not iterate throughout the column. Therefore, the function is comparing E2 to itself throughout the column. I guess I misunderstood how this function would work. Now, I am stuck. I am still not sure if it is possible to do what I want here with a simple function. I do hope, though that this explanation cleared up my question!

[jeffreybrown]

@qwea,

Administrative Note:

• As per Forum Rule #12, please don't quote whole posts unless necessary-- it's just clutter...Thanks.

[FDibbins]

ok I think I get it. I sorted them by...

1st sort = color
2nd sort = shape
3rd sort = Bin old

The 1st few came out exactly the same, but the last few were a bit different to your sort because green (G) comes before red (R)

[qwea]

It's just coincidence due to the small data set, unfortunately. In the real scenario, there are many more bins, many more columns of characteristics, and a large variety of possible characteristics in any given category. In the examples, imagine more unique objects that happen to be blue placed in more "bin old" values: binsort5_nonunique.xls (Note: "bin new" values added for clarity)

Using your sorting method, it would turn out like this:binsort6_nonunique.xls

Rather than something like this: binsort7_nonunique.xls

Without a scheme to relate matched objects and their bins, it gets quite a bit messier the more objects there are and the more characteristics they can take on.

Thank you very much for sticking with me thus far

[qwea]

Hindsight is 20/20, as they say. It turns out the formula works exactly as I wanted it to, however I had been applying it incorrectly. My unfamiliarity with arrays led me to highlight an entire column in order to apply a formula that used arrays, instead of simply ctrl+shift+entering the formula into the first cell of the column and then dragging through to iterate the formula through like with any normal formula. Silly me!

Thank you again, FDibbins; for if this thread had received no attention, I likely would have looked for alternative methods!