Transpose and filter

**Back2Basics** · 02-10-2009, 05:45 AM

I have some data that I need to re-organise so that it looks a lot nicer and easier to read. Basically I have a list of site names with dates and then a result. But I want to get it into the format of (column headers) name, date, chemical 1, chemical 2, chemical 3.

This part I can managed, but when i transpose the information I realised that there could potentially be several readings for the same site on the same date. I would like to get it so that all readins taken on the same day are in one line eg:

name date chemical 1 chemical 2 chemcial 3
site a 01/01/09 0.1 0.0 1.5
site a 05/02/09 0.1 0.3 0.9
site b 01/01/09 1.2 1.7 <0.01

where as at the moment I am getting

name date chem 1 chem 2 chem 3
site a 01/01/09 0.1
site a 01/01/09 0.0
site a 01/01/09 1.5
site a 05/02/09 0.1
site a 05/02/09 0.3
etc

**DonkeyOte** · 02-10-2009, 06:36 AM

One approach... to keep file size down I've only inserted a few rows of formulae - you should copy the formulae down as required.

If you prefer to do formulae-less you could use VBA.

EDIT: Scrap that -- just re-read your posts...

What happens if you have multiple lines on the same day for the same Determinand ?

**DonkeyOte** · 02-10-2009, 06:47 AM

See revised approach ... I think it's along the right lines...

Again, I have only inserted a few formulae on both sheets so you will need to copy down accordingly to get final results.

**Back2Basics** · 02-10-2009, 06:50 AM

Yeah I don't really have a clue about VBA tbh. I would like to try it at some point but don't know where to start.

In answer to your question there will never be the same det repeated on the same day, as these samples are done in person it wont get taken twice.

However this isn't quite what i have been trying to achive. I have found a way of getting what you have done already unfortunately.

But then i realised that it is niced than having alist but not the best way to view it as the same date for each site can be repeated. I then used this formula:

=VLOOKUP($A53&":"&$B53&":"&C$1, GWQMN_1ST_EXTRACT_Query!$C$1:$E$1716, 3, 0)

(for the nitrates column)

and copied to over the other two, this gave me all the available readings for the same date, but again i have several copies of this.

What i want is as the above formula but only one row for each site for that particular date. Is there a way of doing this, even if it is using vba and this will save me a lot of time in the future.

At the moment I am copying the above formlua to all cells and then sorting by date and manually deleting the duplicates, can take quite a while..... especially if there are say 20 dets, as there could be 20 rows for each site with the same date.

**Back2Basics** · 02-10-2009, 06:51 AM

Thats is much better, thanks so much.

Almost seems like my last post was either waste or you knew exactly what i was writing at the time of me writing it

Thanks Don