Home 
Search 
Today's Posts 
#1




Show value of first non contiguous data
hi everybody
I have some non contiguous data in an column , column A , from range rows 50 to 1500; aprox 40 cells . I need a function in range A1 to show the value of the first cell with data ,  or non blank can be say  in A2 a function to show value of the second cell wirh data , and so on , my range A1:A40 to show non contiguous data from column A50:A1500 I really apreciate your help 
#2




Show value of first non contiguous data
must I give more specifications ?
In range a 50 : a 1500 there are a a few cells , maximum 45 cells with a simple value , from 0 to 450 for example . They will apear in different adress always . y so, assume we have in cell a100 value = 89 , in A1 the value must to be 89. the second value is , f.eg. , in A154 , vith value = 57 ; cell a A2 must display value = 57 ; between cells filled with values , there are empty cells , with no kind of data in it. 
#3




Show value of first non contiguous data
Hi,
Am Fri, 27 Nov 2020 08:38:04 0800 (PST) schrieb Xxer Xxes: must I give more specifications ? In range a 50 : a 1500 there are a a few cells , maximum 45 cells with a simple value , from 0 to 450 for example . They will apear in different adress always . y so, assume we have in cell a100 value = 89 , in A1 the value must to be 89. the second value is , f.eg. , in A154 , vith value = 57 ; cell a A2 must display value = 57 ; between cells filled with values , there are empty cells , with no kind of data in it. try in A1: =INDEX($A$50:$A$1500,SMALL(IF($A$50:$A$1500<"",RO W($1:$1451)),ROW(A1))) and copy down. Regards Claus B.  Windows10 Office 2016 
#4




Show value of first non contiguous data
My God
Sir , you are a really life.saveing it work perfectly . thank you so much . 
#5




Show value of first non contiguous data
I cant figure it out to make a little change in this formula to
make the task with 100 cells , A1:A100 for bringing data from range A101:A1500 . 
#6




Show value of first non contiguous data
Hi,
Am Fri, 27 Nov 2020 09:47:28 0800 (PST) schrieb Xxer Xxes: I cant figure it out to make a little change in this formula to make the task with 100 cells , A1:A100 for bringing data from range A101:A1500 . then try in A1: =INDEX($A$101:$A$1500,SMALL(IF($A$101:$A$1500<"", ROW($1:$1400)),ROW(A1))) Regards Claus B.  Windows10 Office 2016 
#7




Show value of first non contiguous data
then try in A1:
=INDEX($A$101:$A$1500,SMALL(IF($A$101:$A$1500<"", ROW($1:$1400)),ROW(A1))) Brilliant I remain in debt for you this year . 
#8




Show value of first non contiguous data
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 . If it can be done , this will save me from a lot of code and time wasteing in calculation . 
#9




Show value of first non contiguous data
Hi,
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)) Regards Claus B.  Windows10 Office 2016 
#10




Show value of first non contiguous data
works perfecty both
For the column try in A1: =SMALL(IF($AE$1:$AX$1<"",COLUMN($AE$1:$AX$1)),CO LUMN(A1)) for this above one , is my last request on today ; to change rows instead columns, and bring the adress number of the row with data , for below range adress : =INDEX($A$101:$A$1500,SMALL(IF($A$101:$A$1500<"", ROW($1:$1400)),ROW(A1))) 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Function to return the last value in a contiguous set of data  Excel Worksheet Functions  
Copy and Paste LAST ROW of data: noncontiguous Row, contiguous Column  Excel Programming  
charting noncontiguous data  Charts and Charting in Excel  
Sorting data in noncontiguous cells  Excel Programming  
large non contiguous data set  Charts and Charting in Excel 