Monitoring Data

[SenojNW]

Hi
I collect a lot of environmental monitoring data in my profession an
want to create a single spreadsheet to collect data from a list o
sites but with varying analytes.

The actual table will be fairly simple - columns for analytes and row
for sites. Not all sites will have all the total list of analytes bu
gaps wouldn't matter.

e.g. attached...

The problem I have is working out an easy way of importing data I ge
from labs into this table. It would be OK if I consistently got EXACTL
the same order of analytes in a spreadsheet from the labs but thi
doesn't happen.

I need to work out a way for excel to check the analyte categorie
(column headings) and place the data under the correct heading. The ro
categories (sites) don't matter as all data from one site will be in th
single row - I just want excel to shuffle the data to fit under th
correct column heading.

I'm not bad with excel but I only have a basic knowledge of macros an
pivot tables I can't do but want to learn.

Thanks

Nic

+-------------------------------------------------------------------
|Filename: example.gif
|Download: http://www.excelforum.com/attachment.php?postid=3675
+-------------------------------------------------------------------

 

[swatsp0p]

It would probably help us with an answer to your problem if we knew th
format of the "imported" data. Does it come in an Excel file, a CS
file, etc.?

Thanks

 

[SenojNW]

The imported data is just sent to me in excel spreadsheets... Have
look at the attachement in the last post - I did a couple of scree
captures to show a very basic example. The worksheet tabs should b
pretty self explanatory.

Cheer

 

[swatsp0p]

I have another question: On your database tab, the Sites are A-H, whil
on your Import tab, the sites include I, V, P, S....

Is this intended or simply examples? I am thinking a VLOOKUP might b
an option, but the Sites would need to match up

 

[SenojNW]

Yeah it was intentional.

An example...

... if a set of analysis comes in for site "A" a new row of data i
added under the appropriate analytes (columns) I had tested.

Then I check site "A" again in a few months and test some differen
analytes, or I receive a spreadsheet back from the lab with the analyt
columns in a different order to that in the existing database. I the
need Excel to check the heading and create a new entry (row) but plac
the data into the correct categories (columns) when it creates the ne
row (entry).

Thanks for the help.

Nic

 

[SenojNW]

and using the screen capture example;

Site A in the Database originally had analytes A,D, and F tested wit
these analytes represented in columns B,E and G.

Then the new set of data (Data To Import), Site "A" has had analytes
and A tested with these analyes represented in columns B and F.

So when the database adds a new row for site A (with a new date an
time) it needs to drop the values for analyte B into column C an
analyte A into column B

Make sense? :

 

[SenojNW]

I'll keep trying but if you can think of anything?

 

[SenojNW]

I am stumped..

 

[SenojNW]

OK - I've got it worked out with HLOOKUP - I'll post back when
finished....