Transpose Rows to Columns

[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 rows below row 1_12 for each individual soldier 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 following Data_ID 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 corresponding Column in step one.

Number of entries will be different for each soldier.

Worksheet has 1145 Soldiers and with 27486 rows of data.

Is this possible?

Original Worksheet

A B C D E F G H

Row_ID	Person_ID	Last Name​	First Name​	Middle​	Data_ID​	Research Data​
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 Exchanged​	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​
57	3	Adams​	Peter​	L​	1_1	Army​	Confederacy
58	3				1_2	Location​	Texas
59	3				1_3	Regiment​	10th Regiment Texas Infantry (Nelson's)
60	3				1_4	Function​	Infantry
61	3				1_5	Company​	D
62	3				1_6	Rank​	Private
63	3				1_7	Age​	--
64	3				1_8	Residence​	--
65	3				1_9	Enrolled​	Fairfield, Freestone County, Texas
66	3				1_10	Date​	October 26, 1861
67	3				1_11	Enlisted​	Hempstead, Texas
68	3				1_12	Date​	October 31 1863
69	3				10_1	Absent​	January 1862-April 1864
70	3				11_1	Transferred​	Refugees from Arkansas Post
71	3				11_1	Unit​	Company L, 8th Regiment Texas Infantry, Young's Regiment
72	3				11_1	Date​	February 3, 1863

New Worksheet would look like this

Person_ID

Last Name​

First Name​

Middle​

Detail​

Date​

Relieved of Duty​

Date​

Captured​

Date​

Forwarded​

Forwarded​

Prisoner​

Died​

Cause​

Forwarded for Exchanged​

Date​

Hospital​

Location​

Date​

Captured​

Date​

Forwarded​

Forwarded​

Arrived​

Forwarded​

Date​

Arrived​

Discharged​

Date​

Absent​

Transferred​

Unit​

Date​

1

Able​

Ezekiel​

Hospital Nurse

November 8, 1862

Nurse

February 4, 1862

Battle of Arkansas Post

January 11, 1863

St. Louis, Missouri via Boats

Camp Douglas, Illinois via Rail

Camp Douglas, Illinois

March 7, 1863

Small Pox

2

Adams​

John​

H​

Battle Arkansas Post, Arkansas

January 11, 1863

St. Louis, Missouri via Boats

Camp Douglas, Illinois via Rail

Camp Douglas, Illinois

City Point, Virginia

April 10, 1863

Sick

Kingston, Georgia

March 22, 1864

Franklin, Tennessee

November 30, 1864

Nashville, Tennessee

Louisville, Kentucky

December 3 1864

Camp Douglas, Illinois

December 3, 1863

December 6, 1863

Per G.O. No. 109, A.G.O.

June 18, 1865

 

[Peter_SSs]

Just checking on the sample data. Is it correct that for Row_ID = 15 and column "Research Data" the data is "Detail"? Or should it be "Date".
I'm asking because it seems to me that wherever else "Date" occurs in column G, it relates to the item in the row above. Further, in your sample results you have the heading "Date" above that particular entry.

 

[Peter_SSs]

Or should it be "Date"

Assuming it should be "Date", try this in a copy of your workbook.

Sub Rearrange()
  Dim dHdrs As Object, dID As Object
  Dim a As Variant, b As Variant, HdrBits As Variant, Hdr As Variant
  Dim i As Long, j As Long, k As Long, maxcol As Long
  
  a = Range("B1", Range("H" & Rows.Count).End(xlUp)).Value
  Set dHdrs = CreateObject("Scripting.Dictionary")
  dHdrs.CompareMode = 1
  Set dID = CreateObject("Scripting.Dictionary")
  dID.CompareMode = 1
  ReDim b(1 To Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Count, 1 To 4)
  maxcol = UBound(b, 2)
  For i = 3 To UBound(a)
    If Len(a(i, 5)) > 0 Then
      If Len(a(i, 2)) > 0 Then
        k = k + 1
        For j = 1 To 4
          b(k, j) = a(i, j)
        Next j
        dID.RemoveAll
      End If
      If Split(a(i, 5), "_")(0) > 1 Or Split(a(i, 5), "_")(1) > 12 Then
        If LCase(a(i, 6)) <> "date" Then
          If IsEmpty(dHdrs(a(i, 6))) Or (UBound(Split(dHdrs(a(i, 6)), "|")) = dID(a(i, 6))) Then
            maxcol = maxcol + 1
            ReDim Preserve b(1 To UBound(b), 1 To maxcol)
            dHdrs(a(i, 6)) = dHdrs(a(i, 6)) & "|" & maxcol
          End If
          dID(a(i, 6)) = dID(a(i, 6)) + 1
          j = Split(dHdrs(a(i, 6)), "|")(dID(a(i, 6)))
        Else
          j = Split(dHdrs(a(i - 1, 6)), "|")(dID(a(i - 1, 6))) + 1
        End If
        If j > maxcol Then
          maxcol = maxcol + 1
          ReDim Preserve b(1 To UBound(b), 1 To maxcol)
        End If
        b(k, j) = a(i, 7)
      End If
    End If
  Next i
  Sheets.Add After:=ActiveSheet
  With ActiveSheet
    .Range("A2").Resize(k, maxcol).Value = b
    .Range("A1:D1").Value = Application.Index(a, 1, Array(1, 2, 3, 4))
    For Each Hdr In dHdrs.keys
      HdrBits = Split(dHdrs(Hdr), "|")
      For i = 1 To UBound(HdrBits)
        .Cells(1, Val(HdrBits(i))).Value = Hdr
      Next i
    Next Hdr
    .Rows(1).SpecialCells(xlBlanks).Value = "Date"
    .UsedRange.EntireColumn.AutoFit
  End With
End Sub

 

[gzell]

Just checking on the sample data. Is it correct that for Row_ID = 15 and column "Research Data" the data is "Detail"? Or should it be "Date".
I'm asking because it seems to me that wherever else "Date" occurs in column G, it relates to the item in the row above. Further, in your sample results you have the heading "Date" above that particular entry.

You are correct. It should be date. I saw that as I posted the example, but failed to correct on worksheet. I will try it later today when i return home. I appreciate your help with this.

 

[gzell]

I am getting an error. It steps through but appears to break down when double entry such as "Date" in two consecutive rows. I am currently going through the code and identifing the errors and correcting. I appreciate the help and will let you know the outcome when I have cleaned up this information. I must compare it to the master files and verify what row can be deleted so it may take a few days.

Thanks again for the help.

 

[Peter_SSs]

.. appears to break down when double entry such as "Date" in two consecutive rows.

I agree that my code was not expecting such an occurrence and there was none like that in the sample. If you don't get this resolved yourself, I would be interested to see a very small set of sample data like this and the expected results.

Obviously I am not familiar with your data and exactly what it means but I'm wondering how you would get 2 Dates consecutively? For example, if there was a 'Captured' followed by two Dates I'm figuring in between those two dates the soldier would have to have been 'Released' or 'Escaped'?

 

[Peter_SSs]

Not tested heavily, but see if this deals appropriately with multiple consecutive 'Date' columns.

Sub Rearrange_v2()
  Dim dHdrs As Object, dID As Object
  Dim a As Variant, b As Variant, HdrBits As Variant, Hdr As Variant
  Dim i As Long, j As Long, k As Long, maxcol As Long, lr As Long
  
  a = Range("B1", Range("H" & Rows.Count).End(xlUp)).Value
  Set dHdrs = CreateObject("Scripting.Dictionary")
  dHdrs.CompareMode = 1
  Set dID = CreateObject("Scripting.Dictionary")
  dID.CompareMode = 1
  ReDim b(1 To Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Count, 1 To 4)
  maxcol = UBound(b, 2)
  For i = 3 To UBound(a)
    If Len(a(i, 5)) > 0 Then
      If Len(a(i, 2)) > 0 Then
        k = k + 1
        For j = 1 To 4
          b(k, j) = a(i, j)
        Next j
        dID.RemoveAll
      End If
      If Split(a(i, 5), "_")(0) > 1 Or Split(a(i, 5), "_")(1) > 12 Then
        If LCase(a(i, 6)) <> "date" Then
          If IsEmpty(dHdrs(a(i, 6))) Or (UBound(Split(dHdrs(a(i, 6)), "|")) = dID(a(i, 6))) Then
            maxcol = maxcol + 1
            ReDim Preserve b(1 To UBound(b), 1 To maxcol)
            dHdrs(a(i, 6)) = dHdrs(a(i, 6)) & "|" & maxcol
          End If
          dID(a(i, 6)) = dID(a(i, 6)) + 1
          j = Split(dHdrs(a(i, 6)), "|")(dID(a(i, 6)))
        Else
          lr = i - 1
          Do Until LCase(a(lr, 6)) <> "date"
            lr = lr - 1
          Loop
          j = Split(dHdrs(a(lr, 6)), "|")(dID(a(lr, 6))) + i - lr
        End If
        If j > maxcol Then
          maxcol = maxcol + 1
          ReDim Preserve b(1 To UBound(b), 1 To maxcol)
        End If
        b(k, j) = a(i, 7)
      End If
    End If
  Next i
  Sheets.Add After:=ActiveSheet
  With ActiveSheet
    .Range("A2").Resize(k, maxcol).Value = b
    .Range("A1:D1").Value = Application.Index(a, 1, Array(1, 2, 3, 4))
    For Each Hdr In dHdrs.keys
      HdrBits = Split(dHdrs(Hdr), "|")
      For i = 1 To UBound(HdrBits)
        .Cells(1, Val(HdrBits(i))).Value = Hdr
      Next i
    Next Hdr
    .Rows(1).SpecialCells(xlBlanks).Value = "Date"
    .UsedRange.EntireColumn.AutoFit
  End With
End Sub

 

[gzell]

I agree that my code was not expecting such an occurrence and there was none like that in the sample. If you don't get this resolved yourself, I would be interested to see a very small set of sample data like this and the expected results.

Obviously I am not familiar with your data and exactly what it means but I'm wondering how you would get 2 Dates consecutively? For example, if there was a 'Captured' followed by two Dates I'm figuring in between those two dates the soldier would have to have been 'Released' or 'Escaped'?

There would be duplicate data such as forwarded. The soldiers were captured and sent to St. Louis and I cannot find the exact date of when they arrived in St. Louis, after a couple of days they were segragated by rank and forwarded to another prison. But I should not have two "Date" in consecutive rows. I think it happened when I found an exact date instead of a general time period. It should not have happened. I have spent the past year trying to clean up the data but I missed these errors. There may be a better way to find the errors but I am stepping through each line of your code to find the errors and if I miss seeing the row when the error occurs, it wipes out all the entries in the Locals window and I start over.

I appreciate your help. This started out as a small group of men, a single company, then I became so interested in the Regiment it grew to what I have now. I should have created the columns from the beginning but I never thought it would become this large nor was I intending to share with other military researchers.

Thanks again for the help.

 

[Peter_SSs]

One thing that you could do is run the code from post #7 (my testing didn't error for multiple consecutive Date columns) and then have a look across the headings of the result sheet. If there were multiple consecutive 'date' cells in the data, then you should now find multiple consecutive 'Date' headings. If not a lot of data you could visually scan down those columns to see which row(s) contain dates in both columns.

If a lot of data then you could put AutoFilter on the data and go to the 2nd column where any multiple consecutive Date columns are and filter that column for non-blank. That should show any rows where there are 2 or more consecutive Date values.

Unfilter and repeat the above if there are more multi-Date columns in the header row.

Here is part of an example that I tested with

gzell Transpose.xlsm
	A	B	C	D	E	F	G	H
51	50	2				2_1	Arrived	December 3 1864
52	51	2				2_1	Forwarded	Camp Douglas, Illinois
53	52	2				2_1	Date	December 3, 1863
54	52	2				2_1	Date	December 4, 1863
55	52	2				2_1	Date	December 5, 1863
56	53	2				2_1	Arrived	December 6, 1863
Sheet1

.. and part of the results

gzell Transpose.xlsm
	Y	Z	AA	AB	AC	AD
1	Arrived	Forwarded	Date	Date	Date	Arrived
2
3	December 3 1864	Camp Douglas, Illinois	December 3, 1863	December 4, 1863	December 5, 1863	December 6, 1863
Sheet9

 

[gzell]

Thanks. I completed the clean up and it ran as expected. Again, I really appreciate your help. I will now go through each row and determine if all the information is there for each soldier but I have no doubt it will be or if not was caused by some other type of error in my spreadsheet.