Sorting data by a predefined List

**camcrazy08** · 08-24-2007, 04:45 PM

All,

Hopefully this is something that can easily be done. I am looking to take a chunk of data (let us say it is in range A2:J103). I want to be able to sort this by a list that I have in another sheet. This list is similar to this:

Division 1
1
2
5
6
7
8
Division 2
3
4
9
12
14
17
Division 3
10
11
13
15
16

The division number it would be referencing is in column A of the worksheet. Is there a way to take my data and sort it based on this criteria? The Division number is something that will not have data returned to it, only a reference when looking at the data. Any thoughts to this? Have I described it well enough?

Also, there may be more than one set of data that comes in under the division number. Will this throw any lists out the window?

**Ikaabod** · 08-24-2007, 06:31 PM

In the attached workbook I've used functions "indirect" and "offset" to accomplish what I think it is you're trying to get at.

Let me know if this is what you are looking for. This can be easily done with a macro as well if you prefer.

**camcrazy08** · 08-25-2007, 02:05 PM

I have enclosed a copy of what I am looking to do.

On the first tab, you will see a slew of data. This file will be 100x longer for a real day. I am looking to be able to sort the data as it looks on Tab 2. If possible, I would want to sort the data where they would be listed in order as defined by that list. So, if I change the list, then the sort changes. If it is a hassle to keep the Plant #### entry in there they can be deleted. Is this possible?

The number we are sorting by would be the first column on sheet 1.

Thank you for any assistance anyone could be. Even an idea of where to go from here would be great. Thank you!

The way that I was currently looking at designing would be to subtotal all of the data, and do a simple VLookup function to copy the info over. This has not worked out well so far...

**FrankBoston** · 08-25-2007, 05:13 PM

My approach uses worksheet functions as follows.

Reorganize sheet 'Sort Define' to hold a list of each tcr with its associated plant number.

Insert an additional col A in the data to receive the plant number according to the search function below. Then, sort the data into order by Plant and TCR.

Once the lookups have calculated, you can "fix" the data values with Paste Special: Values.

A modified workbook is attached.

Please Login or Register  to view this content.

**camcrazy08** · 08-25-2007, 05:39 PM

Thank you.

This is perfect. While it wasn't what I was originally thinking to do, as I sit here and play with the data, it does exactly what I was hoping for. I thank you for your assistance, and thank you for taking the time to help me.

One last thing, what did you mean about "fixing" the data values? Do you mean so that plant # is not based on a formula, or something else?

**FrankBoston** · 08-25-2007, 06:18 PM

If you want to preserve the values that have calcuulated in a range of cells, you can select the range, Copy, and then Paste Special: Values into the same range. The formulas disappear, leaving only the values, which now will not change.

This is useful for efficiency, if you have thousands of cells that don't need to compute anymore, or if you are sorting rows that contain formulas that refer to neighboring rows (a rare case).

The formula for locating the Plant number in your data rows is not sensitive to sorting, so you can leave it as a formula, or make it constant.

You will need to keep the Plant cells as formulas if TCR's can move around from plant to plant, so you can re-sort everything. Of course, you can always put the formulas back if you need them.

I'm glad that this suggestion worked for you.