Home |
Search |
Today's Posts |
#1
|
|||
|
|||
automating an array?
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 |
#2
|
|||
|
|||
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" wrote in message news:jaimetimbrell.1tl8qg_1123743912.782@excelforu m-nospam.com... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message news:jaimetimbrell.1tnbsl_1123841191.875@excelforu m-nospam.com... 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 |
#5
|
|||
|
|||
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. -- jaimetimbrell ------------------------------------------------------------------------ jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162 View this thread: http://www.excelforum.com/showthread...hreadid=394847 |
#6
|
|||
|
|||
You're welcome, Jaime ! Glad it helped.
The feedback is appreciated .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass an array to Rank | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |