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, ....
- 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!!