Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH | Excel Worksheet Functions | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |