Splitting data in cells w/VBA macro

[seattletimebandit]

Curious to know if there is a way to take data from one table and reorder the data into into another table. My project is creating tables of soil sample data collected for environmental cleanup.


First table is output from another VBA routine.

• NOTE: the "GP-1" is the location of a soil sample collected; the trailing numbers are the depth the sample was collected ("1.8" feet, "5.5" feet, etc.)

[TABLE="class: grid, width: 801"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]GP-1-1.8[/TD]
[TD="align: center"]GP-1-5.5[/TD]
[TD="align: center"]GP-1-18.25
[/TD]
[TD="align: center"]GP-1-21
[/TD]
[TD="align: center"]GP-1-26[/TD]
[TD="align: center"]GP-1-34[/TD]
[TD="align: center"]GP-1-40[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD="align: center"]0.00844 U[/TD]
[TD="align: center"]0.0074 U[/TD]
[TD="align: center"]0.00749 U[/TD]
[TD="align: center"]0.0128 U[/TD]
[TD="align: center"]0.00885 U[/TD]
[TD="align: center"]0.00889 U[/TD]
[TD="align: center"]0.00901 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD="align: center"]0.0242 U[/TD]
[TD="align: center"]0.0212 U[/TD]
[TD="align: center"]0.0214 U[/TD]
[TD="align: center"]0.0205 U[/TD]
[TD="align: center"]0.0253 U[/TD]
[TD="align: center"]0.0254 U[/TD]
[TD="align: center"]0.0258 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD="align: center"]0.0302 U[/TD]
[TD="align: center"]0.0265 U[/TD]
[TD="align: center"]0.0268 U[/TD]
[TD="align: center"]0.0256 U[/TD]
[TD="align: center"]0.0317 U[/TD]
[TD="align: center"]0.0318 U[/TD]
[TD="align: center"]0.0322 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD="align: center"]0.0604 U[/TD]
[TD="align: center"]0.0529 U[/TD]
[TD="align: center"]0.0536 U[/TD]
[TD="align: center"]0.0513 U[/TD]
[TD="align: center"]0.0633 U[/TD]
[TD="align: center"]0.0636U[/TD]
[TD="align: center"]0.0644 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD="align: center"]0.0302 U[/TD]
[TD="align: center"]0.0265 U[/TD]
[TD="align: center"]0.0268 U[/TD]
[TD="align: center"]0.0256 U[/TD]
[TD="align: center"]0.0317 U[/TD]
[TD="align: center"]0.0318 U[/TD]
[TD="align: center"]0.0322 U[/TD]
[/TR]
[TR]
[TD]Gasoline[/TD]
[TD="align: center"]61.04[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD]PCE[/TD]
[TD="align: center"]6.04[/TD]
[TD="align: center"]5.29[/TD]
[TD="align: center"]5.36[/TD]
[TD="align: center"]5.13[/TD]
[TD="align: center"]6.33[/TD]
[TD="align: center"]6.36[/TD]
[TD="align: center"]6.44[/TD]
[/TR]
</tbody>[/TABLE]

I need to split the depths from the Sample ID. So it's copying the Sample ID and pasting to the first row/col, splitting off the depths into the following columns, or deleting the Sample ID from those columns, leaving only the depth (also removing the second dash "-")).

The output table looks like this:

[TABLE="class: grid, width: 801, align: left"]
<tbody>[TR]
[TD="align: center"]GP-1
[/TD]
[TD="align: center"]1.8
[/TD]
[TD="align: center"]5.5[/TD]
[TD="align: center"]18.25[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD="align: center"]0.00844 U[/TD]
[TD="align: center"]0.0074 U[/TD]
[TD="align: center"]0.00749 U[/TD]
[TD="align: center"]0.0128 J[/TD]
[TD="align: center"]0.00885 U[/TD]
[TD="align: center"]0.00889 U[/TD]
[TD="align: center"]0.00901 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD="align: center"]0.0242 U[/TD]
[TD="align: center"]0.0212 U[/TD]
[TD="align: center"]0.0214 U[/TD]
[TD="align: center"]0.0205 U[/TD]
[TD="align: center"]0.0253 U[/TD]
[TD="align: center"]0.0254 U[/TD]
[TD="align: center"]0.0258 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD="align: center"]0.0302 U[/TD]
[TD="align: center"]0.0265 U[/TD]
[TD="align: center"]0.0268 U[/TD]
[TD="align: center"]0.0256 U[/TD]
[TD="align: center"]0.0317 U[/TD]
[TD="align: center"]0.0318 U[/TD]
[TD="align: center"]0.0322 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD="align: center"]0.0604 U[/TD]
[TD="align: center"]0.0529 U[/TD]
[TD="align: center"]0.0536 U[/TD]
[TD="align: center"]0.0513 U[/TD]
[TD="align: center"]0.0633 U[/TD]
[TD="align: center"]0.0636 U[/TD]
[TD="align: center"]0.0644 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD="align: center"]0.0302 U[/TD]
[TD="align: center"]0.0265 U[/TD]
[TD="align: center"]0.0268 U[/TD]
[TD="align: center"]0.0256 U[/TD]
[TD="align: center"]0.0317 U[/TD]
[TD="align: center"]0.0318 U[/TD]
[TD="align: center"]0.0322 U[/TD]
[/TR]
[TR]
[TD]Gasoline[/TD]
[TD="align: center"]61.04[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD]Diesel[/TD]
[TD="align: center"]0.0604 U[/TD]
[TD="align: center"]0.0529 U[/TD]
[TD="align: center"]0.0536 U[/TD]
[TD="align: center"]0.0513 U[/TD]
[TD="align: center"]0.0633 U[/TD]
[TD="align: center"]0.0636 U[/TD]
[TD="align: center"]0.0644 U[/TD]
[/TR]
[TR]
[TD]PCE[/TD]
[TD="align: center"]6.04[/TD]
[TD="align: center"]5.29[/TD]
[TD="align: center"]5.36[/TD]
[TD="align: center"]5.13[/TD]
[TD="align: center"]6.33[/TD]
[TD="align: center"]6.36[/TD]
[TD="align: center"]6.44[/TD]
[/TR]
</tbody>[/TABLE]


















Is this even possible?

Thanks in advance!

stb

 

[Rick Rothstein]

You question is not state with enough specifics....

Where is the table? I'll assume it start in cell A1.

Do you want to fix the existing table in place or create a new table? I'll assume fix it in place.

Here is code will do what you want if my assumptions are correct...

[table="width: 500"]
[tr]
	[td]Sub SplitDepthsFromSampleID()
  Dim ID As String
  ID = Left(Range("B1").Value, InStrRev(Range("B1").Value, "-") - 1)
  Range("A1").Value = ID
  Range("B1", Cells(1, Columns.Count).End(xlToLeft)).Replace ID & "-", "", xlPart, , , , False
End Sub[/td]
[/tr]
[/table]

 

[seattletimebandit]

Rick,


You assumed correctly, starting in A1 was my intention. And for now replacing existing table is good.
The code works perfectly! Thanks!

I'm looking to expand the scope of this code.

First question:

Is it possible to select that same row of data (rather than having it start in "A1") and run the macro?

Second question:

My source table will actually be spanning quite a few columns with the SampleID/Depths to be split, and creating several tables from the source table
Example Source table:




[TABLE="class: grid, width: 785, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]GP-6-1.8
[/TD]
[TD="align: center"]GP-6-5.5[/TD]
[TD="align: center"]GP-6-18.25[/TD]
[TD="align: center"]GP-7-1.8[/TD]
[TD="align: center"]GP-7-5.5[/TD]
[TD="align: center"]GP-7-18.25[/TD]
[TD="align: center"]GP-8-1.8[/TD]
[TD="align: center"]GP-8-5.5[/TD]
[TD="align: center"]GP-8-18.25[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD="align: center"]0.00844[/TD]
[TD="align: center"]0.0074[/TD]
[TD="align: center"]0.00749[/TD]
[TD="align: center"]0.00844[/TD]
[TD="align: center"]0.0074[/TD]
[TD="align: center"]0.00749[/TD]
[TD="align: center"]0.00844[/TD]
[TD="align: center"]0.0074[/TD]
[TD="align: center"]0.00749[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD="align: center"]0.0242[/TD]
[TD="align: center"]0.0212[/TD]
[TD="align: center"]0.0214[/TD]
[TD="align: center"]0.0242[/TD]
[TD="align: center"]0.0212[/TD]
[TD="align: center"]0.0214[/TD]
[TD="align: center"]0.0242[/TD]
[TD="align: center"]0.0212[/TD]
[TD="align: center"]0.0214[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD="align: center"]0.0302[/TD]
[TD="align: center"]0.0265[/TD]
[TD="align: center"]0.0268[/TD]
[TD="align: center"]0.0302[/TD]
[TD="align: center"]0.0265[/TD]
[TD="align: center"]0.0268[/TD]
[TD="align: center"]0.0302[/TD]
[TD="align: center"]0.0265[/TD]
[TD="align: center"]0.0268[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD="align: center"]0.0604[/TD]
[TD="align: center"]0.0529[/TD]
[TD="align: center"]0.0536[/TD]
[TD="align: center"]0.0604[/TD]
[TD="align: center"]0.0529[/TD]
[TD="align: center"]0.0536[/TD]
[TD="align: center"]0.0604[/TD]
[TD="align: center"]0.0529[/TD]
[TD="align: center"]0.0536[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD="align: center"]0.0302[/TD]
[TD="align: center"]0.0265[/TD]
[TD="align: center"]0.0268[/TD]
[TD="align: center"]0.0302[/TD]
[TD="align: center"]0.0265[/TD]
[TD="align: center"]0.0268[/TD]
[TD="align: center"]0.0302[/TD]
[TD="align: center"]0.0265[/TD]
[TD="align: center"]0.0268[/TD]
[/TR]
[TR]
[TD]Gasoline[/TD]
[TD="align: center"]61.04[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]61.04[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]61.04[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1000[/TD]
[/TR]
[TR]
[TD]PCE[/TD]
[TD="align: center"]6.04[/TD]
[TD="align: center"]5.29[/TD]
[TD="align: center"]5.36[/TD]
[TD="align: center"]6.04[/TD]
[TD="align: center"]5.29[/TD]
[TD="align: center"]5.36[/TD]
[TD="align: center"]6.04[/TD]
[TD="align: center"]5.29[/TD]
[TD="align: center"]5.36[/TD]
[/TR]
</tbody>[/TABLE]














Output Tables (could be in the same worksheet, but creating a new worksheet would be slick!):

[TABLE="class: grid, width: 350, align: left"]
<tbody>[TR]
[TD="align: center"]GP-6
[/TD]
[TD="align: center"]1.8[/TD]
[TD="align: center"]5.5[/TD]
[TD="align: center"]18.25[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.00844 U[/TD]
[TD]0.0074 U[/TD]
[TD]0.00749 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0242 U[/TD]
[TD]0.0212 U[/TD]
[TD]0.0214 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.0302 U[/TD]
[TD]0.0265 U[/TD]
[TD]0.0268 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD]0.0604 U[/TD]
[TD]0.0529 U[/TD]
[TD]0.0536 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0302 U[/TD]
[TD]0.0265 U[/TD]
[TD]0.0268 U[/TD]
[/TR]
[TR]
[TD]Gasoline[/TD]
[TD]61.04[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]PCE[/TD]
[TD]6.04[/TD]
[TD]5.29[/TD]
[TD]5.36[/TD]
[/TR]
</tbody>[/TABLE]













[TABLE="class: grid, width: 350, align: left"]
<tbody>[TR]
[TD="align: center"]GP-7
[/TD]
[TD="align: center"]1.8[/TD]
[TD="align: center"]5.5[/TD]
[TD="align: center"]18.25[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.00844 U[/TD]
[TD]0.0074 U[/TD]
[TD]0.00749 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0242 U[/TD]
[TD]0.0212 U[/TD]
[TD]0.0214 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.0302 U[/TD]
[TD]0.0265 U[/TD]
[TD]0.0268 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD]0.0604 U[/TD]
[TD]0.0529 U[/TD]
[TD]0.0536 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0302 U[/TD]
[TD]0.0265 U[/TD]
[TD]0.0268 U[/TD]
[/TR]
[TR]
[TD]Gasoline[/TD]
[TD]61.04[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]PCE[/TD]
[TD]6.04[/TD]
[TD]5.29[/TD]
[TD]5.36[/TD]
[/TR]
</tbody>[/TABLE]













These could span many columns and in some cases there would be 3 depths where samples were collected, or 5 depths, or only 1 depth. So the unique primary key for a sample ID would be the first characters GP-6, GP-7, GP-8, etc.

Of course, I could just cut and paste each set of Sample ID/Depths and corresponding lab results since in most cases that can work fine as there may not be too many samples collected, but if I had a huge set of data where there were 30 or 60 sample locations collected with 5-6 depths, that would be a lot of cutting and pasting.

That all said, the code you showed is great and will save a lot of editing those columns.

Thanks again, you've helped me in the past. It's always greatly appreciated. And I learn a lot and am at least able to understand the code and can modify further. It's just that creating from scratch is one of my weak points.

stb-Russell

Hmmm...my formatting of my post is a bit skewed. To much white space between tables, and a third table (GP-8 didn't upload)...