sorting data

[Ivan]

I have a particular sorting requirement I need help with.

The spread sheet is very simple, basically an inventory file with columns
representing the following data:
Column A: five digit product number
Column B: the production date of that product
(there are other columns representing information that is irrelevant)
The report contains several hundred rows of entries.
Individual product numbers are entered several times with as many different
production dates.
I must use this data to create a report that is sorted FIRSTLY by
production date in ascending order.
However, all product numbers must be contingent. That is to say that when
the first date of a particular product number comes up all the remaining
entries for that product must follow.

Is there a way? Sorry for the naive question. I'm just a warehouse grunt.
Ivan

 

[Earl Kiosterud]

Ivan,

It sounds like you need a two-key sort. Select your table. Be sure all the
rows and columns of your table are selected, not just the two key (product
number and date) columns. It's best if the first row of the selection is
the headings. Data - Sort. Now select the production heading in the first
key box, and the date in the second. THat should do it. Check the results
carefully before you save the file. For more on sorting, read "Sorting in
Excel" at www.smokeylake.com/excel/excel_truths.htm.

 

[Ivan]

No, I've tried that. The main issue is that all the product numbers must
remain in a block.
When one entry for a particular product number comes into play by date, the
rest of the entries of the same product number must be contigent with it,
regardless of the dates.

 

[Ron De Giusti]

Hi Ivan,

From the sounds of it, you will require some custom coding to get what you
would like done or you could use something like MS-Access with some custom
queries.

It does not sound like the built-in sorting functionality of MS-Excel will
do what you are looking for.

Is custom coding of MS-Excel a path you want to take? Is it going to be
worth the effort for the result you are looking for?

Ron

 

[swatsp0p]

It sounds to me that AutoFilter would get you closer. This will allow you
to group all of product number together, manipulate which dates you are
looking at, sort by columns, etc.

What you are asking just doesn't make a lot of sense to me. Maybe I'm
missing something.

Give it a try...

HTH

Bruce

 

[Dave Peterson]

How about this:

(I'm gonna assume that you have headers in row 1)

Insert a new column A.
In the new A1, put:
Min Date

In A2, put this formula:
=TEXT(MIN(IF($B$2:$B$30=B2,$C$2:$C$30)),"yyyy/mm/dd")
Adjust 30 to your last row.
But hit ctrl-shift-enter (instead of just enter).
This is an array formula and if you do it correctly, excel will wrap your
formula with curly brackets {}.

Drag it down your range.

Now sort by the min date field with a secondary sort of Product number and a
tertiary sort of production date.

(You can delete column A if you want when your done--or keep it if you need it
later.)