Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered our
warehouse on a particular date. Not every date in Column A has a number in
Column B, i.e., items were not received every day. I need a formula for
Sheet 2 that would list ONLY those dates when items were received along with
the number of items that were received on that date.

In sheet 2
For date
A2
=INDEX(Sheet1!$A$2:$A$100,SMALL(IF((Sheet1!$A$2:$A $100<"")*(Sheet1!$B$2:$B$100<""),ROW(INDIRECT("1 :"&ROWS($A$2:$A$100)))),ROWS($1:1)))

For Item
B2
=INDEX(Sheet1!$B$2:$B$100,SMALL(IF((Sheet1!$A$2:$A $100<"")*(Sheet1!$B$2:$B$100<""),ROW(INDIRECT("1 :"&ROWS($A$2:$A$100)))),ROWS($1:1)))

Adjust your ranges to suit your needs
ctrl+shift+enter, not just enter on bothe formula above
Select A2 and B2 and drag all the way down as far as needed

"BALEMAN" wrote:

Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered our
warehouse on a particular date. Not every date in Column A has a number in
Column B, i.e., items were not received every day. I need a formula for
Sheet 2 that would list ONLY those dates when items were received along with
the number of items that were received on that date.

Quick down and dirty.

On Sheet2, Row numbers matching the ones used on Sheet1 (this example
presumes dates/receipt counts start on row 2 of Sheet1)

Ok, on Sheet2, in A2:
=IF(B2<"",Sheet1!A2,"")

on Sheet2, in B2:
=IF(Sheet1!B20,Sheet1!B2,"")

Fill both formulas down the sheet. Rows will be empty where no delivery was
made. You can then Auto Filter for Non-Blanks to pull it all together into a
solid looking group.

"BALEMAN" wrote:

Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered our
warehouse on a particular date. Not every date in Column A has a number in
Column B, i.e., items were not received every day. I need a formula for
Sheet 2 that would list ONLY those dates when items were received along with
the number of items that were received on that date.

On sheet2, A1, input

=IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$30<"",ROW(She et1!$A$1:$A$30),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$30,SMALL(IF(Sheet1!$B$1:$B$30< "",ROW(Sheet1!$A$1:$A$30),""),ROW($A1))))

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

Then copy A1 down and across as far as you need.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"BALEMAN" wrote in message
...
Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered
our
warehouse on a particular date. Not every date in Column A has a number
in
Column B, i.e., items were not received every day. I need a formula for
Sheet 2 that would list ONLY those dates when items were received along
with
the number of items that were received on that date.

I'm away from my company PC right now, but couldn't wait to see if I had any
replies to my query. I'm going to try all three solutions tomorrow.

"Bob Phillips" wrote:

On sheet2, A1, input

=IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$30<"",ROW(She et1!$A$1:$A$30),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$30,SMALL(IF(Sheet1!$B$1:$B$30< "",ROW(Sheet1!$A$1:$A$30),""),ROW($A1))))

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

Then copy A1 down and across as far as you need.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"BALEMAN" wrote in message
...
Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered
our
warehouse on a particular date. Not every date in Column A has a number
in
Column B, i.e., items were not received every day. I need a formula for
Sheet 2 that would list ONLY those dates when items were received along
with
the number of items that were received on that date.

Bob:

Your formula provided the dates that items came into the warehouse
correctly; but, did not show the number of items that came into the warehouse
on that particular date.

"Bob Phillips" wrote:

On sheet2, A1, input

=IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$30<"",ROW(She et1!$A$1:$A$30),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$30,SMALL(IF(Sheet1!$B$1:$B$30< "",ROW(Sheet1!$A$1:$A$30),""),ROW($A1))))

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

Then copy A1 down and across as far as you need.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"BALEMAN" wrote in message
...
Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered
our
warehouse on a particular date. Not every date in Column A has a number
in
Column B, i.e., items were not received every day. I need a formula for
Sheet 2 that would list ONLY those dates when items were received along
with
the number of items that were received on that date.

I did say copy down AND ACROSS as needed. The formula was constructed to
pick-up extra rows and columns..

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"BALEMAN" wrote in message
...
Bob:

Your formula provided the dates that items came into the warehouse
correctly; but, did not show the number of items that came into the
warehouse
on that particular date.

"Bob Phillips" wrote:

On sheet2, A1, input

=IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$30<"",ROW(She et1!$A$1:$A$30),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$30,SMALL(IF(Sheet1!$B$1:$B$30< "",ROW(Sheet1!$A$1:$A$30),""),ROW($A1))))

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

Then copy A1 down and across as far as you need.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"BALEMAN" wrote in message
...
Sheet 1's Column A is a list of consecutive dates (09/01/2006 thru
12/31/2006). Sheet 1's Column B lists the number of items that entered
our
warehouse on a particular date. Not every date in Column A has a
number
in
Column B, i.e., items were not received every day. I need a formula
for
Sheet 2 that would list ONLY those dates when items were received along
with
the number of items that were received on that date.