combine data from two worksheets with similar data but different formats

[Ev1lZer0]

Hello!

I have two workbooks, workbook 1 is 907 (Including the header) rows looks like this:

Copy of Felony cases with DV 2023 2024.xlsx
	A	B	C	D	E	F	G
1	DATE	CASE NUMBER	NAME	CASE TYPE	RCW	LE	ENHANCEMENT
2	10/23/2024	24-0-00001-39	John	Assault	1 -- 9A.36.011(1)(a) ~ FIRST DEGREE ASSAULT 1 -- 9A.36.011(1)(d) ~ FIRST DEGREE ASSAULT / 10.99.020(7) ~ Domestic Violence - Family or Household Member 2 -- 9A.56.065 and 9A.56.020(1)(a) ~ THEFT OF A MOTOR VEHICLE / 10.99.020(7) ~ Domestic Violence - Family or Household Member 3 -- 9.41.040(2)(a)(CCC) ~ SECOND DEGREE UNLAWFUL POSSESSION OF A FIREARM	YSO
3	10/23/2024	24-0-00002-39	Jane	Assault	1 -- 9A.36.021(1)(a) ~ SECOND DEGREE ASSAULT / 9.94A.533(4) and 9.94A.825 ~ Enhancement - Deadly Weapon - Class B / 10.99.020(8) ~ Domestic Violence - Intimate Partner 2 -- 9A.36.021(1)(c) ~ SECOND DEGREE ASSAULT / 9.94A.533(4) and 9.94A.825 ~ Enhancement - Deadly Weapon - Class B / 10.99.020(8) ~ Domestic Violence - Intimate Partner 3 -- 9A.72.120 ~ TAMPERING WITH A WITNESS 4 -- 9A.72.120 ~ TAMPERING WITH A WITNESS / 10.99.020(8) ~ Domestic Violence - Intimate Partner 5 -- 9A.36.041(2) ~ FOURTH DEGREE ASSAULT / 10.99.020(8) ~ Domestic Violence - Intimate Partner 6 -- 9A.36.041(2) ~ FOURTH DEGREE ASSAULT / 10.99.020(8) ~ Domestic Violence - Intimate Partner	YPD	X
4	10/25/2024	24-0-00003-39	Wendy	Assault	1 -- 9A.46.020(1)(a)(i)(b) and (2)(b)(ii) ~ FELONY HARASSMENT OF ANOTHER – THREAT TO KILL / 10.99.020(7) ~ Domestic Violence - Family or Household Member 2 -- 9A.46.020(1) and (2)(a) ~ HARASSMENT OF ANOTHER / 10.99.020(7) ~ Domestic Violence - Family or Household Member 3 -- 9A.76.020 ~ OBSTRUCTING A LAW ENFORCEMENT OFFICER	YSO
Sheet1

Workbook 2 is 453 rows (including the header) and looks like this:

Felony cases with DV 2023 2024.xlsx
	A	B	C	D	E	F	G
1	DATE	CASE NUMBER	NAME	CASE TYPE	RCW	LE	ENHANCEMENT
2	10/23/2024	24-0-00004-39	John 2	Assault	26.50.010(7)	YPD
3	10/23/2024	24-0-00005-39	Jane 2	Assault	9.94A.533(4) & 9.94A.825; 26.50.010(6); 26.50.010(7); 9A36.041 (2) X5	YSO	X
4	10/25/2024	24-0-00006-39	Wendy 2	Assault	9A.36.011(1)(a); 10.99.020(8) x2; 9A.48.090; 26.50.010(7)	YSO
Sheet1

The first step is to make column e in Workbook 2 look like column e in Workbook 1 - especially column E - obviously, can't get the text to be added without manual input. Also please notice from above how each new line is immediately after a semi-colon ; ... I removed the semi-colon below. the new Workbook 2 should look like this:

Felony cases with DV 2023 2024.xlsx
	B	C	D	E	F	G
