Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sort with two variables
Hi,
I'm looking for help with a formula. In a file, report.xls A1, I am trying... IF([data.xls]car'!A1="7:00",IF([data.xls]car'!B1="Car",'(car.xls!A3:A5))) The dilemma is the data.xls file will skip some half hour intervals; while the report.xls adds all intervals, even when there are no numbers to report. dilemma #2... Retort.xls has two tabs- truck and car. Any help or suggestions are greatly appreciated. Thanks, Jeff data.xls (this daily report is 2X100's pages) 7:00 car 65 98 78 7:30 truck 5 16 35 8:00 car 45 87 99 8:30 car 44 89 122 10:00 truck 15 79 342 10:30 truck 16 72 45 10:30 car 85 24 57 report.xls (car tab after formula) 7:00 car 65 98 78 7:30 8:00 car 45 87 99 8:30 car 44 89 122 9:00 9:30 10:00 10:30 car 85 24 57 retort.xls (truck tab after formula) 7:00 7:30 truck 5 16 35 8:00 8:30 9:00 9:30 10:00 truck 15 79 342 10:30 truck 16 72 45 |
#2
|
|||
|
|||
One interp / way ..
(Link to a sample file is provided below) Assume the source data below is in a sheet: Data, A1:E7 7:00 car 65 98 78 7:30 truck 5 16 35 8:00 car 45 87 99 8:30 car 44 89 122 10:00 truck 15 79 342 10:30 truck 16 72 45 10:30 car 85 24 57 In sheet: Data Put in F1: =TEXT(A1,"hh:mm") Copy down Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK (The above defines WSN as a name we can use to refer to the sheetname in formulas. It auto-extracts the sheetname implicitly. Technique came from a post by Harlan) In sheet: Car Put in A1: 7:00, in A2: 7:30 Select A1:A2, fill down to A48 to label the 48 half-hourly time segments for a day Put into the formula bar for B1, array-enter (i.e. press CTRL+SHIFT+ENTER): =IF(ISNA(MATCH(TEXT($A1,"hh:mm")&"_"&WSN,Data!$F$1 :$F$100&"_"&Data!$B$1:$B$1 00,0)),"",INDEX(Data!B$1:B$100,MATCH(TEXT($A1,"hh: mm")&"_"&WSN,Data!$F$1:$F$ 100&"_"&Data!$B$1:$B$100,0))) Copy B1 across to E1, fill down to E48 The above will extract the data for rows with "Car" in sheet: Data and return these nicely matched in the correct time segment, viz. you'll get: 7:00 car 65 98 78 7:30 8:00 car 45 87 99 8:30 car 44 89 122 9:00 9:30 10:00 10:30 car 85 24 57 Now just make a copy of the sheet: Car, and rename the copy as: Truck. You'll get the corresponding returns for rows with "Truck" from sheet: Data 7:00 7:30 truck 5 16 35 8:00 8:30 9:00 9:30 10:00 truck 15 79 342 10:30 truck 16 72 45 Here's a sample file with implemented set-up: http://flypicture.com/p.cfm?id=55269 (Right-click on the link: "Download File" at the top in the page, just above the ads) File:Jeff_newusers_1.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jeff" wrote in message ... Hi, I'm looking for help with a formula. In a file, report.xls A1, I am trying... IF([data.xls]car'!A1="7:00",IF([data.xls]car'!B1="Car",'(car.xls!A3:A5))) The dilemma is the data.xls file will skip some half hour intervals; while the report.xls adds all intervals, even when there are no numbers to report. dilemma #2... Retort.xls has two tabs- truck and car. Any help or suggestions are greatly appreciated. Thanks, Jeff data.xls (this daily report is 2X100's pages) 7:00 car 65 98 78 7:30 truck 5 16 35 8:00 car 45 87 99 8:30 car 44 89 122 10:00 truck 15 79 342 10:30 truck 16 72 45 10:30 car 85 24 57 report.xls (car tab after formula) 7:00 car 65 98 78 7:30 8:00 car 45 87 99 8:30 car 44 89 122 9:00 9:30 10:00 10:30 car 85 24 57 retort.xls (truck tab after formula) 7:00 7:30 truck 5 16 35 8:00 8:30 9:00 9:30 10:00 truck 15 79 342 10:30 truck 16 72 45 |
#3
|
|||
|
|||
Here's a new link to the sample file
with the implemented construct: http://www.savefile.com/files/9806240 File: Jeff_newusers_1.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Ascending button - Excel 2003 | Excel Discussion (Misc queries) | |||
Sort Ascending button - Excel 2003 | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |