Dates and Quantity sort
 

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

Dates and Quantity sort
 

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" wrote
in message ...

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

Dates and Quantity sort
 

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


--
foilprint0
------------------------------------------------------------------------
foilprint0's Profile: http://www.excelforum.com/member.php...fo&userid=6245
View this thread: http://www.excelforum.com/showthread...hreadid=507153

Dates and Quantity sort
 

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

Dates and Quantity sort
 

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

Dates and Quantity sort
 

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

Dates and Quantity sort
 

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(B 1)))

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" wrote
in message ...

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