1	CASE NUMBER	NAME	CASE TYPE	RCW	LE	ENHANCEMENT
2	24-0-00004-39	John 2	Assault	1 -- 26.50.010(7)	YPD
3	24-0-00005-39	Jane 2	Assault	1 -- 9.94A.533(4) & 9.94A.825 2 -- 26.50.010(6) 3 -- 26.50.010(7) 4 -- 9A36.041 (2) X5	YSO	X
4	24-0-00006-39	Wendy 2	Assault	1 -- 9A.36.011(1)(a) 2 -- 10.99.020(8) x2 3 -- 9A.48.090 4 -- 26.50.010(7)	YSO
Sheet1

Once this is done, I can copy and paste the data from one into the other.

However, there is a second and separate step. I actually want the end goal of the data to have each RCW in a row by itself, like this:

Felony cases with DV 2023 2024.xlsx
	B	C	D	E	F	G
1	CASE NUMBER	NAME	CASE TYPE	RCW	LE	ENHANCEMENT
2	24-0-00004-39	John 2	Assault	1 -- 26.50.010(7)	YPD
3	24-0-00005-39	Jane 2	Assault	1 -- 9.94A.533(4) & 9.94A.825	YSO	X
4	24-0-00005-39	Jane 2	Assault	2 -- 26.50.010(6)	YSO	X
5	24-0-00005-39	Jane 2	Assault	3 -- 26.50.010(7)	YSO	X
6	24-0-00005-39	Jane 2	Assault	4 -- 9A36.041 (2) X5	YSO	X
7	24-0-00006-39	Wendy 2	Assault	1 -- 9A.36.011(1)(a)	YSO
8	24-0-00006-39	Wendy 2	Assault	2 -- 10.99.020(8) x2	YSO
9	24-0-00006-39	Wendy 2	Assault	3 -- 9A.48.090	YSO
10	24-0-00006-39	Wendy 2	Assault	4 -- 26.50.010(7)	YSO
Sheet1

 

[DanteAmor]

Try the following macro:
Copy sheet1 from workbook1 to the workbook with the macro and call it Sheet1x.
Copy sheet1 from workbook2 to workbook with the macro and call it Sheet1y.
In the workbook with the macro create a sheet and call it Sheet3.

Put the macro in the macro workbook, save the file as macro-enabled excel.

Run the macro and the result will be on Sheet3.

Sub separateInformation()
  Dim sha As Worksheet, shb As Worksheet, sh3 As Worksheet
  Dim i As Long, k As Long, m As Long, n As Long, q As Long
  Dim rcw As Variant, a As Variant, b As Variant
 
  Set sha = Sheets("Sheet1x")
  Set shb = Sheets("Sheet1y")
  Set sh3 = Sheets("Sheet3")
  sh3.Range("A2:G" & Rows.count).ClearContents
 
  'First sheet
    a = sha.Range("A2:G" & sha.Range("A" & Rows.count).End(3).Row).Value
    For i = 1 To UBound(a)
      n = n + Len(a(i, 5)) - Len(Replace(a(i, 5), Chr(10), "")) + 1
    Next
    ReDim b(1 To n, 1 To UBound(a, 2))
   
    For i = 1 To UBound(a)
      For Each rcw In Split(a(i, 5), Chr(10))
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          If m = 5 Then b(k, m) = rcw
        Next
      Next
    Next
    sh3.Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
 
  'Second sheet
    n = 0
    a = shb.Range("A2:G" & shb.Range("A" & Rows.count).End(3).Row).Value
    For i = 1 To UBound(a)
      n = n + Len(a(i, 5)) - Len(Replace(Replace(a(i, 5), ":", ";"), ";", "")) + 1
    Next
    ReDim b(1 To n, 1 To UBound(a, 2))
   
    k = 0
    For i = 1 To UBound(a)
      q = 0
      For Each rcw In Split(Replace(a(i, 5), ":", ";"), ";")
        q = q + 1
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          If m = 5 Then b(k, m) = q & " -- " & rcw
        Next
      Next
    Next
    sh3.Range("A" & Rows.count).End(3)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[Ev1lZer0]

