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

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

e.g. attached...

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

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

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

Thanks

Nick


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

--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282

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

Thanks.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393282

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

Cheers


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282

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

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393282

Yeah it was intentional.

An example...

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

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

Thanks for the help.

Nick


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282

and using the screen capture example;

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

Then the new set of data (Data To Import), Site "A" has had analytes B
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 and
time) it needs to drop the values for analyte B into column C and
analyte A into column B

Make sense? :)


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282

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


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282

I am stumped...


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282

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


--
SenojNW
------------------------------------------------------------------------
SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958
View this thread: http://www.excelforum.com/showthread...hreadid=393282