automating an array?

[jaimetimbrell]

Hello all,

I have been driven to near dementure by a problem that I am sure has a simple solution (but then all problems are easy when the solution is to hand!)

I have a spreadsheet, about 5000 rows by 20 columns of data that has been imported from a *.txt file. The data is separated into events that take place separately and I want to treat each event as a unique array and look at each event separately - it is all vertically stacked when looking at the spreadsheet.

one of the columns contains 3 digit numeric values and I want to run a MAX(array) formula, but I am having a problem constructing an expression that starts at the top of each column of data in each unique event and stops at the bottom of that unique event, by way of illustration:

column J
row 2 RESULT
row 3 452 MAX(J3:J6) result 937
row 4 653
row 5 937
row 6 932
row 7 "blank"
row 8 RESULT
row 9 487 MAX(J9:J15) result 983
row 10 665
row 11 344
row 12 958
row 13 234
row 14 567
row 15 983
row 16 "blank"
row 17 RESULT
etc

How do I construct a formula that starts at row 3 and stops at row 6, calculates the MAX figure and then starts again at row 9 etc - the next separate event? In other words how do I automate construction of the array in each of the MAX formulae above (J3:J6 and J9:J15 etc)?

(note: I have 100 spreadsheets of about 5000 rows so manually transposing or manipulating the data isnt an option open to me)

Any help would be most gratefully received.

Best Regards,
Jaime.

[Max]

One play which might work ..

For the col J sample data, let's just take J2:J16, which covers the first 2
"batches" (disregard cell J17, which is the starting cell of the 3rd batch)

Using 2 empty cols to the right, say cols K & L

Put in K2: =COUNTIF($J$2:J2,"Result")
Copy K2 down until the last blank in col J, i.e. till K16

Col K will mark it out nicely as to the different "batches",
i.e. the lines in-between one "RESULT" to the next "RESULT"

Now we could put in say L2, and array-enter,
i.e. press CTRL+SHIFT+ENTER:

=MAX(IF($K$2:$K$16=ROWS($A$1:A1),$J$2:$J$16))

and copy L2 down until zeros appear,
signalling exhaustion of calcs for col J

For the sample data in J2:J16,
L2 will return the max for the 1st batch: 937
L3 will return the max for the 2nd batch: 983
and so on ..

Adapt the ranges $K$2:$K$16, $J$2:$J$16 to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jaimetimbrell" <[email protected]>
wrote in message
news:[email protected]...
>
> Hello all,
>
> I have been driven to near dementure by a problem that I am sure has a
> simple solution (but then all problems are easy when the solution is to
> hand!)
>
> I have a spreadsheet, about 5000 rows by 20 columns of data that has
> been imported from a *.txt file. The data is separated into events that
> take place separately and I want to treat each event as a unique array
> and look at each event separately - it is all vertically stacked when
> looking at the spreadsheet.
>
> one of the columns contains 3 digit numeric values and I want to run a
> MAX(array) formula, but I am having a problem constructing an
> expression that starts at the top of each column of data in each unique
> event and stops at the bottom of that unique event, by way of
> illustration:
>
> column J
> row 2 RESULT
> row 3 452 MAX(J3:J6) result 937
> row 4 653
> row 5 937
> row 6 932
> row 7 "blank"
> row 8 RESULT
> row 9 487 MAX(J9:J15) result 983
> row 10 665
> row 11 344
> row 12 958
> row 13 234
> row 14 567
> row 15 983
> row 16 "blank"
> row 17 RESULT
> etc
>
> How do I construct a formula that starts at row 3 and stops at row 6,
> calculates the MAX figure and then starts again at row 9 etc - the next
> separate event? In other words how do I automate construction of the
> array in each of the MAX formulae above (J3:J6 and J9:J15 etc)?
>
> (note: I have 100 spreadsheets of about 5000 rows so manually
> transposing or manipulating the data isnt an option open to me)
>
> Any help would be most gratefully received.
>
> Best Regards,
> Jaime.
>
>
> --
> jaimetimbrell
> ------------------------------------------------------------------------
> jaimetimbrell's Profile:
http://www.excelforum.com/member.php...o&userid=26162
> View this thread: http://www.excelforum.com/showthread...hreadid=394847
>

[jaimetimbrell]

Max,

I am sorry but I am obviously being a bit dim. It didnt work but then that is probably how I have adapted your advice.

I dont know what the $A$1:A1 refers to or what ctrl shift enter does or when/how to do it?

I think I need a beginners class!

JAime.

[Max]

Maybe take a look at a sample file with the implemented construct? :
http://www.savefile.com/files/9609633
File: Automating_an_array_jaimetimbrell_misc.xls

> .. what ctrl shift enter does or when/how to do it?

Pressing CTRL+SHIFT+ENTER
is a special way of entering array formulas
(after we have pasted the formula into the formula bar)

For normal formulas, we just press ENTER to confirm the entry,
but for array formulas we need to press CTRL+SHIFT+ENTER
(Hold down the CTRL + SHIFT keys, press ENTER)

If correctly done, Excel will then insert curly braces { }
around the array formula

For e.g. in L2 in the sample file, if you look closely,
the formula appears in the formula bar as:
{=MAX(IF($K$2:$K$16=ROWS($A$1:A1),$J$2:$J$16))}

Don't worry about the "A$1:A1" etc for now

Hope the above and the sample file helps ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jaimetimbrell" <[email protected]>
wrote in message
news:[email protected]...
>
> Max,
>
> I am sorry but I am obviously being a bit dim. It didnt work but then
> that is probably how I have adapted your advice.
>
> I dont know what the $A$1:A1 refers to or what ctrl shift enter does
> or when/how to do it?

> I think I need a beginners class!
>
> JAime.
>
>
> --
> jaimetimbrell
> ------------------------------------------------------------------------
> jaimetimbrell's Profile:
http://www.excelforum.com/member.php...o&userid=26162
> View this thread: http://www.excelforum.com/showthread...hreadid=394847
>

[jaimetimbrell]

Max,

I have just returned from a weekend away from my pc, and have returned to this fantastic additional advice and very haelpful save file.

I have been through your advice again and the penny has dropped.

Thank you so, so, much for sticking with me and explaining things again.

This has helped me greatly and I am indebted to your kind attention to my problem and helping me to move on in my knowledge and solving this problem.

I only hope that I can help you in some other matter - but given the gulf in our respective knowledge of excel I dont think it will be on this forum.

Thank you again.
Jaime.

[Max]

You're welcome, Jaime ! Glad it helped.
The feedback is appreciated ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--