Dates and Quantity sort

[foilprint0]

I have a random list of dates with quantity ordered along with other data in a worksheet.
I would like to transfer the date and quantity ordered only over to another worksheet in date order.
so original sheet will have
cell A6 03/04/05 cell A7 will have 8
cell B6 03/03/05 cell B7 will have 6
cell C6 01/04/05 cell C7 will have 12

In the new sheet i want the resul to show
cell A1 03/03/05 cell A2 will have 6
cell B1 01/04/05 cell B2 will have 12
cell C1 03/04/05 cell C2 will have 8

Can someone show me an example please.
Thanks for any help

[Bob Phillips]

How about

=SMALL(Sheet1!A:A,ROW(A1))

and

=INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"foilprint0" <[email protected]> wrote
in message news:[email protected]...
>
> I have a random list of dates with quantity ordered along with other
> data in a worksheet.
> I would like to transfer the date and quantity ordered only over to
> another worksheet in date order.
> so original sheet will have
> cell A6 03/04/05 cell A7 will have 8
> cell B6 03/03/05 cell B7 will have 6
> cell C6 01/04/05 cell C7 will have 12
>
> In the new sheet i want the resul to show
> cell A1 03/03/05 cell A2 will have 6
> cell B1 01/04/05 cell B2 will have 12
> cell C1 03/04/05 cell C2 will have 8
>
> Can someone show me an example please.
> Thanks for any help
>
>
> --
> foilprint0
> ------------------------------------------------------------------------
> foilprint0's Profile:
http://www.excelforum.com/member.php...fo&userid=6245
> View this thread: http://www.excelforum.com/showthread...hreadid=507153
>

[foilprint0]

Hi thanks for the response

do I paste =SMALL(Sheet1!A:A,ROW(A1)) into A1 on sheet 2

do I paste =INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)) into A2 on sheet 2

[[email protected]]

This is correct for a set of dates arranged vertically which starts in
line 1.

For dates arranged horizontally like in the example (starting in column
A) you need to adapt the functions:

=SMALL(6:6;COLUMN(A6))

=INDEX(7:7;(MATCH(A1;6:6;0)))

Enter the =small function in A1, B1, C1 etc and the =index function in
A2, B2, C2 etc.

Hans

[vezerid]

Is there a chance that you have the same date appear more than once in
the original data row?
If so, the formulas suggested by Bob and Hans might return erroneous
results for the second instance of the same date, they would reproduce
the quantity first appearing for the duplicate date.

A formula to cater for such duplicates would be more complex.

Maybe you can first select your data, copy, and then paste them in
another blank sheet with Edit|Paste Special... clicking the Transpose
checkbox. After using Data|Sort on the transposed table you can
copy/transpose again back to the original sheet.

HTH
Kostis Vezerides

[foilprint0]

Kostis

Thanks for the info, Do you know of a different way to get round the problem?
Thanks for any help

[Bob Phillips]

On sheet 2

A1: =Sheet1!A1
A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1:$A$20),MATCH(0,COUNTIF(A$
1:A1,Sheet1!$A$1:$A$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy A2 down

B1: =IF(ISERROR(SMALL(A:A,ROW(B1))),"",SMALL(A:A,ROW(B1)))

Copy down

C1: =SUMIF(Sheet1!A:A,B1,Sheet1!B:B)

Copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"foilprint0" <[email protected]> wrote
in message news:[email protected]...
>
> Kostis
>
> Thanks for the info, Do you know of a different way to get round the
> problem?
> Thanks for any help
>
>
> --
> foilprint0
> ------------------------------------------------------------------------
> foilprint0's Profile:
http://www.excelforum.com/member.php...fo&userid=6245
> View this thread: http://www.excelforum.com/showthread...hreadid=507153
>