Add two columns and fill columns based on other column data

[gcoug]

Hi all!

I'm having a senior moment and can't see the forest for the trees. I'm used to editing the data manually since I didn't do it that frequently. Now I do and I need to automate the process so I'm looking for something better than brute force.

I import text files of lab data (from a few rows to 10's of hundreds of rows) with the header row containing the field names. After importing, I would like to add two columns, name them (CVALUE and CUNITS), and then populate the rest of the columns with values based on data found in the VALUE and UNITS columns. I am converting the VALUE and UNITS from one unit to another unit (i.e. mg/kg, mg/L, or ug/g to ppm) -- read the UNIT, scale the VALUE to new CUNIT, and then save the CVALUE (calculated value) and CUNIT (calculated unit) into the new columns. I would like to add the columns to preserve the original values and units and not manipulate them directly.

Open to any and all suggestions --thank you!

g

[watersev]

hi gcoug, it would be helpful to see the result you need to obtain as a result of code execution

[gcoug]

hi watersev,

When I set it up manually I use a simple, no-frills UDF:

Please Login or Register  to view this content.

It returns a single value, CUNITS, and not "ppm". I insert the function in the first cell and filldown and then autofilter the column leaving out the blanks and add ppm to second column. I set the function up so it would just put blanks if the unit was not there (and leave room for future additions). I convert most mass concentration items to parts per million (a base unit of comparison) and don't care about the rest since I filter/massage the data in a couple of pivottables later in the analysis.

I know I could streamline the function but I set it up for ease of reading rather than efficiency.

Did I answer you question? Short story is VALUE=CVALUE for mg/L, ug/g, and mg/kg and the CUNIT for all three becomes "ppm". "ug/kg" divides VALUE by 1000 and the the CUNIT would be "ppm". I would like to have the values and not formulas populated in the cells.

g