Re : Excel Range of Values Amidst Characteristic Transitions

Re : Excel Range of Values Amidst Characteristic Transitions

Enter an Excel worksheet ; now that the tabulation is prepared for
(preferential) presentation, Column A is shown as follows :-

01  8.0

02  8.0

03  <A Blank Row>

04  8.0

05  <A Blank Row>

06  <A Blank Row>

07  9.0

08  <A Blank Row>

09  9.0

10  9.0

11  <A Blank Row>

12  6.5

13  6.5

14 <A Blank Row>

15 <A Blank Row>

16 <A Blank Row>

17  8.0

18  <A Blank Row>

19  8.0

20  <A Blank Row>

21  8.0

22  <A Blank Row>

23  6.5

24  <A Blank Row>

25  6.5

26  <A Blank Row>

27  9.6

28  <List continues ...>

The range of values as given above shows the following characteristics
:-

1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
2. The values are interspersed with Blank Rows and the number of Blank
Rows (amidst transitions or without transitions thereof) varies
unevenly (as well).

Please devise a formula that will return an array consisting of the row
numbers corresponding to the transition of values within the given
range namely, {01, 07, 12, 17, 23, 27}.

Please share your experience. Regards.

0
tkt_tang (117)
8/7/2006 4:29:08 PM
excel 39879 articles. 2 followers. Follow

3 Replies
469 Views

Similar Articles

[PageSpeed] 54

Here is a possibility:
In cell B1, enter     =ROW()
In cell B2, enter     =IF(LEN(A2)=0,B1,IF(A2<>INDIRECT("A" &B1),ROW(),B1))
Copy B2 down through all rows of data

Here are 3 different VBA functions. RowArray1 returns a range of cells (the 
cells where the values change in the input range). RowArray2 returns a string 
with the row numbers separated by commas. RowArray3 returns a variant 
containing an array of long integers (row numbers). The parameter for all 
three is the range of cells in column B as described above. 

Public Function RowArray1(Rng As Range) As Range
'Returns an array of cells
    Dim Curr As Range, Prev As Long
    Prev& = 0
    For Each Curr In Rng
        If Curr.Value <> Prev& Then
            Prev& = Curr.Value
            If RowArray1 Is Nothing Then
                Set RowArray1 = Curr
            Else
                Set RowArray1 = Union(RowArray1, Curr)
            End If
        End If
    Next Curr
End Function

Public Function RowArray2(Rng As Range) As String
'Returns a string with comma-delimited row numbers
    Dim Curr As Range, Prev As Long
    Prev& = 0
    For Each Curr In Rng
        If Curr.Value <> Prev& Then
            Prev& = Curr.Value
            RowArray2$ = RowArray2$ & ", " & Curr.Value
        End If
    Next Curr
    RowArray2$ = "{" & Right(RowArray2$, Len(RowArray2$) - 2) & "}"
End Function

Public Function RowArray3(Rng As Range) As Variant
'Returns a variant containing an array of long integers
    Dim Curr As Range, Prev As Long
    Dim RA() As Long, x As Long
    x& = 0
    Prev& = 0
    For Each Curr In Rng
        If Curr.Value <> Prev& Then
            x& = x& + 1
            ReDim Preserve RA(x&)
            RA(x&) = Curr.Value
            Prev& = Curr.Value
        End If
    Next Curr
    RowArray3 = RA
End Function


Hope this helps,

Hutch

"TKT-Tang" wrote:

