One Function to Non Continguous Columns

[cgrablew]

Mr. Excel,

I have an array that 63 rows & 39 columns (Dates). The first column is the County and other columns are metrics for that columns date. I want to have a single formula that extracts first column & the last 10 columns. The number of columns is dynamic & changes daily as I add another day's figure to the array. I created a formula using choose({1,2,3,4,5,6,7,8,9,10,11},Range1, Range2, Range3,Range4, Range5......,Range11). This isn't a great formula. I tried indirect, filter, offset without success. A formula like =(A1:A10;AA1:AK10) would work but I can't make it dynamic. Any ideas of a better way to do this?

Data Looks like this:

County 3/27 3/28 3/39 ..............................................................5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
Adams 5 7 9 51 55 67 69 75 77 78 81 89 90
Jefferson 8 9 9 .............................................................. 80 83 90 99 100 101 105 106 107 109
.
.
.
.
.
Last County 8 10 11 .............................................................. 80 83 90 99 100 101 105 106 107 109

So I want it to be:

County 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
Adams 51 55 67 69 75 77 78 81 89 90
Jefferson 80 83 90 99 100 101 105 106 107 109
.etc----------------------------------------------------------

 

[mikerickson]

Would you take two formulas
=A1
and
=INDEX(1:1, 1, MATCH("zzzz", 1:1)-11+COLUMN(A1))

Put A1 in one cell, put the other in the cell to the right and then drag the INDEX formula to the right 10 cells

 

[cgrablew]

Thanks but I do have a Non-Elegant one formula solution and was looking for a better one formula solution. I did come up with a few 2 solutions formula similar to what you proposed but was looking for an elegant one formula solution.

 

[Rick Rothstein]

Thanks but I do have a Non-Elegant one formula solution and was looking for a better one formula solution.

I have no idea if this solution is better or not because you did not post what your "non-elegant one formula solution" was, but here is the single formula solution I came up with (it uses Mike's formula as a basis)...

=IF(COLUMNS($A:A)=1,$A1,INDEX(1:1,1,MATCH(9E+99,1:1)-11+COLUMN(A1)))

 

[cgrablew]

Rick,

Thanks I'll give it a try. The non-elegant formula was in the text of the msg---- =choose({1,2,3,4,5,6,7,8,9,10,11},Range1, Range2, Range3,Range4, Range5......,Range11).
where the Ranges were the 11 different ranges. The actual formula is:

=CHOOSE({1,2,3,4,5,6,7,8,9,10,11},B4:B13,OFFSET($B$4,0,Summary!$K$80-10,10,1),OFFSET($B$4,0,Summary!$K$80-9,10,1),OFFSET($B$4,0,Summary!$K$80-8,10,1),OFFSET($B$4,0,Summary!$K$80-7,10,1),
OFFSET($B$4,0,Summary!$K$80-6,10,1),OFFSET($B$4,0,Summary!$K$80-5,10,1),OFFSET($B$4,0,Summary!$K$80-4,10,1),OFFSET($B$4,0,Summary!$K$80-3,10,1),OFFSET($B$4,0,Summary!$K$80-2,10,1),
OFFSET($B$4,0,Summary!$K$80-1,10,1))

Thanks,

Chuck