I’m having a very annoying data manipulation problem, and brute force is likely to take me about a week to achieve what I’m after.

Basically, the workbook is being set up as a template to receive data that a scientific instrument will push to it and populate. The sheet the instrument software populates contains 127 matrices of ca. 19,000 cells. However, the order in which this is done (and can’t be altered) is that it’s a different matrix for each wavelength, when I need the data sorted by sample. Basically, how the sheet is organised:

Excitation wavelength = w – matrices
Sample = x – columns
Emission wavelength = y – rows
Response = z – value

What I need, on the next sheet is:

sample = w – matrices
Emission wavelength = x – rows
Excitation wavelength = y – columns
Response = z – value

Is there a way to do this, or am I resigned to linking to the first sheet and carrying out about 19,000 formula changes by hand? Once I have the matrices of data organised correctly, I then need to carry out some manipulations, but these formulae should copy well enough once the data’s all in the right order.

excel really doesn’t sound like the tool for you in this instance…

Is that english …?

Sorry mate – being the wrong side of 50 i havnt got a clue what youre talking about 🙄

Not sure I fully understand what you’re trying to achieve but would a pivot table not do the job for you?

This does sound like a pivot table job.

I’m sure it could be done, but I’m too thick to understand what you are trying to do with the explanation you’ve given. Could you put up a screenshot of a sample matrix with a few entries and one with the results you are looking for.

It does sound like a bit of VBA might be needed to truly automate it though.

Hmmm. Re-reading I agree that’s not clear. I’ll try again…

Basically, I have four dimensions of information

The instrument takes microplates containing 96 individual samples (what I want the matrices separated by, or teh fourth dimension). This particular analysis involves scanning fluorescence. As fluorescence data is three-dimensional (wavelength of light shone at the sample – excitation, wavelength of light fluoresced back – emission, and the response at these two parameters). This results in a matrix of data per sample I was talking about.

However, the instrument’s software won’t export the data on a per-sample basis. Instead, it exports it in matrices on a excitation wavelength basis (to do with how it scans, but can’t be changed).

What I get is 127 matrices, which contain ‘stripes’ of emission data by row, sample by column, and response is the value. Each matrix is a different excitation wavelength

What I need is 96 matrices, which contain emission data per column, excitation data by row, and response is the value. Each matrix would be an individual sample.

I’m not sure pivot tables will do it.

matlab.

matlab.

Hmmm. There’s a couple of problems with that suggestion, not least that we don’t have a license, but even if we did, I wouldn’t even know where to start.

Assuming the instrument is dumping out a load of text files, I’d do the manipulation in a scripting language like python, perl or php.

Alternatively, you could use vba to do it within excel, but you’re going to need to learn the vba or buy it in.

Good luck.

I learnt to program matlab when I ran into how difficult it is to manipulate large multi-dimensional datasets in excel.

could probably do it in python as well but I havent had time to learn that.

octave is a free matlab alternative

It sound like you actually want to deconstruct the matrices into a tabular format?

Would this add-in help I wonder? It would at least get the data into a flattish file format which you could then pivot to display in the format you need.

http://nature.berkeley.edu/~oboyski67/MatrixConverter.htm

I suspect you’d still need a bit of VBA to automate the conversion though.

Unfortunately the instrument only spits out the data in excel. We do have a couple of excel power users in site who’ve built workbooks powered by vba. I’ll go and buy one a coffee tomorrow and see if it’s a relatively easy job for them.

I think a much rougher version might be to transpose the data so samples are rows, add another column or two to make sure they stay in the right order, then sort.

Though I’d rather a more elegant automated approach.

You wouldn’t actually need any VBA to use the add-in – just Excel. I’d just test it against one of the matrices to see if it does the job – you’ve then got an example you can show to your VBA boys and get them to automate it.

@ SprocketJockey – yeah, that’s the sort of thing I’m trying to do, but I still need them in matrices, just in a different manner than how they are currently.

From the very rudimentary knowledge I have of vba, I think I might just be able to fiddle with that macro from Berkley to do what I want, if it’s as simple as copying it 127 times and altering cell targets to get it to do it all at once

I think a much rougher version might be to transpose the data so samples are rows, add another column or two to make sure they stay in the right order, then sort.

This sounds easy enough to do in VBA which would give you the appearance of

a more elegant automated approach

Without it actually being this.

However Excel is only really good at dealing with data in 2 dimensions. Matlab does seem the answer (although its 10 years since I’ve used it) and a licence can’t cost more than a week of your time. Can it?

Sorry sprocketjockey – crossed posts twice. Yep, I think that add-in might work, or at least be close enough to what I want to do to get someone who does know about vba to fiddle about with it.

and a licence can’t cost more than a week of your time. Can it?

If only it were that simple 😉

Let us know how you get on

Can you give me a simplified version of what you’re on about?
You can rearrange stuff using indirect, and I can show you how, but I need a simple, illustrative example to work with.

So are you trying to breakdown 1 cell of all this information into separate columns based on different formulas? If so how is the stripe of information separated (tab, comma, space)?

Have you tried Excel Forum for help?

There are some very clever Excel wizards on there from all over the world that are only too happy to help.

zokes – think I get what youre trying to do.

Id call it three dimensional rather than 4 though.

few Qus:

1) if I understand right, you have 127 matrices which when you reorder the data will mean you have 127 columns of data in SampleNo. of matrices and EmissionNo of rows in each matrix?

2) are the output matrices located at consistent locations on the work sheet – i.e. top left hand corner of each matrix is always in the same column and the number of cells between the top left hand corner on one matrix and the one below it is always the same?

Stoner (and reggiegasket – stoner’s summation is a good simplified version) – yep – your (1) is the problem. It is 3d data – the fourth dimension in the sample number.

However, in answer to (2), the matrices are all in one spreadsheet, but with a consistent spacing between each matrix downwards.

But, the one bit of control I do have is where the matrices are outputted in a spreadsheet, so if there’s an easier way to place the matrices for a macro to do its magic, then I can do that.

…in which case, yes, it can be done.

Using a combination of MATCH and OFFSET to find the coordinates of the data you want based on the new matrix parameters and then populate that new matrix, and so reorganise the data, assuming the SAMPLE and WAVELENGTH names are unique.

If youd like to email me an example of your current output sheet as it is, I can have a go at building the translation sheet in the same workbook sometime tomorrow.
Im sitting at a desk doing little else so would welcome a diversion/problem.

BTW, “Matrix” isnt really the right term. Table would be the more usual description.

Zokes,

This sounds like the sort of manipulation I had the misfortune to do in VBA / Excel previously and its like trying to repaint a car with a paintbrush…. it will work, it will produce a result which works in the end but the process, the pain, the time and the end result will all leave you wondering if it was worth the effort. I would certainly never do it again that way.

You might be able to do what you want with some clever manual chopping etc – but it will be a PITA and you’ll need to set it up that way for each experiment.

The suggestion of using Python is a very good one. I appreciate you are probably not a Python developer and although it is simple enough to learn it sounds like a big job. Most accademics would probably look to Matlab (usually an accademic license around) or open source equivalent – but to my mind this is going to have its own challenges.

With Python I could actually process and return excel files, although most instruments will provide CSV files.

How much of a rush are you in?
Would you be able to supply a couple of sample files to test with?
Which version of excel are you using?
Which operating system (and 32/64 bit)?
Do you have admin rights to install software on the machine?

I might be able to nock up a simple script for you in a spare hour at the weekend if its as simple as it sounds…

I usually try to address this kind of thing using vlookup but I haven’t quite sorted how it would be used here. Do the rows and columns of each matrix have labels, or are they just the data values?

vlookup on its own wont work without directing it to the right matrix (table) by using match and offset first.

Hi Both,

The sample names are unique, but it’s the same 96 sets of wavelengths i.e I have 96 columns of 320nm, 96 columns of 322nm etc. But, that would be easy enough to fix by re-naming columns or rows and dragging along to ensure each has a unique identifier.

Basically, the instrument’s software integrates with excel to build the 127 output tables it generates. I’m at the stage of trying to make that template work for all future analyses.

I’m using excel 2007, and although my desktop (which I’m building the sheets on) is 32 bit, I can sit in the lab on the instrument’s workstation, which is 64 bit. I do have admin rights to both machines.

Many thanks – I see your emails are in your profiles. I’ll try to pull a template together with plenty annotations of what the instrument software does (and is fixed), and what can be changed.

Cheers,

Mark

Zokes, just one thing to consider, if you are going to have lots of data (96 sets of spectra per plate) have you given any thought to how you will:

– organise the data
– visualise the results
– search, sort, sift for interesting stuff

Excel is not ideally suited to any of those jobs with that volume of data…

as long as the list wavelengths are the same for each of the initial matrices and they are unique in their 96 list then that will be fine.

Hi all,

Thanks for the advice. Just spoken to another scientist here who’s had very similar issues with different data, and it looks like we’ll be able to crunch through this when he’s back from leave in a week or so.

I’ll let you know how this goes, and will resurrect the thread either with the resolution, or more questions!

Thanks again,

Mark

I’d just do a bit of conditional formatting and email the spreadsheet on to someone.

Zokes – woke up with the answer in my head so have put it in an example sheet if you want to pm me for a copy of it.

Id be happier waking up with images of Jenny Agutter in my head, but we dont choose our dreams 🙂

cold sweat?

Zokes, Are you sorted?

if not ping me an email with some sample data:

n (dot) polwart (at) gmx (dot) com

Hi Poly,

Yes, more or less (I think). As you say – Excel perhaps not best suited to this, but without investing a lot of time learning something else, it’s all i’ve got, and will just about do it with enough of a machine underneath it.

Stoner very kindly sent through a spreadsheet, that whilst I don’t quite understand the syntax fully, I’ve butchered his formulas enough to get what I think is what I need. However, that was 8.30 on a Friday evening (and I’d been in since 8 in the morning), and as it’s Australia Day here, I won’t be able to test it until Tuesday. I do have some more fiddling to do yet, but I think I’m there. I’m just glad I specced a Xeon workstation to drive the instrument – it’s already way beyond the meagre PC sat on my desk at work…

So thanks again for your offer of help, but hopefully now all sorted 🙂 I just need a bigger PC on my desk!

You should look into VBA, there’s pretty much nothing you can’t do with it and it’s free, built in to Excel and has a great IDE (integrated development environment).