> Re : Excel Range of Values Amidst Characteristic Transitions
> 
> Enter an Excel worksheet ; now that the tabulation is prepared for
> (preferential) presentation, Column A is shown as follows :-
> 
> 01  8.0
> 
> 02  8.0
> 
> 03  <A Blank Row>
> 
> 04  8.0
> 
> 05  <A Blank Row>
> 
> 06  <A Blank Row>
> 
> 07  9.0
> 
> 08  <A Blank Row>
> 
> 09  9.0
> 
> 10  9.0
> 
> 11  <A Blank Row>
> 
> 12  6.5
> 
> 13  6.5
> 
> 14 <A Blank Row>
> 
> 15 <A Blank Row>
> 
> 16 <A Blank Row>
> 
> 17  8.0
> 
> 18  <A Blank Row>
> 
> 19  8.0
> 
> 20  <A Blank Row>
> 
> 21  8.0
> 
> 22  <A Blank Row>
> 
> 23  6.5
> 
> 24  <A Blank Row>
> 
> 25  6.5
> 
> 26  <A Blank Row>
> 
> 27  9.6
> 
> 28  <List continues ...>
> 
> The range of values as given above shows the following characteristics
> :-
> 
> 1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
> to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
> 2. The values are interspersed with Blank Rows and the number of Blank
> Rows (amidst transitions or without transitions thereof) varies
> unevenly (as well).
> 
> Please devise a formula that will return an array consisting of the row
> numbers corresponding to the transition of values within the given
> range namely, {01, 07, 12, 17, 23, 27}.
> 
> Please share your experience. Regards.
> 
> 
0
8/7/2006 7:54:03 PM
Or, we could skip all the column B stuff and use a function like the 
following (with a sample sub to call it):

Sub AAAAAA()
    Dim i, ZZZ
    ZZZ = RowArray4(Range("A1:A27"))
    For i = 1 To 6
        MsgBox ZZZ(i)
    Next i
End Sub

Public Function RowArray4(Rng As Range) As Variant
'Returns a variant containing an array of long integers
    Dim Curr As Range, Prev
    Dim RA() As Long, x As Long
    x& = 0
    Prev = 0
    For Each Curr In Rng
        If Len(Curr.Value) > 0 And Curr.Value <> Prev Then
            x& = x& + 1
            ReDim Preserve RA(x&)
            RA(x&) = Curr.Row
            Prev = Curr.Value
        End If
    Next Curr
    RowArray4 = RA
End Function

Regards,

Hutch

"Tom Hutchins" wrote:

