Stack columns but carry row data

[COWater]

I need help taking a table with multiple columns of water quality data and stacking a subset of the columns into one column, but carrying the associated row data like project, Location, latitude, etc.

Example table below.

You can see that our data is arranged by sampling event, with multiple parameters (columns) starting with pH. I need to take all of the parameters and stack them into a single column BUT each sample parameter needs to carry the row data with it, from Project column through Field Technician column.

I was lucky to find VBA code on this site that allows me to do exactly what I am asking, but the only one column of data is carried forward with each parameter. For example, I can stack all of the parameters (and leave skip blank cells) but only carrying over the "Project" column.

Example of the code I'm using is here:

Any help would be greatly appreciated. I'm happy to clarify anything that may not be clear.

THANKS!

 

[DanteAmor]

Hi @COWater, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Note XL2BB:

It would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.​

​

It is important to put the raw data and also the expected result.​

​

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in​

​

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.​

Note Code Tag:

In future please use code tags when posting code.​

How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[COWater]

Absolutely, thanks for these great resources. This is my first post (probably first of many), so please excuse my newbi-"ness"

Here is the XL2BB you reference above:

Brads_work.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
2	Project	Location	Latitude	Longitude	Sample Type	Sample Date	Sample Time	Season	Field Technician	pH	Water Temperature (°F)	Water Temperature (°C)	Specific Conductance (µS/cm)	TDS (mg/L)	pH	Carbonate	Calcium	Magnesium	Potassium	Sodium	Nitrate+Nitrite	Total Phosphorus	Total Sulfur	Chloride	Iron	Manganese	Zinc	Copper	Boron	Total Hardness	Lime	SAR	SARadj	Total Kjeldahl Nitrogen	Sulfate	Dissolved Arsenic	Dissolved Selenium	Dissolved Uranium	Total Arsenic	Total Selenium	Total Uranium
3	319 - Buffer Zone	Buffer Zone Drain	37.982151	-103.647266	Drain	6/12/2018	2:40 PM	Irrigation	C. Roweth	8.82		31.8	876	570
4	319 - Buffer Zone	Buffer Zone Drain	37.982151	-103.647266	Drain	8/2/2019	9:54 AM		C. Roweth	7.34		25.2	760	493
5	319 - Buffer Zone	Buffer Zone Drain	37.982151	-103.647266	Drain	5/19/2020	9:06 AM	Drain	C. Roweth	7.98		20.9	597	420
6	319 - Buffer Zone	Buffer Zone Drain	37.982151	-103.647266	Drain	6/2/2020	9:12 AM	Drain	C. Roweth	8.13		21	779	548
7	319 - Buffer Zone	Buffer Zone Drain	37.982151	-103.647266	Drain	6/15/2020	10:48 AM	Drain	C. Roweth	8.01		25.3	886	573
8	319 - Buffer Zone	Buffer Zone Irrigation	37.977932	-103.650757	Irrigation	6/12/2018	2:32 PM	Irrigation	C. Roweth	8.45		30.2	804	523
9	319 - Buffer Zone	Buffer Zone Irrigation	37.977932	-103.650757	Irrigation	7/12/2018	10:50 AM	Irrigation	C. Roweth	8.27		25.4	755	491	7.9	0	88	26	3	44	1.16	0.13	95.3	17	0.13	0.41	0.01	0	0.08	19.09	544	1.1	2.4	1.96	283.05	0	0.007	0.009	0.005	0.009	0.0098
10	319 - Buffer Zone	Buffer Zone Irrigation	37.977932	-103.650757	Tailwater	7/12/2018	10:30 AM	Irrigation	C. Roweth	7.75		19.4	816	530	7.4	0	95	28	9	46	0.73	0.59	97.6	21	0.01	0.39	0.02	0	0.12	20.59	522	1.1	2.5	1.96	289.91	0.004	0.011	0.0075	0.003	0.008	0.0075
11	319 - Buffer Zone	Buffer Zone Irrigation	37.977932	-103.650757	Irrigation	7/27/2018	11:15 AM	Irrigation	D. Oneal	8.05		24.7	734	477
12	319 - Buffer Zone	Buffer Zone Irrigation	37.977932	-103.650757	Tailwater	7/27/2018	11:20 AM	Irrigation	D. Oneal	8.09		30.4	760	493
13	319 - Buffer Zone	Buffer Zone Irrigation	37.977932	-103.650757	Irrigation	8/24/2018	11:55 AM	Irrigation	C. Roweth	8.33		27.9	1362	881
14	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	8/13/2018	10:55 AM	Irrigation	D. Oneal	6.98		20.8	3048	1983	7.1	0	534	86	1	101	6.56	0	567.8	37	0.01	0	0.08	0	0.27	98.56	941	1.1	3.3	2.01	1686.40	0	0.01	0.0589	0.039	0.011	0.0685
15	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	8/24/2018	11:37 AM	Irrigation	C. Roweth	7.22		21.7	3259	2123
16	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	9/21/2018	3:12 PM	Irrigation	C. Roweth	7.03		20.8	3398	2208
17	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	6/17/2019	2:10 PM	Irrigation	C. Roweth	7.75		16.1	3064	1981
18	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	7/18/2019	10:30 AM	Irrigation	C. Roweth	7.55		17.6	3015	1960																						0.007	0.005	0.0489	0.06	0.029	0.0773
19	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	8/2/2019	9:47 AM		C. Roweth	6.93		29.1	1910	1892
20	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	5/7/2020	12:53 PM	Well	M. Weber	7.34		17	3630	3141
21	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	5/19/2020	9:03 AM	Well	C. Roweth	7.54		12.6	2898	2457
22	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	6/3/2020	8:48 AM	Well	C. Roweth	7.65		15	4112	3320
23	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	6/15/2020	10:32 AM	Well	C. Roweth	7.13		15.4	4267	3347
24	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	6/26/2020	12:35 PM	Well	C. Roweth	7.82		16.7	4326	3344
25	319 - Buffer Zone	Buffer Zone North	38.998961	-103.637311	Well	7/14/2020	9:13 AM	Well	C. Roweth	7.19		19.44	5037	3274
26	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	8/7/2018	10:55 AM	Irrigation	D. Oneal	6.93		17.8	6358	4132
27	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	8/13/2018	11:00 AM	Irrigation	D. Oneal	6.96		17.7	6888	4477	7.1	0	530	215	1	925	0.01	0	1203.5	367	0.01	0	0.09	0	1.54	128.99	1656	8.5	28.6	1.01	3574.48	0	0.017	0.0616	0.012	0.012	0.0604
28	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	8/24/2018	11:43 AM	Irrigation	C. Roweth	7.05		18.7	6479	4212
29	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	9/21/2018	3:15 PM	Irrigation	C. Roweth	7.1		18.6	6049	3951
30	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	6/17/2019	2:15 PM	Irrigation	C. Roweth	7.6		14.6	5095	3304
31	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	7/18/2019	10:30 AM	Irrigation	C. Roweth	7.2		17.3	5030	3270																						0.003	0.022	0.0507	0.076	0.028	0.0783
32	319 - Buffer Zone	Buffer Zone South	37.978063	-103.648274	Well	8/2/2019	9:49 AM		C. Roweth	6.18		18.1	5293	5423
33	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	6/1/2018	1:04 PM	Irrigation	D. Oneal	6.94		15.6	3689	2027
34	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	6/1/2018	1:04 PM	Irrigation	M. Weber	6.94		15.6	3689	2027
35	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	6/4/2018	10:20 AM	Irrigation	D. Oneal	7.18		15	3530	2297	7.2	0	376	150	9	365	0	0	677.2	103	0.12	0.58	0.02	0	0.72	90.9	1,106	4	12.4	2.94	2,004.64
36	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	6/22/2018	10:25 AM	Irrigation	C. Roweth	6.91		16.5	2794	1816
37	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	6/25/2018	2:40 PM	Irrigation	C. Roweth	7.04		16.5	2800	1824
38	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	7/5/2018	3:06 PM	Irrigation	B. Brubacher	6.9		17.5	2684	1742
39	319 - Lateral Lining	McClave 1	38.143266	-102.851549	Well	7/10/2018	2:00 PM	Irrigation	C. Roweth	6.87		18.7	2793	1815																						0	0	0.0564	0.022	0.005	0.0618
Sheet3

 

