How to get data from different years (2011+to start the same year (Year1+)

**barelyadraft** · 04-13-2011, 07:03 AM

Hi everyone, you can help me with this one-

I would like to know how to construct a formula that will look through a table and insert the first data >0 as Y1 and then the following rows in Y2, Y3 etc. Please see before and the desired after result by clicking below

nullalize.jpg

Excel sheet with data to be manipulated can be found here: Excel sheet to be used.xls

thanks for your help!

**Vinodsralian** · 04-13-2011, 07:08 AM

Hi,

Plz post a sample spreadsheet, with sample data and desired result for better response.

Vinod

**Sadath31** · 04-13-2011, 07:23 AM

see the attached file

**martindwilson** · 04-13-2011, 07:23 AM

try this uses
=IF(COLUMN(A1)>COUNT($B2:$G2),"",INDEX($B2:$G2,MATCH(TRUE,INDEX(($B2:$G2<>0),0),0)+COLUMN(A1)-1)) thus avoiding indirect which can be a pain on large data sets

**barelyadraft** · 04-13-2011, 07:24 AM

Originally Posted by Vinodsralian

Hi,

Plz post a sample spreadsheet, with sample data and desired result for better response.

Vinod

done

Note that it's European notation (semikolon used instead of comma as septation of inputs). Just mentioning if Excel does not convert automatically (which I doubt :X)

**barelyadraft** · 04-13-2011, 07:35 AM

Originally Posted by martindwilson

try this uses
=IF(COLUMN(A1)>COUNT($B2:$G2),"",INDEX($B2:$G2,MATCH(TRUE,INDEX(($B2:$G2<>0),0),0)+COLUMN(A1)-1)) thus avoiding indirect which can be a pain on large data sets

nevermind, thanks!