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

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

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

You're welcome ! Always great to hear it worked, and exactly as required,
too <g. Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RD Wirr" wrote in message
...
That works just like I wanted. Thanks very much Max.
Regards,
RDW