> Here is a possibility:
> In cell B1, enter     =ROW()
> In cell B2, enter     =IF(LEN(A2)=0,B1,IF(A2<>INDIRECT("A" &B1),ROW(),B1))
> Copy B2 down through all rows of data
> 
> Here are 3 different VBA functions. RowArray1 returns a range of cells (the 
> cells where the values change in the input range). RowArray2 returns a string 
> with the row numbers separated by commas. RowArray3 returns a variant 
> containing an array of long integers (row numbers). The parameter for all 
> three is the range of cells in column B as described above. 
> 
> Public Function RowArray1(Rng As Range) As Range
> 'Returns an array of cells
>     Dim Curr As Range, Prev As Long
>     Prev& = 0
>     For Each Curr In Rng
>         If Curr.Value <> Prev& Then
>             Prev& = Curr.Value
>             If RowArray1 Is Nothing Then
>                 Set RowArray1 = Curr
>             Else
>                 Set RowArray1 = Union(RowArray1, Curr)
>             End If
>         End If
>     Next Curr
> End Function
> 
> Public Function RowArray2(Rng As Range) As String
> 'Returns a string with comma-delimited row numbers
>     Dim Curr As Range, Prev As Long
>     Prev& = 0
>     For Each Curr In Rng
>         If Curr.Value <> Prev& Then
>             Prev& = Curr.Value
>             RowArray2$ = RowArray2$ & ", " & Curr.Value
>         End If
>     Next Curr
>     RowArray2$ = "{" & Right(RowArray2$, Len(RowArray2$) - 2) & "}"
> End Function
> 
> Public Function RowArray3(Rng As Range) As Variant
> 'Returns a variant containing an array of long integers
>     Dim Curr As Range, Prev As Long
>     Dim RA() As Long, x As Long
>     x& = 0
>     Prev& = 0
>     For Each Curr In Rng
>         If Curr.Value <> Prev& Then
>             x& = x& + 1
>             ReDim Preserve RA(x&)
>             RA(x&) = Curr.Value
>             Prev& = Curr.Value
>         End If
>     Next Curr
>     RowArray3 = RA
> End Function
> 
> 
> Hope this helps,
> 
> Hutch
> 
> "TKT-Tang" wrote:
> 
> > Re : Excel Range of Values Amidst Characteristic Transitions
> > 
> > Enter an Excel worksheet ; now that the tabulation is prepared for
> > (preferential) presentation, Column A is shown as follows :-
> > 
> > 01  8.0
> > 
> > 02  8.0
> > 
> > 03  <A Blank Row>
> > 
> > 04  8.0
> > 
> > 05  <A Blank Row>
> > 
> > 06  <A Blank Row>
> > 
> > 07  9.0
> > 
> > 08  <A Blank Row>
> > 
> > 09  9.0
> > 
> > 10  9.0
> > 
> > 11  <A Blank Row>
> > 
> > 12  6.5
> > 
> > 13  6.5
> > 
> > 14 <A Blank Row>
> > 
> > 15 <A Blank Row>
> > 
> > 16 <A Blank Row>
> > 
> > 17  8.0
> > 
> > 18  <A Blank Row>
> > 
> > 19  8.0
> > 
> > 20  <A Blank Row>
> > 
> > 21  8.0
> > 
> > 22  <A Blank Row>
> > 
> > 23  6.5
> > 
> > 24  <A Blank Row>
> > 
> > 25  6.5
> > 
> > 26  <A Blank Row>
> > 
> > 27  9.6
> > 
> > 28  <List continues ...>
> > 
> > The range of values as given above shows the following characteristics
> > :-
> > 
> > 1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
> > to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
> > 2. The values are interspersed with Blank Rows and the number of Blank
> > Rows (amidst transitions or without transitions thereof) varies
> > unevenly (as well).
> > 
> > Please devise a formula that will return an array consisting of the row
> > numbers corresponding to the transition of values within the given
> > range namely, {01, 07, 12, 17, 23, 27}.
> > 
> > Please share your experience. Regards.
> > 
> > 
0
8/7/2006 9:59:02 PM
TKT-Tang wrote...
....
>1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
>to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
>2. The values are interspersed with Blank Rows and the number of Blank
>Rows (amidst transitions or without transitions thereof) varies
>unevenly (as well).
>
>Please devise a formula that will return an array consisting of the row
>numbers corresponding to the transition of values within the given
>range namely, {01, 07, 12, 17, 23, 27}.

Name your range LST and define these additional names:

LST.next
=INDEX(LST,2,1):INDEX(LSTsheet!$1:$65536,MAX(ROW(LST))+1,COLUMN(LST))

seq
=ROW(LST)-MIN(ROW(LST))+1

Then you could generate a list of transitions with these formulas
beginning in D1.

D1 [array formula]:
=MIN(ROW(LST))

D2 [array formula]:
=SMALL(IF(ISNUMBER(LST.next)*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),
MMULT(--(seq>=TRANSPOSE(seq)),--ISNUMBER(LST)))=TRANSPOSE(seq)),
IF(ISNUMBER(LST),LST,0))<>LST.next),seq+1),ROWS(D$2:D2))

Fill D2 down as far as needed. This is more flexible because the column
D list can grow or shrink as needed. If you really want a single array
formula, with all the hassle and inflexibility it entails, you could
use the array formula

=SMALL(IF(ISNUMBER(LST.next)*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),
MMULT(--(seq>=TRANSPOSE(seq)),--ISNUMBER(LST)))=TRANSPOSE(seq)),
IF(ISNUMBER(LST),LST,0))<>LST.next)+(seq=MAX(seq)),MOD(seq,MAX(seq))+1),
ROW($A$1:INDEX($A:$A,1+SUM(ISNUMBER(LST.next)
*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),MMULT(--(seq>=TRANSPOSE(seq)),
--ISNUMBER(LST)))=TRANSPOSE(seq)),IF(ISNUMBER(LST),LST,0))<>LST.next)))))

0
hrlngrv (1990)
8/7/2006 9:59:44 PM
Reply:

Similar Artilces:

I cannot enter data in my Excel 2000 spreadsheet
Good afternoon dear members of the microsoft.public.excel.misc newsgroup. I need Your kindly help. I cannot enter data in my Excel 2000 document for one or other reason. I just clic on any of the spreasheet cells and type for word but it does not work. Any idea ? Many Thanks. Octavio Ovidio What else is going on? Are you using VBA code on the back side? Are there any Excel alert boxes open behind your main application window? Please provide more information. Alex >-----Original Message----- >Good afternoon dear members of the microsoft.public.excel.misc newsgroup. I >nee...

code for auto import from excel to Access97
Thanks for the previous help. I now need some direction in setting code to automatically import data from excel into Access97. I have set up my queries for the import, and it works like a champ. Before I can run that query, I have to manually import the excell data into a specially named table. I would like a command that would allow the importing of the excel data (.csv) into the specially named table, then append that data into 3 seperate tables, which is done using my queries. I would like this command to be placed in one of my "switchboard" menus, but I don't se...

Format date in excel 2000 like '31st January 2005' #4
Trying to set up field in Excel Data Source file so that the current date can be input in letters typed in this format '31st January 2005' Is it possible or am I stuck with dd/mmm/yy format? Good afternoon Malcolm Agingwell Would the date in the format "31 January 2005" be of any use to you? If so, set up a custom format of dd mmmm yyyy. Excel doesn't handl ordinals (1st, 2nd, 3rd etc) on its own. HTH Dominic -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php?action=getin...

Fix #VALUE! error
I need a fix to a simple formula where a cell is not being recongized. example below: cell C1 has 88 in it with no visible formula cell E1 - I put in formula =product(C1,2080) returned 2080 as the solution but should have been 183040 "Charles" <Charles@discussions.microsoft.com> wrote in message news:E3567F0A-328A-4E66-8729-9B1A44CD30CE@microsoft.com... > I need a fix to a simple formula where a cell is not being recongized. > example below: > cell C1 has 88 in it with no visible formula > cell E1 - I put in formula =product(C1,2080) > returned 2...

how do i convert excel to csv format
I am trying to convert multi tabs excel file into csv. Can I get all the tabs in csv format in a same file? Tks If you combine all the data into one worksheet in a new workbook, then you could save that workbook as your CSV file. Ron de Bruin has some code at: http://www.rondebruin.nl/copy2.htm that might get you started. baxterr wrote: > > I am trying to convert multi tabs excel file into csv. Can I get all the tabs > in csv format in a same file? > > Tks -- Dave Peterson ...

how do i find a true value between two different times e.g. 4:15
i would like to find the exact value between two different time in hours and minutes e.g. 4:15 (4 hours 15 minutes) and not 4:42 (4.42 hours) for example. Subtract them! An example: Enter 8:45 in A1 and 5:15 in A2. Enter the formula =A1-A2 in A3 and, voila, the answer is 3:30. Excel will automatically assign the time format to A3 if it has the General number format. -- Jim "Chris01623" <Chris01623@discussions.microsoft.com> wrote in message news:CF16BD54-CC0C-4188-B18D-1C3CE871D95A@microsoft.com... |i would like to find the exact value between two different time in h...

See if cell is in Range
Thanks for taking the time to read my question. I'm passing a string that is a cell reference to a function. In that function I want to determine if that cell reference is within a predetermined range. Not sure how to do that. Right now I have: Function CheckRange(TheSheet As String, TheCell As Range) As Boolean If TheSheet = "Sheet1" Then if TheCell In Range("B4:B30") then 'This line is red as it is incorrect CheckRange = True End If What do I use instead of "In"? Thanks, Brad Brad Use something like: If In...

Excel's option menu
Every time I go into my Excel "Options" menu I get a message that states Excel.exe is being closed by Windows. Any ideas on how to correct this? http://support.microsoft.com/default.aspx?scid=kb;en-us;168201 XL97: Error Messages Appear Clicking Options on Tools Menu Maybe this is your problem AJ -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "AJ" <vajames@charter.net> wrote in message news:01f101c35539$303c9780$a401280a@phx.gbl... > Every time I go into my Excel "Options" menu I get a > message that states Excel.exe ...

first value of the month from a list of dates
Hello gurus, I've read through a list of message thread, and you guys are wonderfu ! . Experts !. Anyway, I hope somebody can help me with this problem. I have a list of share prices together with the dates. (about 20 to 2 dates in a month). How do i extract out the first day of every month from a list o database of 3 years (day by day) ? I think hard, but no avail. Help... -- Message posted from http://www.ExcelForum.com Hi one way: - add a helper colum adjacent to your data list (lets say column C) - in C1 enter the formula: =DAY(A1) ->if column A stores your dates - copy do...

OLAP and Excel question
Hi everyone, I posted this in excel.misc on the 11th Feb. No response Using Excel 2003, a cube database (extension .cub) with a pivot table. The OLAP database is 1,000 rows deep and 13 columns wide. Therefore only 13,000 cells. A PivotTable should handle this easily. When I expand all the levels, I get the following message: "Excel cannot display this PivotTable report. The row area has more than 65,536 items, or the column area has more than 16,384 items." I thought the there were only 256 columns in Excel And I thought that a pivot table could only display 8,000 elements from a ...

Create a Variable from First 4-5 chars of a existing value ??
In a current table, the values in a particular field are all items like: MD45 Tuesday PM or TX341 Friday AM or FL12 Monday PM ...in other words...2 parts seperated by a space. The first part is always a State Abbrv. followed by either a 2 or 3 char. number. That first part will always be 4-5 characters long (or...everthing before the first Space). I'm needing to create a varible that is the First Part of the existing value.. ..in other words...the variable would need to be (from the above examples)... MD45 or TX341 or FL12 I'm kind of familiar with LEFT command. Would usin...

import UTF8 to Excel
I import a text file which was saved in UTF8 format to Excel, but the characters are mashed. If I save the file in unicode by notepad, then I can import to Excel without problem. But the same UTF8 text file, I can open in Word. Any one can help? ...

Excel 2007 and large spreadsheets
I have a user that doesn't want to move to Excel 2007 from 2003 due to what he describes as "Specifically, my analytical tasks often involve creating very large spreadsheets (>100 MB) for data processing. I found that Excel 2007 tended to freeze up or calculate indefinitely when working with such large spreadsheets, and therefore was unsatisfactory for my needs". I can't find anything that addresses this type of issue. -- Len Robinson Manager, IT Helpdesk SkyTerra LP Len, type in:- EXCEL Specifications And Limits - on an F1 search within EXCEL 2007...

Excel and MS Works v4.5
Have been using MS Works v4.5 with W98se for some time now due to invoicing capability, but am new to e-mail and the net. Am wondering if Works document or spreadsheet files are e- mailable or convertible to such and also if recipient can read these files with more modern versions of Excel and Word. Thanks Mark I use excel2002, but not Works. But Works has an option under File|SaveAs to save as a .xls file. I think I'd do a little experiment and save your file as a .xls file and email one. Then find out if everything worked ok. Mark wrote: > > Have been using MS Works ...

Using Excell to input data into Access
Is it posible to use an excel spreadsheet to enter data into access? The actual reason for using excel to enter the fields in access is that over 100 of our users are familiar with excel, but do not know anything about databases. They actually call excel a database about 1/2 of the time. We work in a contract research facility that does lots of different projects in several scientific disciplines of varying complexity. I started using databases to store my research data in back in the 80's with db3. We did not even have an IT department until about 2 years ago. I have been trying to drag ...

How do I enter the date last modified in the footer of an Excel d.
How do I enter the date last modified in the footer of an Excel document? I want to be able to distribute a monthly or weekly report in Excel to users and have it automatically update the date last modified in the footer each time I update the report. You need to do that with a Before_Print event macro. If you search the newsgroups from Google you will find lots of examples. On Fri, 4 Feb 2005 09:33:04 -0800, "Last modified date in footer" <Last modified date in footer@discussions.microsoft.com> wrote: >How do I enter the date last modified in the footer of an Excel doc...

