Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating inventory quantity | Excel Worksheet Functions | |||
Totals by type between dates | Excel Worksheet Functions | |||
Count between dates by type | Excel Worksheet Functions | |||
Inventory sheet to track, order & reduce quantity from master. | New Users to Excel | |||
Make inventory sheet to track, order & reduce quantity from master | Excel Worksheet Functions |