Unfortunately, your macro didn't work - it only combined both sheets as they were into sheet 3 with no modifications. However, it did lead me to a simple find-and-replace solution.

I selected column e and used a find and replace to find ';' and replaced it with a line break. Using 'crtl+ shift + j' in the replace. After that I was able to just do a copy-and-paste to combine them.

I still do have the issue though about how to add the:
'1 -- '
'2 -- '
'3 -- '
...... and so on to the beginning of each new line break in column e.

 

[DanteAmor]

That is the result of my macro on the sheet.
If you follow my instructions in post #2.

Your first example on sheet1a, the second example on sheet1b, that's it, you run the macro and the result will be on sheet3

 

[Ev1lZer0]

The first time i ran it i ended up with the result i mentioned above. This time, i ended up with this error:

When i click okay, the following is highlighted:

What am i doing wrong?

I have placed my data from Workbook 1 into 'sheet1x' and my data from workbook 2 into 'sheet1y' and created a 'sheet3.'

I opened up the Macro window by pressing 'alt+F11' selected "This workbook" and copied and pasted the code for the macro into the window.

saved the file as a .xltm opened the macro window again by using 'alt+F11' and hit the play button

 

[Ev1lZer0]

I wish I could take back my previous response. I stand by the issues I ran into the first time. but upon trying the second time, I realized that I failed to copy the entire code, and once I resolved that, the macro worked.

I did find another issue with my data, however. I noticed that the RCW were not JUST separated by the semi-colon ';' they are also separated by commas ',' and colons ":'

Can this be addressed in your macro? and would I have to start from scratch? Ie. the original spreadsheets, or would I run this altered macro specifically for sheet 3?

Thank you in advance!

 

[DanteAmor]

I did find another issue with my data, however. I noticed that the RCW were not JUST separated by the semi-colon ';' they are also separated by commas ',' and colons ":'

The macro already considered the semicolon and the 2 points.
Now I have done the update. You can run the macro.

The macro works with the source data of the 2 sheets (sheet1a and sheet1b) and it always puts the new result on sheet3.

Use this macro:

Sub separateInformation()
  Dim sha As Worksheet, shb As Worksheet, sh3 As Worksheet
  Dim i As Long, k As Long, m As Long, n As Long, q As Long
  Dim rcw As Variant, a As Variant, b As Variant
  
  Set sha = Sheets("Sheet1x")
  Set shb = Sheets("Sheet1y")
  Set sh3 = Sheets("Sheet3")
  sh3.Range("A2:G" & Rows.count).ClearContents
  
  'First sheet
    a = sha.Range("A2:G" & sha.Range("A" & Rows.count).End(3).Row).Value
    For i = 1 To UBound(a)
      n = n + Len(a(i, 5)) - Len(Replace(a(i, 5), Chr(10), "")) + 1
    Next
    ReDim b(1 To n, 1 To UBound(a, 2))
    
    For i = 1 To UBound(a)
      For Each rcw In Split(a(i, 5), Chr(10))
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          If m = 5 Then b(k, m) = rcw
        Next
      Next
    Next
    sh3.Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  
  'Second sheet
    n = 0
    a = shb.Range("A2:G" & shb.Range("A" & Rows.count).End(3).Row).Value
    For i = 1 To UBound(a)
      n = n + Len(a(i, 5)) - Len(Replace(Replace(Replace(a(i, 5), ",", ""), ":", ""), ";", "")) + 1
    Next
    ReDim b(1 To n, 1 To UBound(a, 2))
    
    k = 0
    For i = 1 To UBound(a)
      q = 0
      For Each rcw In Split(Replace(Replace(a(i, 5), ",", ";"), ":", ";"), ";")
        q = q + 1
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          If m = 5 Then b(k, m) = q & " -- " & rcw
        Next
      Next
    Next
    sh3.Range("A" & Rows.count).End(3)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub