Solved

need to re-arrange excel spreadsheet with a macro

Posted on 2014-07-25
5
268 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 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