Solved

need to re-arrange excel spreadsheet with a macro

Posted on 2014-07-25
5
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

690 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