I get regular data runs contain 2 fields, product and date, where unique
combinations thereof amount to batches. the dates can and do change.
Products can move on and off the list.

I am looking for a solution to review the the 2 arrays and determine the
numver of unique combinations and based on chronoligical order of date past
to future return the name of the product and a batch number. "Apples 2"
would be the second of unique combinations of product Apples and date. There
may be 2 there may be 20 combinations, depending on any individual datra run.
And where Product appears without a date I am looking for Product name
followed by "unscheduled" instead of a batch number (foe example; "Apples
Unscheduled"). "Pears 1" would be the first or unique combination for
product Pears.

Thank you in advance!

You know one way you can do that without a macro or an easier macro, you can
highlight the entire column with BANGOR ME and BATON ROUGE, then hit CTRL+G.
Go to Special. Click on Blanks. Then right click on one of the blanks, hit
delete shift upwards

"rjreilly" wrote:

I get regular data runs contain 2 fields, product and date, where unique
combinations thereof amount to batches. the dates can and do change.
Products can move on and off the list.

I am looking for a solution to review the the 2 arrays and determine the
numver of unique combinations and based on chronoligical order of date past
to future return the name of the product and a batch number. "Apples 2"
would be the second of unique combinations of product Apples and date. There
may be 2 there may be 20 combinations, depending on any individual datra run.
And where Product appears without a date I am looking for Product name
followed by "unscheduled" instead of a batch number (foe example; "Apples
Unscheduled"). "Pears 1" would be the first or unique combination for
product Pears.

Thank you in advance!

You're a funny guy.

I get regular data runs contain 2 fields, product and date, where unique
combinations thereof amount to product batches. The dates can and do
change. Also, products can move on and off the list.

I am looking for a solution to review the the 2 arrays and determine the
number of unique combinations of product and dtate per product and, based on
chronoligical order, assign the Batch name (i.e. the product name and a
batch number). For examople, "Apples 2" would be the second of unique
combinations of product Apples and date. There may be 2 there may be 20
combinations, depending on any individual datra run


And where Product appears without a date I am looking for batch name which
would be the Product name followed by "unscheduled" instead of a batch number
(foe example; "Apples
Unscheduled").

"Pears 1" would be the first or unique combination for product Pears.

The goal is to eliminate e wink link in this chain - the soutuion I
currently employ involces a couple steps and will likely lead to future
error, particularly if i need to rely on an others assign batch names. .

Thank you in advance!

"akphidelt" wrote:

You know one way you can do that without a macro or an easier macro, you can
highlight the entire column with BANGOR ME and BATON ROUGE, then hit CTRL+G.
Go to Special. Click on Blanks. Then right click on one of the blanks, hit
delete shift upwards

"rjreilly" wrote:

I get regular data runs contain 2 fields, product and date, where unique
combinations thereof amount to batches. the dates can and do change.
Products can move on and off the list.

I am looking for a solution to review the the 2 arrays and determine the
numver of unique combinations and based on chronoligical order of date past
to future return the name of the product and a batch number. "Apples 2"
would be the second of unique combinations of product Apples and date. There
may be 2 there may be 20 combinations, depending on any individual datra run.
And where Product appears without a date I am looking for Product name
followed by "unscheduled" instead of a batch number (foe example; "Apples
Unscheduled"). "Pears 1" would be the first or unique combination for
product Pears.

Thank you in advance!

A pivot table should do the job you want. It would show unique products down
the left or across the top (you pick), unique dates in the left or top
(forming a grid with your products), then counts of each product for each
date with a separate count for products with no date.

A more convoluted approach is to setup a dedicated grid somewhere on your
sheet and use array formulas. The grid would have unique products across the
top or down the left side (again, up to you) and batch numbers on the
opposing part of the grid (top or left).

Let's say you decide to list products down the left and batch #s across the
top, beginning with G1 as the cell where the left and top of the grid meet.
So H1 has "unscheduled", I1 has the number "1", and so on for batch numbers.
G2 has "Apples", G3 "Pears", etc.

With products in A and dates in B, in H2 you would enter this array formula:
=INDEX($B:$B,SMALL(IF($A$2:$A$1000=$G2,ROW($2:$100 0)),ROW(2:2)))
and commit using Control-Shift-Enter (or CSE) as this is an array formula.

Use your fill handle to drag this down your list of products, then use the
fill handle again to drag it across the 30 or 40 batches you've preset across
the top. Each batch will return the first (earliest) date for that product
up to the latest date. Unfortunately, I'm not sure how it will handle blank
dates.

Now say you only have 3 batches of Apples today. Preset batches 4-40 will
return a #NUM! error. Just conditional format the whole table (H2:Z20, for
example) to "Formula Is" =ISERROR(H2) and change the font to white to make
the errors invisible.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"rjreilly" wrote:

You're a funny guy.

I get regular data runs contain 2 fields, product and date, where unique
combinations thereof amount to product batches. The dates can and do
change. Also, products can move on and off the list.

I am looking for a solution to review the the 2 arrays and determine the
number of unique combinations of product and dtate per product and, based on
chronoligical order, assign the Batch name (i.e. the product name and a
batch number). For examople, "Apples 2" would be the second of unique
combinations of product Apples and date. There may be 2 there may be 20
combinations, depending on any individual datra run


And where Product appears without a date I am looking for batch name which
would be the Product name followed by "unscheduled" instead of a batch number
(foe example; "Apples
Unscheduled").

"Pears 1" would be the first or unique combination for product Pears.

The goal is to eliminate e wink link in this chain - the soutuion I
currently employ involces a couple steps and will likely lead to future
error, particularly if i need to rely on an others assign batch names. .

Thank you in advance!

"akphidelt" wrote:

You know one way you can do that without a macro or an easier macro, you can
highlight the entire column with BANGOR ME and BATON ROUGE, then hit CTRL+G.
Go to Special. Click on Blanks. Then right click on one of the blanks, hit
delete shift upwards

"rjreilly" wrote:

I get regular data runs contain 2 fields, product and date, where unique
combinations thereof amount to batches. the dates can and do change.
Products can move on and off the list.

I am looking for a solution to review the the 2 arrays and determine the
numver of unique combinations and based on chronoligical order of date past
to future return the name of the product and a batch number. "Apples 2"
would be the second of unique combinations of product Apples and date. There
may be 2 there may be 20 combinations, depending on any individual datra run.
And where Product appears without a date I am looking for Product name
followed by "unscheduled" instead of a batch number (foe example; "Apples
Unscheduled"). "Pears 1" would be the first or unique combination for
product Pears.

Thank you in advance!