Quartiles of very large data scattered over several sheets.

[ScorchedLemonade]

Hi, I will soon have a lot of data over several Excel workbooks and want to be able to do something like this:

select data from a row of column C, say, if the same rows in columns A and B contain certain data

e.g. if one were at a zoo feeding animals the quantities they got at certain times might be like this

hedgehog breakfast 3
squirrel lunch 2
shrew morning 20
hedgehog breakfast 4
cerebus breakfast 3
hedgehog lunch 13
jabberwocky tea 5
etc.

So, I'd be selecting the 3 and the 4 for "hedgehog" and "breakfast" respectively.
Then I'm wanting eventually to find the upper quartile of all the data so collected.

There'll probably be about 23000 rows of data, several columns and several sheets.

Excel claims that it only does quartiles for up to 8191 pieces of data but this actually isn't true. It does seem not to be able to take disjoint arrays of data though and even if it did, the selection of those pieces of data could be a problem.

Is there an easy was around this?

Thanks.

 

[bkpchs237]

ScorchedLemonade,

I am sure that someone could write a macro that would search your data set efficiently and come up with a list that would suit your needs. However, I will suggest two different formula methods. I prefer Method 2 myself.

Method 1:

Insert a new column A in front of your present 3 columns of data. In the new column A place the formula =B1&C1 (assuming no header row) and copy that formula down the entire column for the entire 23000 rows. Then copy and paste special, values, ok, these items to get rid of the formula.

Then wherever you desire for your resulting list to appear, place the formula:
=if(isna(vlookup("hedgehogbreakfast",$a1:$d1,4,false)),"",vlookup("hedgehogbreakfast",$a1:$d1,4,false))
and copy it down the necessary 23000 rows. Or you could use a reference to some static cells themselves instead of "hedgehogbreakfast" (i.e. $b$1&$d$1). This gives the advantage of just changing two cells to obtain different results. This will place a number in the column where the item was found and a blank where it wasn't. Then you can sum the items; sort them; or filter them to display items as needed. You could paste special, values, ok them to get rid of the formulas or leave them to obtain different results as needed. After that you could copy the items to another location or use the items as you see fit to quartile them.

Method 2:

Highlight the first row of data in your data set, Go to Data, Filter, Auto Filter. Click the down arrow on column A and choose "hedgehog" and click the down arrow on column B and choose "breakfast." This will leave you with all the cells that include these two items in their respective columns. Now highlight all the cells that you see and go to Edit, GoTo, Special, Visible Cells Only, OK. Then Edit, Copy, move to a new location of your choosing, preferably a new sheet to use for calculations only, Edit, Paste. Now you have a separate list of your desired values. Quartile them as you see fit.

Hope this helps.