Row to Column + Data

[gzell]

In the worksheet Column G is data Designation and Column H is data that goes with Column G. on each row. I need help in converting first 12 rows for each individual in column G to columns on new worksheet. The original rows have a Data_ID that corresponds with Data in Column G and H, not sure if this would help in the conversion.. Step one would include the rows with Data_ID 1_1, 1_2, 1_3, 1_4, 1_5, 1_6, 1_7, 1_8, 1_9, 1_10, 1_11, 1_12, (Column G) these would be moved to another worksheet and transposed to columns. Column H would then be moved to the new worksheet and the data entry would be placed on rows below the correct Column in step one.

Worksheet has 1145 Soldiers and with 27486 rows of data.

Current worksheet
A B C D E F G H

Row_ID	Person_ID​	Last Name	First Name	Middle	Data_ID	Research Data	Individual Information
1
2	1	Able​	Ezekiel​		1_1	Army​	Confederacy
3	1				1_2	Location​	Texas
4	1				1_3	Regiment​	10th Regiment Texas Infantry (Nelson's)
5	1				1_4	Function​	Infantry
6	1				1_5	Company​	C
7	1				1_6	Rank​	Private
8	1				1_7	Age​	--
9	1				1_8	Residence​	--
10	1				1_9	Enrolled​	Buchanan, Johnson County, Texas
11	1				1_10	Date​	October 16, 1861
12	1				1_11	Enlisted​	Houston, Texas
13	1				1_12	Date​	October 25, 1861
14	1				2_1	Detail​	Hospital Nurse
15	1				2_1	Detail​	November 8, 1862
16	1				3_1	Relieved of Duty​	Nurse
17	1				3_1	Date​	February 4, 1862
18	1				4_1	Captured​	Battle of Arkansas Post
19	1				4_1	Date​	January 11, 1863
20	1				4_2	Forwarded​	St. Louis, Missouri via Boats
21	1				4_3	Forwarded​	Camp Douglas, Illinois via Rail
22	1				4_4	Prisoner​	Camp Douglas, Illinois
23	1				4_5	Died​	March 7, 1863
24	1				4_5	Cause​	Small Pox
25
26	2	Adams​	John​	H​	1_1	Army​	Confederacy
27	2				1_2	Location​	Texas
28	2				1_3	Regiment​	10th Regiment Texas Infantry (Nelson's)
29	2				1_4	Function​	Infantry
30	2				1_5	Company​	I
31	2				1_6	Rank​	Corporal
32	2				1_7	Age​	42
33	2				1_8	Residence​	Johnson County, Texas
34	2				1_9	Enlisted​	Kimball Texas or Millican, Texas
35	2				1_10	Date​	January 16, 1862
36	2				2_1	Captured​	Battle Arkansas Post, Arkansas
37	2				2_1	Date​	January 11, 1863
38	2				2_1	Forwarded​	St. Louis, Missouri via Boats
39	2				2_1	Forwarded​	Camp Douglas, Illinois via Rail
40	2				2_1	Prisoner​	Camp Douglas, Illinois
41	2				2_1	Forwarded for Exchange​	City Point, Virginia
42	2				2_1	Date​	April 10, 1863
43	2				2_10	Hospital​	Sick
44	2				2_10	Location​	Kingston, Georgia
45	2				2_10	Date​	March 22, 1864
46	2				2_1	Captured​	Franklin, Tennessee
47	2				2_1	Date​	November 30, 1864
48	2				2_1	Forwarded​	Nashville, Tennessee
49	2				2_1	Forwarded​	Louisville, Kentucky
50	2				2_1	Arrived​	December 3 1864
51	2				2_1	Forwarded​	Camp Douglas, Illinois
52	2				2_1	Date​	December 3, 1863
53	2				2_1	Arrived​	December 6, 1863
54	2				13_1	Discharged​	Per G.O. No. 109, A.G.O.
55	2				13_1	Date​	June 18, 1865

New Worksheet would look like this

Row_ID

Person_ID

Last_Name​

First_Name​

Middle_Name​

Army​

Location​

Regiment​

Function​

Company

Rank​

Age

Residence​

Enrolled​

Date​

Enlisted​

Date​

2

1

Able​

Ezekiel​

Confederacy

Texas

10th Regiment Texas Infantry (Nelson's)

Infantry

C

Private

--

--

Buchanan, Johnson County, Texas

October 16, 1861

Houston, Texas

October 25, 1861

3

2

Adams​

John​

H​

Confederacy​

Texas​

10th Regiment Texas Infantry (Nelson's)​

Infantry​

I

Corporal​

42

Johnson County, Texas​

--​

--​

Kimball Texas or Millican, Texas​

January 16, 1862​

 

[Fluff]

How about

Sub gzell()
   Dim Ar As Areas
   Dim i As Long
   
   Set Ar = Sheets("Sheet1").Range("C:C").SpecialCells(xlConstants).Areas
   With Sheets.Add
      .Name = "New"
      .Range("A1:E1").Value = Ar(1).Offset(, -2).Resize(, 5).Value
      .Range("F1:Q1").Value = Application.Transpose(Ar(2).Offset(, 4).Resize(12).Value)
      For i = 2 To Ar.Count
         With .Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Value = i
            .Offset(, 1).Resize(, 4).Value = Ar(i).Offset(, -1).Resize(, 4).Value
            .Offset(, 5).Resize(, 12).Value = Application.Transpose(Ar(i).Offset(, 5).Resize(12).Value)
         End With
      Next i
   End With
End Sub

 

[gzell]

Great. Works perfect. Thanks for the support.

 

[Fluff]

You're welcome & thanks for the feedback

 

[gzell]

Thanks again for the help. The last step I would like to do the same action for the remainder rows for each soldier. There are more entries and not all are the same, because it is specific information for actions during the Civil War. It would be similar to the steps above, create new worksheet, called "Individual Data" and move data under each column as needed. As you can see from example in original thread, it is the rows below the 1_1-1_12. for each individual. Do I need to identify the number of columns that will be created on new work sheet or can it be done with an open ended statement on the number of new columns? If I need to open a new thread I will be glad to do that.

 

[Fluff]

This will need a new thread. Thanks