-
Show value of first non contiguous data
From
Xxer Xxes@21:1/5 to
All on Thu Nov 26 13:00:33 2020
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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Claus Busch@21:1/5 to
All on Fri Nov 27 18:01:54 2020
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<>"",ROW($1:$1451)),ROW(A1)))
and copy down.
Regards
Claus B.
--
Windows10
Office 2016
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 08:38:04 2020
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.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 09:47:28 2020
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 .
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Claus Busch@21:1/5 to
All on Fri Nov 27 19:03:54 2020
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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 09:24:37 2020
My God
Sir , you are a really life.saveing
it work perfectly . thank you so much .
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 10:29:38 2020
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 .
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Claus Busch@21:1/5 to
All on Fri Nov 27 21:11:21 2020
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<>"",ROW($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<>"",COLUMN($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)),COLUMN(A1))
Regards
Claus B.
--
Windows10
Office 2016
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 11:54:03 2020
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<>"",ROW($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 .
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Claus Busch@21:1/5 to
All on Fri Nov 27 21:55:34 2020
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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 12:46:15 2020
works perfecty both
For the column try in A1:
=SMALL(IF($AE$1:$AX$1<>"",COLUMN($AE$1:$AX$1)),COLUMN(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)))
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Xxer Xxes@21:1/5 to
All on Fri Nov 27 13:02:36 2020
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 !
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)