Splitting data in cells w/VBA macro

S

seattletimebandit

Board Regular
Joined
Apr 11, 2013
Messages
69
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
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...
Code: 
[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]
 
Upvote 0
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)...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top