Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 26th 20, 10:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 05:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 06:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default 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   Report Post  
Old November 27th 20, 06:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default Show value of first non contiguous data

My God

Sir , you are a really life.saveing

it work perfectly . thank you so much .
  #5   Report Post  
Old November 27th 20, 06:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 07:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default 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   Report Post  
Old November 27th 20, 07:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 08:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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   Report Post  
Old November 27th 20, 09:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default 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   Report Post  
Old November 27th 20, 09:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to return the last value in a contiguous set of data Deb Excel Worksheet Functions 3 May 13th 09 01:48 PM
Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column Sam via OfficeKB.com Excel Programming 8 November 5th 07 08:18 PM
charting non-contiguous data tom Charts and Charting in Excel 10 July 17th 07 05:04 PM
Sorting data in non-contiguous cells jonco Excel Programming 1 April 27th 06 09:59 PM
large non contiguous data set bhofsetz Charts and Charting in Excel 0 May 25th 05 05:30 PM


All times are GMT +1. The time now is 07:52 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017