ExcelBanter - How can you split an Excel file

Are the colors all numbers?

If yes, you could check to see if the max and min of each order is the same. If
it is, then it's one color. If they're different, it's a mixed order.

I put this is D2:
=MAX(IF($A$2:$A$10=A2,$C$2:$C$10))-MIN(IF($A$2:$A$10=A2,$C$2:$C$10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

Now apply Data|filter|autofilter to that column.

Filter to show 0's and copy the visible cells to a new worksheet.

Custom filter to show does not equal 0 and copy those visible cells to another
sheet.

If you have a mixture of text in column C, you could use this array formula:

=SUM(($A$2:$A$10=A2)*(1/SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10=C2))))

And apply the data|filter|autofilter and do the same filtering.
But this time, 1's will mean unique colors and anything else will be mixed.

Mainframe to MSFT wrote:

Be warn! I was a mainframe guy! Used to work with EASYTRIVE, thru which I
could manipulate any file and report in a split seconds.

I have a spread-sheet of orders. Within each order there are line items and
each could be a different, say, color or same color. I need to separate the
orders with the same colors into one file and the mixed ones into another.

e.g.
Order Line # color
100 1 245
100 2 245
100 3 245
110 1 245
110 2 290
120 1 200
120 2 200
150 1 050
150 2 300

Need to put all 100 and 120 in one file and 110 and 150 in another file.

Is there an easy way to do this?

Thank you for th help.

--

Dave Peterson