Index, Match or what???
 

I have information on a data page like the following

A B C
8-15 10-16 99
8-16 11-24 98.7

where columns A and B are start and end dates and C is a value for those
dates... how can I stick this data into another sheet formated as a matrix
8-15 8-16
10-16 99
11-24 98.7

I have tried various combinations of INDEX and MAtCH or Vlookup... any help
is greatly appreciated.

Index, Match or what???
 

I wasn't sure whether the top row/first column of dates were in place but
try this to get your data:

in cell B2 on Sheet2 (ie. where the 99 is):

=IF(ISERROR(INDEX(Sheet1!$C$1:$C$100,MATCH(1,(C$1= Sheet1!$A$1:$A$100)*($A3=Sheet1!$B$1:$B$100),0))), "",INDEX(Sheet1!$C$1:$C$100,MATCH(1,(C$1=Sheet1!$A $1:$A$100)*($A3=Sheet1!$B$1:$B$100),0)))

Change ranges to suit

Enter as an array formula with Ctrl+Shift+Enter (you should see { ...}
appear round the formula)

Copy across and down as required.

The following will copy the dates from column A in Sheet1 to row 1 in Sheet2
starting in B1:

=IF(COLUMN()-1<=COUNTA(Sheet1!A:A),INDIRECT("Sheet1!A" & COLUMN()-1))

Copy across row 1

The following will copy the dates from column B in Sheet1 to Column A1 in
Sheet2 starting in A2:

=IF(INDIRECT("Sheet1!B"&ROW()-1)<"",INDIRECT("Sheet1!B"&ROW()-1),"")

Copy down column 1

HTH

"John" wrote:

I have information on a data page like the following

A B C
8-15 10-16 99
8-16 11-24 98.7

where columns A and B are start and end dates and C is a value for those
dates... how can I stick this data into another sheet formated as a matrix
8-15 8-16
10-16 99
11-24 98.7

I have tried various combinations of INDEX and MAtCH or Vlookup... any help
is greatly appreciated.

Index, Match or what???
 

toppers thank you! I have got it for the example... now to try an adapt to
my sheet! Thank you

"Toppers" wrote:



I wasn't sure whether the top row/first column of dates were in place but
try this to get your data:

in cell B2 on Sheet2 (ie. where the 99 is):

=IF(ISERROR(INDEX(Sheet1!$C$1:$C$100,MATCH(1,(C$1= Sheet1!$A$1:$A$100)*($A3=Sheet1!$B$1:$B$100),0))), "",INDEX(Sheet1!$C$1:$C$100,MATCH(1,(C$1=Sheet1!$A $1:$A$100)*($A3=Sheet1!$B$1:$B$100),0)))

Change ranges to suit

Enter as an array formula with Ctrl+Shift+Enter (you should see { ...}
appear round the formula)

Copy across and down as required.

The following will copy the dates from column A in Sheet1 to row 1 in Sheet2
starting in B1:

=IF(COLUMN()-1<=COUNTA(Sheet1!A:A),INDIRECT("Sheet1!A" & COLUMN()-1))

Copy across row 1

The following will copy the dates from column B in Sheet1 to Column A1 in
Sheet2 starting in A2:

=IF(INDIRECT("Sheet1!B"&ROW()-1)<"",INDIRECT("Sheet1!B"&ROW()-1),"")

Copy down column 1

HTH

"John" wrote:

I have information on a data page like the following

A B C
8-15 10-16 99
8-16 11-24 98.7

where columns A and B are start and end dates and C is a value for those
dates... how can I stick this data into another sheet formated as a matrix
8-15 8-16
10-16 99
11-24 98.7

I have tried various combinations of INDEX and MAtCH or Vlookup... any help
is greatly appreciated.