How do I Define A specific a value?
How do I define 3 identical values in a cell or individual cells as 3 and 2 of 3 identitical values as value 2 and 0 of 3 values equal to 1 like the following examples: 777 = 3 277 = 2 247 = 1 and so on.......... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ If I understand you correctly, I believe a VLOOKUP formula will do what you want....... Create a RangeName of your values down one column and the digets you would like to represent them in the next column t...

Excel opens two worksheets instead of one
I have Office 2007 and recently upgraded to Windows 7. When I open an existing document in Excel, sometimes (about 50% of the time) it opens a second blank worksheet along with it. Word does this also. Is there a way to fix this? -- Thank you ...

set or reset the print range for several sheets at a time?
how do you set or reset the print range for several sheets at a time? Manually I don't think it's possible to set more than one sheet's print area at a time. Of course a macro can make this a lot quicker. -- Jim Rech Excel MVP "Mestrella31" <mestrella@mxn.com> wrote in message news:BF8D4BDF-1787-4633-B5D0-AA6F2E4732AF@microsoft.com... | how do you set or reset the print range for several sheets at a time? Unfortunately setting the print area on grouped sheets is not an option without using VBA. Code from Bob Flanagan for setting same print area on groupe...

65000 Row Limitation from Access to Excel
This question has been asked a couple times, but the answer seems to be unclear so I will ask again. I have query results in Access that I would like to work with in Excel - I like Excel pivot tables better than Access pivot tables. If I highlight the 287K line Access table, right click on copy, then paste into the 1 million plus row spreadsheet capacity in Excel, it only pastes 65,000 lines. Exports from Access to the million row plus spreadsheet work similarly only exporting 65,000 lines of the much larger table. I can successfully export the data from Access to a text file, then impor...

Excel Chart How do I create Y axis to represent numbers I am using
I am attempting to create a chart that is populating its data from another worksheet in the same book. I am pulling in numbers that range from 75000 to 3000000 and I would like my y axis to be 50K to 350K in multiples of 25K but I can not figure out how to adjust the axis key. Thanks for your help in advance. Krefty Select the axis and press Ctrl+1 (numeral one). Excel 2003 and earlier: In the dialog that appears, click on the Scale tab, and enter the values you want for min, max, and major unit, and don't check the corresponding Auto boxes. Excel 2007: Check Fixed for each of mi...

Excel graphs should allow copy & paste of individual bar formats
In excel graphs - bar charts, I make the last bar a hashed shading as it is a forcast. To do this I select only the last bar and change its formating. At the end of the month I want to change the format back to the same as all the other bars and make the new coming month's predicted value that hashed shading etc. Unfortunately excel will not allow copy and paste in this situation - so I have do open the format tab etc and then change. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggest...

looking for range of text in a single cell
I just started a new job and my company already had a file with abou 16,000 records in it (called Products). This file contains all of th parts numbers that they carry. In this file there is one column calle "description". This cell contains the dimensions, color name and som other information such as if the piece is flat or rounded. I hav another file that has about 400 records and this file tells me "colo name" as well as the "type" of stone that it is (called Stone_Type). What I am trying to do is add a field to the Products file that wil say stone type. ...

Difference between an excel worksheet and XLXS file
I'm a beginner in the world of computer and ms office. On my computer I using Office2007. In excel Icreate a file and saved it as .XLXS file. For the correctons of this file I send to my friend,by using a flash disk. My friend is using Office2003. My file is not oppen on my friend's computer.Why? "Martin" <Martin@discussions.microsoft.com> wrote in message news:8CE55079-436D-411F-BA7B-9C122AB3CFB2@microsoft.com... > I'm a beginner in the world of computer and ms office. On my computer I > using Office2007. In excel Icreate a file and saved it as .XLXS f...

Merging excel data into word #2
Ok, I have a feeling this process is quite simple, but I haven't bee able to figure it out on my own, nor find anything in FAQ's. I have created an excel spreadsheet containing the following: students' last name, first name, ID number, advisor and counselo (obvioulsy Im in education) I would now like to create a phone call log for each student, but woul like to import each students' info into this word document. So as print the 100 or so pages, page one would have the first students info page two would contain the second students info, and so on. Any help is greatly appre...