Solved

need to re-arrange excel spreadsheet with a macro

Posted on 2014-07-25
5
265 Views
Last Modified: 2014-08-02
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
0
Comment
Question by:Cam Raben
  • 3
5 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40221261
do a macro record of the manual process as much as possible,  upload the code generated.
helps us to follow process.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40221764
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
 

Author Comment

by:Cam Raben
ID: 40223335
Thanks Glenn.  Great idea.  I'll do this tomorrow!

TC
0
 

Accepted Solution

by:
Cam Raben earned 0 total points
ID: 40224391
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
 

Author Closing Comment

by:Cam Raben
ID: 40235896
I withdrew the question and will re-post in a more simplified way in a different area of EE (Access DB queries).  Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question