Text to Columns to Rows Macro

[Gimics]

Hello,

I am looking for some help or to repurpose a simple macro to do two things. I have a table of data that consists of a few columns of consistent data (ex. vendor names and vendor numbers) and then one column with cells full of comma delimited invoice numbers.

What I would like to do is convert the comma delimited cells into columns (easy enough, with the Text to Columns function), but then I would like to reformat the result into individual rows for each unique invoice, with the new rows maintaining the vendor name's and ID's that the existing line contains.

Example original:

	A	B	C
1	Vendor Name	Vendor Number	Invoices
2	XYZ Company	12345	5000-1, 5000-2, 5000-3

<tbody>
</tbody>

Example after text to columns:

	A	B	C	D	D
1	Vendor Name	Vendor Number	Invoices
2	XYZ Company	12345	5000-1	5000-2	5000-3

<tbody>
</tbody>

Example result:

	A	B	C
1	Vendor Name	Vendor Number	Invoice
2	XYZ Company	12345	5000-1
3	XYZ Company	12345	5000-2
4	XYZ Company	12345	5000-3

<tbody>
</tbody>

I have one vendor that has up to 69 invoices contained in one cell and using text to columns over the existing data results in 29,000 unique invoice records, from approximately 8,000 rows of vendors.

Hoping someone might be able to help out with a simple macro for creating and populating all of the unique records for each vendor.

Thanks in advance!

 

[Gimics]

Thanks both - Rick, if I could turn back time, I would. Hopefully the apology in my previous post didn't go unnoticed...

I updated Rick's macro to be more dynamic with the addition of one more variable and a for statement

Sub VendorInvoices()  Dim R As Long, X As Long, Z As Long, LastRow As Long, InvoiceCount As Long, Constants As Long
  Dim Data As Variant, Result As Variant, Invoices() As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Data = Range("A2:Q" & LastRow)
  Constants = 16
  InvoiceCount = Evaluate(Replace("SUM(1+LEN(Q2:Q#)-LEN(SUBSTITUTE(Q2:Q#,"","","""")))", "#", LastRow))
  ReDim Result(1 To InvoiceCount, 1 To Constants + 1)
  For R = 1 To UBound(Data)
    Invoices = Split(Data(R, Constants + 1), ",")
    For Z = 0 To UBound(Invoices)
      X = X + 1
      For Y = 1 To Constants
        Result(X, Y) = Data(R, Y)
      Next Y
      Result(X, Constants + 1) = Trim(Invoices(Z))
    Next
  Next
  With Range("S1").Resize(, Constants + 1)
    .Resize(X) = Result
    .EntireColumn.AutoFit
  End With
End Sub

It could be improved a bit more by removing the fixed column references (A, Q, S, etc.) and using a dynamic column count, but the above did the trick.

The output is the correct length, but I'll have to do some spot checks to make sure everything is captured.

Thanks again for the help!

 

[hiker95]

Thanks both

Gimics,

You are very welcome.

Glad that we could help.

I am sure that Rick Rothstein will be able to solve your request.

 

[Rick Rothstein]

Thanks both - Rick, if I could turn back time, I would. Hopefully the apology in my previous post didn't go unnoticed...

I updated Rick's macro to be more dynamic with the addition of one more variable and a for statement

Sub VendorInvoices()  Dim R As Long, X As Long, Z As Long, LastRow As Long, InvoiceCount As Long, Constants As Long
  Dim Data As Variant, Result As Variant, Invoices() As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Data = Range("A2:Q" & LastRow)
  Constants = 16
  InvoiceCount = Evaluate(Replace("SUM(1+LEN(Q2:Q#)-LEN(SUBSTITUTE(Q2:Q#,"","","""")))", "#", LastRow))
  ReDim Result(1 To InvoiceCount, 1 To Constants + 1)
  For R = 1 To UBound(Data)
    Invoices = Split(Data(R, Constants + 1), ",")
    For Z = 0 To UBound(Invoices)
      X = X + 1
      For Y = 1 To Constants
        Result(X, Y) = Data(R, Y)
      Next Y
      Result(X, Constants + 1) = Trim(Invoices(Z))
    Next
  Next
  With Range("S1").Resize(, Constants + 1)
    .Resize(X) = Result
    .EntireColumn.AutoFit
  End With
End Sub

It could be improved a bit more by removing the fixed column references (A, Q, S, etc.) and using a dynamic column count, but the above did the trick.

The output is the correct length, but I'll have to do some spot checks to make sure everything is captured.

I tried to run your code for data that I made up and it does not work for me (I get an error on the first line of code after the With statement... X has the value of 0). I am guessing I did not set up the sample data correctly. I did this because I wanted to see figure out you have and what you are trying to accomplish with it. For me, a picture really is worth far more than a thousand words. What I need to see rather than your code is your existing layout and the result layout you want from it... then I would know what you have and what you want from it.

 

[Gimics]

No worries Rick - it worked for me.

I kept the Invoices column as the last column in the "Data" range (column 17 in actuality, or "Constants +1"), and the updated code reflects. Everything else from the prior example is similar:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Vendor Name	Vendor Number	Constant 3	Constant 4	.	.	.	.	.	.	.	.	.	.	.	Constant 16	Invoices
2	XYZ Company	12345	.	.	.	.	.	.	.	.	.	.	.	.	.	.	5000-1, 5000-2, 5000-3

<tbody>
</tbody>

The output is exactly what I was looking for.

Thanks again for the help,