Posted to microsoft.public.excel.worksheet.functions
|
|
Merge and sort two pages
That works just like I wanted. Thanks very much Max.
Regards,
RDW
"Max" wrote:
Here's one formulas play ..
Sample construct available at:
http://www.savefile.com/files/4921356
Merge and sort two pages_RDWirr_wks.xls
Assume the source tables are in sheets named: T1, T2
In a new sheet: X,
Put in A1: =INDEX('T1'!$1:$1,,ROW(A1)+1)
Put in B1, array-enter (press CTRL+SHIFT+ENTER):
=IF(A1=0,IF(INDEX('T2'!$1:$1,,ROW(A1)-MAX(IF($A$1:$A$100<0,ROW($A$1:$A$100)
))+1)=0,"",INDEX('T2'!$1:$1,,ROW(A1)-MAX(IF($A$1:$A$100<0,ROW($A$1:$A$100))
)+1)),A1)
Put in C1, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISERROR(SMALL(IF($B$1:$B$1000,$B$1:$B$100),RO W(A1))),"",SMALL(IF($B$1:$
B$1000,$B$1:$B$100),ROW(A1)))
Select A1:B1, copy down to C100
(adapt the ranges and formula fill to suit)
In sheet: Results,
A1:A6 houses:
Mo. No.
TB 38
TB 39
TB 25
TB 26
TB 10
Put in B1, copy B1 across (up to 100 cols):
=INDEX(X!$C:$C,COLUMN(A1))
This returns the sorted dates
Put in B2 (normal ENTER):
=IF(ISNA(INDEX(OFFSET('T1'!$A:$A,,MATCH(B$1,'T1'!$ 1:$1,0)-1),MATCH($A2,'T1'!
$A:$A,0))),
IF(ISNA(INDEX(OFFSET('T2'!$A:$A,,MATCH(B$1,'T2'!$1 :$1,0)-1),MATCH($A2,'T2'!$
A:$A,0))),"",
INDEX(OFFSET('T2'!$A:$A,,MATCH(B$1,'T2'!$1:$1,0)-1),MATCH($A2,'T2'!$A:$A,0))
),
INDEX(OFFSET('T1'!$A:$A,,MATCH(B$1,'T1'!$1:$1,0)-1),MATCH($A2,'T1'!$A:$A,0))
)
Copy B2 across and fill down to populate the table
Switch-off zeros display for a cleaner look via:
Tools Options View tab Uncheck "Zero Values" OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"RD Wirr" wrote in message
...
I have a workbook with two pages of data tables (Table 1 and Table 2) with
column headings of dates that I need to dynamically merge into one page of
column headings and sorted (horizontally) by date (Merge Table). I can set
up
lookups to bring the data tables into the rows beneath the columns but I
need
to find a formula to that will bring in the sorted dates:
Table 1
Mo. No. 12-20-05 1-10-06 1-25-06
TB 38 10
TB 39 10
TB 25 4
TB 26 5
TB 10 30
Table 2
Mo. No. 1-5-06 1-15-06 1-20-06
TB 38 6 5
TB 39 10 50
TB 25 2 10
TB 26 5
TB 10 4 20
Merge Table
Mo. No. 12-20-05 1-5-06 1-10-06 1-20-06 1-25-06
TB 38 10 5
TB 39 10 10 50
TB 25 2 4
TB 26 5
TB 10 4 20 30
These numbers and dates are changing constantly so I am looking for a
method
that will update the merged table instantly. Can anyone help me find a
formula to do this.
Thanks,
RDW
|