[COWater]

And here is the code in proper format (I'm not sure if this code is really what I'm after, so feel free to modify, edit, or delete and rework something new).

Sub stackcolumn_v2()

Dim a As Variant, lr As Long, lc As Long, n As Long, r As Long, c As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(.Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.CountA(.Range(.Cells(2, 2), .Cells(lr, lc))) + 1
  ReDim o(1 To n, 1 To 3)
  j = j + 1: o(j, 1) = "": o(j, 2) = ""
  For r = 3 To lr
    For c = 11 To lc
      If Not a(r, c) = vbEmpty Then
        j = j + 1: o(j, 1) = a(r, 1)
        o(j, 2) = a(r, c)
      End If
    Next c
  Next r
  .Cells(1, lc + 2).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Hi Cowater and thanks for the example in the minisheet.

I propose the following:

1. Create a new sheet and name it "Result" to put the result.​

2. In the results sheet, I added a column to put the name of the parameter (column J) and in the next column the value of the parameter (column K).​

If you don't want the name of the parameter, change these lines:​

b(m, 10) = a(1, j)​

b(m, 11) = a(i, j)​

By this line:​

b(m, 10) = a(i, j)​

----- --

Put the following code in a module.
Don't forget to adjust the sheet names in the code.

Sub stackcolumn_v3()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim i&, j&, k&, lr&, lc&, n&, m&
 
  Set sh1 = Sheets("Raw")       'Fit to your sheets's name
  Set sh2 = Sheets("Result")    'New sheet
 
  lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  a = sh1.Range("A1", sh1.Cells(lr, lc)).Value
  n = Application.CountA(sh1.Range("J2", sh1.Cells(lr, lc))) + 1
  ReDim b(1 To n, 1 To 11)
 
  For i = 2 To UBound(a, 1)
    For j = 10 To UBound(a, 2)
      If a(i, j) <> "" Then
        m = m + 1
        For k = 1 To 9
          b(m, k) = a(i, k)
        Next
        b(m, 10) = a(1, j)
        b(m, 11) = a(i, j)
      End If
    Next j
  Next i
 
  sh2.Cells.ClearContents
  sh2.Range("A1:I1").Value = sh1.Range("A1:I1").Value
  sh2.Range("J1:K1").Value = Array("Parameter", "Value")
  sh2.Range("A2").Resize(UBound(b, 1), UBound(b, 2)) = b
  sh2.UsedRange.Columns.AutoFit
End Sub

Run the macro, you will see the results in the "result" sheet

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[COWater]

Yes! That worked exactly like I was hoping it would. Big thanks @DonteAmor!!!