need to re-arrange excel spreadsheet with a macro

Hi, I need a macro that will re-arrange a worksheet and place the results in a new worksheet.   Basically, the macro needs to create a new worksheet, add some columns to the new worksheet, and populate the new worksheet (from data in the old) based on a few rules that I define below.  See my attached excel file for reference.

As you can see, my spreadsheet contains numerous rows of data for the same sample location (column = "LOCDESR") and sample date (column = "SUBDATE") combination.  Instead, I need all the results collected at the same location on the same date to be stored in a SINGLE row.  For example, 9 rows have the same combination of [LOCDESCR and SUBDATE] where LOCDESCR  = "little tennessee riv at SR 1651 NR prentiss" and SUBDATE = "1/19/2006".  Note that each of these rows is associated with a different analyte (see column = "ANALYTE").   (For that [LOCDESCR and SUBDATE], the spreadsheet has results for 9 analytes, one per row: ”Cd by ICPMS”, “Cr by ICPMS”, “Cu by ICPMS”, “Ni by ICPMS”, “Pb by ICPMS”, ”Zn by ICP”, “As by ICMS”, “Hg 245.1”, and “Al by ICP”.)  Instead of those 9 rows, I need a SINGLE row containing all of those analyte data as new, freshly populated columns.  

So.... here are the rules.  

 What columns should be in the new worksheet?   Answer:  

- All the columns containing basic data as before:   ID, SAMPNO, LOCCODE, LOCDESCR, SUBDATE, OWNER, COUNTY, PROJECT, ....
 PLUS  
- two new columns (titled X-water, and X-water-PQL) for each unique chemical element in ANALYTE, where X represents the abbreviated name of the chemical element.  

(The unique chemical elements are: Ag, Al, Sb (=antimony), As (=arsenic), B, Ba, Be, Ca, Cd (=cadmium), Co, Cr, Cu, Fe (=iron), hardness, Hg, K, Pb (=lead), Li, Mg, Mn, Mo, Na, Ni, Pb, Se, Sn, Sr (=strontium), thallium, Ti, V, Zn.)

Note that columns RESULT AND ANALYTE will be deleted and their contents moved to the new sheet (since there will now be a new column to store that data).

How do we populate the new worksheet?   Answer:
a.      Each row in the new sheet will represent a unique combination of [LOCDESCR and SUBDATE].
b.      Each row in the original sheet will be used to populate the new sheet.
c.      X-water and X-water-PQL are populated from from rows in the original sheet with ANALYTES that do NOT contain the word “solid”.    (Ignore the rows with ANALYTES containing the word "solid".)
d.    Since the original data were pasted from an access db, some of the cells in the worksheet are not functional as numbers.  Those cells (PQL and RESULTS columns) should be made to function as numbers.

Ideally, the macro would work for any other original spreadsheet that contains similar data/formats...this will allow me to re-use it for other downloads.

Thanks in advance!!

TC
for-expertexchange-metalsdb.xlsx
Cam RabenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
do a macro record of the manual process as much as possible,  upload the code generated.
helps us to follow process.
0
Glenn RayExcel VBA DeveloperCommented:
HI Ted,

This is a rather involved request - more like a project.  The Experts here do this voluntarily for nothing but pride and points (and well, the T-shirts are nice!).

It would help greatly if you could present an example workbook that - instead of having two sheets of similar data - had a "Source" (or "before" or "old") sheet and a "Destination" (or "after" or "new") sheet with the same sample data represented in each.  My experience has been that when this kind of sample data is provided, the solutions from Experts are provided faster and more-accurately.

Just 20-30 records of data should suffice.

Regards,
-Glenn
0
Cam RabenAuthor Commented:
Thanks Glenn.  Great idea.  I'll do this tomorrow!

TC
0
Cam RabenAuthor Commented:
Thanks all.  This may be too complicated to describe and do via a macro.  I'll break it down to a bite sized chunk and re-post in the Access DB "need a query" area of EE.    

TC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cam RabenAuthor Commented:
I withdrew the question and will re-post in a more simplified way in a different area of EE (Access DB queries).  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.