Solved

need to re-arrange excel spreadsheet with a macro

Posted on 2014-07-25
5
259 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now