View Single Post
  #9   Report Post  
Old November 27th 20, 09:11 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default Show value of first non contiguous data


Am Fri, 27 Nov 2020 11:54:03 -0800 (PST) schrieb Xxer Xxes:

I will dare to ask here more two things , which they will
acomplish all my task .

One is that im shure I will need and to can do this and in a Row , instead
of a column . So, instead of Column A , to do this in Row 1 ;
for eg. to show data from row 1 , Range Column 31 to Column 50 ,
this mean range AE1:AX1 , in range A1 : J1 ( that represesnt ten columns ).

Second dare , is to ask for an improvement to first formula
< try in A1:
<=INDEX($A$50:$A$1500,SMALL(IF($A$50:$A$1500<"",R OW($1:$1451)),ROW(A1)))
< and copy down.
which to do the same task , But , if it can be ,
to give in cells like a value the number of the row where this data is , not the value of the data .

for the value try in A1:
=INDEX($AE$1:$AX$1,0,SMALL(IF($AE$1:$AX$1<"",COLU MN($A$1:$T$1)),COLUMN(A1)))
and copy to the right.
For the column try in A1:
=SMALL(IF($AE$1:$AX$1<"",COLUMN($AE$1:$AX$1)),COL UMN(A1))

Claus B.
Office 2016