Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show value of first non contiguous data
My God
Sir , you are a really life.saveing it work perfectly . thank you so much . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show value of first non contiguous data
Hi,
Am Fri, 27 Nov 2020 12:46:15 -0800 (PST) schrieb Xxer Xxes: 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))) try: =SMALL(IF($A$101:$A$1500<"",ROW($A$101:$A$1500)), ROW(A1)) and copy down. Regards Claus B. -- Windows10 Office 2016 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show value of first non contiguous data
Pe vineri, 27 noiembrie 2020, la 22:55:38 UTC+2
Claus B. -- Windows10 Office 2016 It work btilliant ! What can I say ... In a blink of eyes ... so smart Many many many thanks Sir ! |
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: non-contiguous Row, contiguous Column | Excel Programming | |||
charting non-contiguous data | Charts and Charting in Excel | |||
Sorting data in non-contiguous cells | Excel Programming | |||
large non contiguous data set | Charts and Charting in Excel |