Be Excellent To Each Other

Right, I have an Excel sheet exported from an external program and it has 3 sets of data, as below.
millisec - k0 - Count
6.7 - 1.9 - 10692

this is repeated 20 times, eg
millisec - k0 - Count - millisec - k0 - Count - millisec - k0 - Count...

the data is exported as the largest count value in the first set. Over time the count values change so that what is the largest count value in one row isn't the next, therfore the data is all miggled up wrt k0. (does this make an sense?)

I want to be able to filter all data from the sets within given bounds of k0. Is there a quick way of doing this for a spreadsheet? I almost want an SQL type command that says select from (column x, x-1, x+1) where (x > y, x< z). (I bet this makes even less sense)

An easier way to do it might be to concatenate all of the like columns, is there a swift way of doing this? My VBA isn't good enough to sort this, but it isn't none existent.


Any help would be appreciated, but as this is unlikely to make sense to anyone, please feel free to simply ignore it.

Can you post the XLS?

Edit -- I think I understand your problem. Not sure of the best way to solve it, sure we can figure something out though.

ignore what?

Sorry

Because you're worth it.

I'm afraid I don't really understand the question though

Post a chunk of it in a [code] box maybe, and explain again?

There is another column in the first column, but just ignore that, it is a simple count column.

Anything with a ??? means there was nothing present in that cycle.

Mass spec data?

Not far off. Ion Mobility Spectrometry.

So you want a flat list of values of C for a given value or range of values of k0? And the millisec numbers and count down the left don't matter?

I think loading it into a database is the way forward.

Beaten me to it there Doc.

I think in Excel 2007 you can filter like that. Although I'm still not exactly sure what you want to do.

Malc

Can you not just reformat the stuff so that you only have 3 columns, or is the spectrum number actually important?

Malc

It's the data repeating in both rows and columns that are defeating my attempts at an in-Excel solution, I can't beat the pivot table wizard into consuming the data. I wonder if SQL Import Services will consume the data.

Try doing some side-sheets to level up and stock up on Java Applets to regain health.

Hmm, I'd be tempted to re-format it in a single column (well, 3 columns) by hand - it'd only take 10 minutes. Of course if you need to do this more than once, maybe rig up a perl-script (or hell, even a text editor macro) to pre break-up each line of 20 in the source file?

then you can sort for k0 out of all the data. Is that what you wanted?

Alt-F4

Fuuuuck, no.

Actually, yes. Fuck all this, export it as CSV and run it through a Perl script.

This is what I have been doing in the past, but I am getting fed up of doing this and wondered if there was an easier way.

I tried the auto export to access stuff, but I couldn't figure out how to make all of C into one single field (and all of k0).

Cheers guys, don't waste any of your time on it, I just wondered if I was missing something obvious.

Riiight. Err, this may require a bit of explaining, like all of it. I have never used Perl.

I shall look it up on the interweb.

I'd just wait a bit if I were you

Do you just want all the C values for a given k0? It doesn't matter which row of the sheet they appear on?

kind of, for a range of k0 values, eg between 1.844 and 1.708

I'll give you three columns, sorted on k0. How's that? Then you can grab a range.

Edit, er maybe not, I need them to be referenced, so a {k0, C} pairing. if you see what I mean.

Well that's easy isn't it, surely you can just scroll down to 1.708 in the sorted list and then to 1.884 and select those? The C value would come with it.

PS I can't do the Perl for you, I don't have it installed here and am not at all good at it anyway

Yeah, I mean, three columns -- millisec (for completeness), k0, and C.

Ah, I see, I misunderstood.
I wouldn't expect you to do it.
This is getting silly now, I am going to have learn some sort of coding malarky.

'Sok. I miss Perl

Evidence of how much I miss Perl: I wrote

and stared dumbfounded at the compile errors.

Well I'd have given it a shot if I had it installed. It's a good idea to use it occasionally to keep your hand in, so to speak. I fail at that.

PS you might want to post up a bit of the source text file. It would be easier to go source -> perl -> import to xcel and sort
rather than source -> import to excel -> export to CSV -> perl -> import to excel and sort

First attempt. First column in that CSV is the value of k0, rows over to the right are all the values of C that correspond to that value of k0.

Second attempt. Simple pairs of {k0,C} sorted by k0. You can grab vertical ranges from that in Excel with a quick formula.

Do either of these solve your problem Bobby? Also, note, you should do some manual error checking to make sure I haven't goofed. I did some debugging as I went along but I make no promises!

Script is here if you are curious.

Edit -- if this is no good, now I've written the parser I can get the data out anyway you like.

Oh that sorts it for you too! Of course.
Well done Dr G!

That's brilliant! Thanks Dr G!
Out of curiosity, what do I need to run Perl? And why did you guys choose Perl over any other language for this?

Perl is kind of built for handling data - check out the script, it's very easy to manipulate if you know what you're doing.

Most other languages you'd be buggering about with arrays of strings and writing your own parsing and sort algorithms.

There are other languages with the same high level functionality though - Ruby is the other one that springs to mind.

You're welcome!

I only really went with Perl because of familiarity (my First Real Job was writing Perl scripts to mangle scientific data, funnily enough). Not sure I'd recommend you start with Perl; it's easy to use for this sort of stuff but can be quite tricky in other ways. Hmm, I'll have a think. Python might be a better bet, which is as powerful as Perl but less idosyncratic.

Nice work, nerd-types!

Wait 'til you see us in Horde mode.

I like that you are not only nerdy enough to point this out, but also to assume that the rest of us would understand/care.

The polite thing would have been to smile and nod, Cras.

"So, err, d'ya think you'll have it fixed soon Dave? My internet?"

I have horded with these people. It was terrifying (and wonderful).

More help, please.
I have a range in excel sheet. I want to assign it to an array. A range is an array, I just want to say range = array. How do I don this, I have tried:


and a couple of variations of this, but I can't get it to assign. I comes up with the error message, "can't assign to array".

What am I doing wrong?

I always had to loop though each cell in the range and place it in the array "manually". This is me just being lazy and not finding out if you could assign a range as an array.

A range isn't an array, it's a collection of cell objects. I'm not sure you can assign it automatically, you may have to iterate through the array something like this:

I was informed incorrectly from the internetz. I shall do as you suggest. Thanks.

Also change your array declaration to string or integer, whichever your values are.

Christ, VB is horrible.

To be fair, VBA hasn't been touched in about 12 years. I really wish they'd bring out a VBA.NET

Bobby, why are you putting range data into an array? A range is a pretty nice data structure of it's own, with lots of lovely built in functions like searching that an array doesn't have.

I hate excel VBA, it is so poorly structured and cobbled together, plus I have no idea what I am doing!
What I wanted to do was, in poor pseudo code:

range1
rangeLife

where (value in range1 is present in rangeLife) coincident cellColour = Red

pretty simple really, but my knowledge of coding is poor and coloured with C++ and Java, so I keep getting things wrong.

VBA not needed. You can use conditional formatting. On my phone so hard to post an example but try http://www.mrexcel.com/articles/duplica ... atting.php for examples.

Although it might be better to add another column with

=if(countif(lookup_range,lookup_cell)>0,"X","")

Then you will be able to sort and filter the results.