I have data that is about 2000 rows and 55 columns. 52 of the columms are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting the
times a quantity appears in a dated column). The 52 dated columns have the
quantity ordered but each items is not ordered every week. What I would like
to do is:

For the items that have been ordered only once, find the date in the heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say, "if
the quantity in columm C (the number of times ordered) is equal to 1, look
across the dated columns until a value greater than zero is found and then
copy the date at the head of that column into the cell where the formula is".

Thanks & Happy New Year

Ron R

Try something like this:

D1:J1 = date headers

=IF(C2=1,LOOKUP(10^10,D2:J2,D$1:J$1),"")

Format as DATE

Biff

"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the columms are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting
the
times a quantity appears in a dated column). The 52 dated columns have
the
quantity ordered but each items is not ordered every week. What I would
like
to do is:

For the items that have been ordered only once, find the date in the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say,
"if
the quantity in columm C (the number of times ordered) is equal to 1, look
across the dated columns until a value greater than zero is found and then
copy the date at the head of that column into the cell where the formula
is".

Thanks & Happy New Year

Ron R

OK, so I replaced the 10^10 with the value I put into my test, and it worked.
You've got some skills!
What is the 10^10 meant to do?

"T. Valko" wrote:

Try something like this:

D1:J1 = date headers

=IF(C2=1,LOOKUP(10^10,D2:J2,D$1:J$1),"")

Format as DATE

Biff

"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the columms are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting
the
times a quantity appears in a dated column). The 52 dated columns have
the
quantity ordered but each items is not ordered every week. What I would
like
to do is:

For the items that have been ordered only once, find the date in the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say,
"if
the quantity in columm C (the number of times ordered) is equal to 1, look
across the dated columns until a value greater than zero is found and then
copy the date at the head of that column into the cell where the formula
is".

Thanks & Happy New Year

Ron R

10^10 is 10 to the 10th power or 10,000,000,000

The way that LOOKUP works is if the lookup_value (10^10 or 10,000,000,000)
is not found the result is the LAST numeric value in the range that is less
than the lookup_value. As per the OP, the range would only contain one
numeric entry so that entry is the LAST numeric value in the range and is
more than likely less than the lookup_value.

Basically, the lookup_value (10^10 or 10,000,000,000) is an arbitrarily huge
number that is "guaranteed" to be greater than any numeric value in the
range thus ensuring the desired result.

Biff

"Sean Timmons" wrote in message
...
OK, so I replaced the 10^10 with the value I put into my test, and it
worked.
You've got some skills!
What is the 10^10 meant to do?

"T. Valko" wrote:

Try something like this:

D1:J1 = date headers

=IF(C2=1,LOOKUP(10^10,D2:J2,D$1:J$1),"")

Format as DATE

Biff

"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the columms
are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting
the
times a quantity appears in a dated column). The 52 dated columns have
the
quantity ordered but each items is not ordered every week. What I
would
like
to do is:

For the items that have been ordered only once, find the date in the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say,
"if
the quantity in columm C (the number of times ordered) is equal to 1,
look
across the dated columns until a value greater than zero is found and
then
copy the date at the head of that column into the cell where the
formula
is".

Thanks & Happy New Year

Ron R

Biff,

I see that Sean got it to work by putting in some value but, when I
duplicate your formula with my cells, each response (answer) comes up Dec-25
(which is the last column). What did I do wrong? And, what does the 10^10
do?

Thanks again. You guys Rock!!

Ron

"T. Valko" wrote:

Try something like this:

D1:J1 = date headers

=IF(C2=1,LOOKUP(10^10,D2:J2,D$1:J$1),"")

Format as DATE

Biff

"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the columms are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting
the
times a quantity appears in a dated column). The 52 dated columns have
the
quantity ordered but each items is not ordered every week. What I would
like
to do is:

For the items that have been ordered only once, find the date in the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say,
"if
the quantity in columm C (the number of times ordered) is equal to 1, look
across the dated columns until a value greater than zero is found and then
copy the date at the head of that column into the cell where the formula
is".

Thanks & Happy New Year

Ron R

See my reply to Sean about 10^10.

Here's a small sample file that demonstrates this:

lookup_headers.xls 13.5kb

http://cjoint.com/?bdaBfZcblw

See if that helps.

Biff

"ronnomad" wrote in message
...
Biff,

I see that Sean got it to work by putting in some value but, when I
duplicate your formula with my cells, each response (answer) comes up
Dec-25
(which is the last column). What did I do wrong? And, what does the
10^10
do?

Thanks again. You guys Rock!!

Ron

"T. Valko" wrote:

Try something like this:

D1:J1 = date headers

=IF(C2=1,LOOKUP(10^10,D2:J2,D$1:J$1),"")

Format as DATE

Biff

"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the columms
are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting
the
times a quantity appears in a dated column). The 52 dated columns have
the
quantity ordered but each items is not ordered every week. What I
would
like
to do is:

For the items that have been ordered only once, find the date in the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say,
"if
the quantity in columm C (the number of times ordered) is equal to 1,
look
across the dated columns until a value greater than zero is found and
then
copy the date at the head of that column into the cell where the
formula
is".

Thanks & Happy New Year

Ron R

=INDEX(D1:BD1,MIN(IF(D2:BD2<"",COLUMN(D2:BD2)-COLUMN(D2)+1)))

which is an array formula, so commit with Ctrl-Shift-Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the columms are
headed with a week starting date. The other columns have product names,
number and number of times ordered (this number is attained by counting
the
times a quantity appears in a dated column). The 52 dated columns have
the
quantity ordered but each items is not ordered every week. What I would
like
to do is:

For the items that have been ordered only once, find the date in the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would basically say,
"if
the quantity in columm C (the number of times ordered) is equal to 1, look
across the dated columns until a value greater than zero is found and then
copy the date at the head of that column into the cell where the formula
is".

Thanks & Happy New Year

Ron R