Need help with MAcro for Multi-value cell and row processing

[jfrick]

How can I handle a cell with multiple values in it? Can this be done
in Excel with macros?

I am new to macros and need help.

Here is my challenge:
I have a row of data that has one cell with multiple values in that
cell. I must be able to create multiple rows for each of these
values. For example:

Company name, company address, company city, company codes
ABC Widgets, 100 Main Street, New York, 005980542;017230022;017231132

Notice that the Company Code cell has semicolons separating three
company codes. The semicolons indicate that the particular company on
that row has more than one company code. I have many companies with
only one value. I also have companies that have up to 7 codes.

So my problem is how to take hundreds of these rows and create one row
per company code. The result of the above example should look like
this:

Company name, company address, company city, company codes
ABC Widgets, 100 Main Street, New York, 005980542
ABC Widgets, 100 Main Street, New York, 017230022
ABC Widgets, 100 Main Street, New York, 017231132

If I were doing this in a programming language, I might use logic like
this:

Go to the first row. Go to the company code cell.

Count the original number of semicolons. Copy the row I am on exactly
the number of semicolons. (Note: 6 codes will have 5 semicolons.
Since one row already exists, I only need to copy the row 5 times, and
thus I end up with 6 rows for this company.

On the row I am already on, the first row, delete all characters in
the company code cell from the first semicolon to the right. (This
does row 1 and code 1.)

Skip a row. Go to the company code cell. Locate semicolon 1. Delete
from the semicolon to the left. Locate the new first semicolon,
(formerly semicolon 2). Delete all characters from the first
semicolon to the right. (This does row 2 and code 2.)

Skip a row. Go to the company code cell. Locate semicolon 2.
(Remember that on this new row, the company code cell contains all of
the original company codes and semicolons.) Delete from the semicolon
to the left. Locate the new first semicolon, (formerly semicolon 3).
Delete all characters from the first semicolon to the right. (This
does row 3 and code 3.)

And so on until my row count equals the number of company codes. (An
alternate way of expressing this is to count the number of times I
skip to the next row, and this should match the number of semicolons I
found.)

What I don't know how to do is put this logic into a macro or do this
on a spreadsheet.

How do process and track the row? How do I keep count of the rows and
the semicolons when moving from row to the next row?

Any tips or thoughts would be greatly appreciated.

 

[Jim Cone]

This will do one cell (A5).
It first parses the text into an array (vArr) separated by commas.
It then takes the last vArr element and parses it again into another array (vLast) separated by semi-colons.
The constant portion of the text is determined using InStrRev.
The cell is increased to three rows (Resize)
The constant portion plus each element from vLast is added to the 3 cells in a loop.
(there has to be a better way <g>)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


'--
Sub FromOneToMany()
Dim rng As Range
Dim rngCell As Range
Dim vArr As Variant
Dim vLast As Variant
Dim lngCount As Long
Dim lngLast As Long
Dim N As Long

Set rngCell = Range("A5")

vArr = VBA.Split(rngCell.Value, ",")
vLast = VBA.Split(vArr(UBound(vArr)), ";")
lngCount = UBound(vLast) + 1
Set rng = rngCell.Resize(lngCount, 1)
rng.Value = rngCell.Value
lngLast = InStrRev(rngCell.Value, ",") - 1
For N = lngCount To 1 Step -1
rng(N).Value = VBA.Left$(rngCell.Value, lngLast) & _
" " & VBA.Trim$(vLast(N - 1))
Next
Set rng = Nothing
Set rngCell = Nothing
End Sub
'--


"jfrick"
<[email protected]>
wrote in message
How can I handle a cell with multiple values in it? Can this be done
in Excel with macros?

I am new to macros and need help.
Here is my challenge:
I have a row of data that has one cell with multiple values in that
cell. I must be able to create multiple rows for each of these
values. For example:

Company name, company address, company city, company codes
ABC Widgets, 100 Main Street, New York, 005980542;017230022;017231132

Notice that the Company Code cell has semicolons separating three
company codes. The semicolons indicate that the particular company on
that row has more than one company code. I have many companies with
only one value. I also have companies that have up to 7 codes.

So my problem is how to take hundreds of these rows and create one row
per company code. The result of the above example should look like
this:

Company name, company address, company city, company codes
ABC Widgets, 100 Main Street, New York, 005980542
ABC Widgets, 100 Main Street, New York, 017230022
ABC Widgets, 100 Main Street, New York, 017231132

If I were doing this in a programming language, I might use logic like
this:

Go to the first row. Go to the company code cell.

Count the original number of semicolons. Copy the row I am on exactly
the number of semicolons. (Note: 6 codes will have 5 semicolons.
Since one row already exists, I only need to copy the row 5 times, and
thus I end up with 6 rows for this company.

On the row I am already on, the first row, delete all characters in
the company code cell from the first semicolon to the right. (This
does row 1 and code 1.)

Skip a row. Go to the company code cell. Locate semicolon 1. Delete
from the semicolon to the left. Locate the new first semicolon,
(formerly semicolon 2). Delete all characters from the first
semicolon to the right. (This does row 2 and code 2.)

Skip a row. Go to the company code cell. Locate semicolon 2.
(Remember that on this new row, the company code cell contains all of
the original company codes and semicolons.) Delete from the semicolon
to the left. Locate the new first semicolon, (formerly semicolon 3).
Delete all characters from the first semicolon to the right. (This
does row 3 and code 3.)

And so on until my row count equals the number of company codes. (An
alternate way of expressing this is to count the number of times I
skip to the next row, and this should match the number of semicolons I
found.)

What I don't know how to do is put this logic into a macro or do this
on a spreadsheet.

How do process and track the row? How do I keep count of the rows and
the semicolons when moving from row to the next row?

Any tips or thoughts would be greatly appreciated.

 

[Jim Cone]

Correction...
The line "rng.Value = rngCell.Value" can be removed.
It doesn't hurt but it is not necessary.
Jim Cone

 

[jfrick]

Thanks for such a quick response.

I have tried the macro, but I am getting an error. The error says,

-----
Run-time error '9':

Subscript out of range
-----

The dialog then gives me the choice of End Debug or Help

When I click Debug, the editor takes me to this line:


vLast = VBA.Split(vArr(UBound(vArr)), ";")


I am not sure I am starting this correctly, since I do not understand
the first line of your post, "This will do one cell (A5)." What am I
doing wrong? Or how should I start this macro? Where should I be on
the worksheet or does it even make a difference?

I have tried starting the macro from the first cell of the row, from
the Company code cell, from a range, and several other ways. I get
the same error message each time.

Does it matter about my version of Excel? It is 2003, SP3.
Thanks for your help with this.

 

[Jim Cone]

The version of Excel can matter in some cases.
However, your version will run the code without a problem.

Cell A5 is the fifth cell from the top of the worksheet in column A.
Paste your example into that cell and run the code.
Your example is...
"ABC Widgets, 100 Main Street, New York, 005980542;017230022;017231132"

For your info: Cell A5 is referred to as Range("A5") in the code.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"jfrick" <[email protected]>
wrote in message
Thanks for such a quick response.
I have tried the macro, but I am getting an error. The error says,
-----
Run-time error '9':
Subscript out of range
-----
The dialog then gives me the choice of End Debug or Help
When I click Debug, the editor takes me to this line:
vLast = VBA.Split(vArr(UBound(vArr)), ";")

I am not sure I am starting this correctly, since I do not understand
the first line of your post, "This will do one cell (A5)." What am I
doing wrong? Or how should I start this macro? Where should I be on
the worksheet or does it even make a difference?

I have tried starting the macro from the first cell of the row, from
the Company code cell, from a range, and several other ways. I get
the same error message each time.

Does it matter about my version of Excel? It is 2003, SP3.
Thanks for your help with this.

 

[jfrick]

I was able to make the work based on your latest post. Thanks for the
work, and by the way your solution is very interesting.

However, I still have the challenge that I have over 1,000 rows of
data to process, and the data is in both a csv format and in an xls
format. We get the data from a report writer that outputs the data
into a 4 cell report. Or more correctly, the report writer outputs
any report into an xls file with each row made up of 1 cell per field
on the report. (And by the way, I have checked to see if the report
writer can break down the multi-valued company code field for us. It
can not.)

In other words, the original data I need to convert is in an xls with
4 columns of data, the company codes being in the 4th column.

Your solution assumes one line of data that is in one cell. Is there
a way to make the macro work against a 4 cell row of data instead of a
1 cell row?

As an aside, I did convert my 4 column xls into a one column file. I
did this by saving the 4 column xls to a csv, and then bringing it
back in as a fixed length text file. I was able to get all 4 columns
into one column. So if I had to, I could convert the data into a one
column report for processing.

And how would I apply it against 1000 rows of data?

Again thanks for your help.

 

[Jim Cone]

Re: "Is there a way to make the macro work against a 4 cell row
of data instead of a1 cell row?"
It would have to be completely rewritten.

Re: "So if I had to, I could convert the data into a one column report for processing.
And how would I apply it against 1000 rows of data?"
The following code will do that.
1000 rows take about 3 seconds on my machine.
All data imported into Excel should be cleaned before attempting to work with it.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare and match data with "XL Companion")

'--
Sub FromOneToMany_R1()
'Jim Cone - San Francisco - February 2008
Dim rng As Range
Dim rngCell As Range
Dim vArr As Variant
Dim vLast As Variant
Dim strBase As String
Dim lngCount As Long
Dim lngLast As Long
Dim N As Long
Dim R As Long

Application.ScreenUpdating = False
lngCount = Cells(Rows.Count, 1).End(xlUp).Row
'Assumes all data is in column 1 and starts in row 5 - ADJUST!
For R = lngCount To 5 Step -1
Set rngCell = Cells(R, 1)
vArr = VBA.Split(rngCell.Value, ",")
vLast = VBA.Split(vArr(UBound(vArr)), ";")
lngCount = UBound(vLast) + 1
lngLast = InStrRev(rngCell.Value, ",") - 1
strBase = VBA.Left$(rngCell.Value, lngLast) & " "

If lngCount > 1 Then
rngCell.Offset(1, 0).Resize(lngCount - 1, 1).Insert shift:=xlDown
Set rng = rngCell.Resize(lngCount, 1)
For N = lngCount To 1 Step -1
rng(N).Value = strBase & VBA.Trim$(vLast(N - 1))
Next
Else
Set rng = rngCell
End If

If R Mod 2 = 0 Then rng.Interior.ColorIndex = 15
Next
Application.ScreenUpdating = True
Set rng = Nothing
Set rngCell = Nothing
End Sub
'--



"jfrick"
wrote in message
I was able to make the work based on your latest post. Thanks for the
work, and by the way your solution is very interesting.
However, I still have the challenge that I have over 1,000 rows of
data to process, and the data is in both a csv format and in an xls
format. We get the data from a report writer that outputs the data
into a 4 cell report. Or more correctly, the report writer outputs
any report into an xls file with each row made up of 1 cell per field
on the report. (And by the way, I have checked to see if the report
writer can break down the multi-valued company code field for us. It
can not.)

In other words, the original data I need to convert is in an xls with
4 columns of data, the company codes being in the 4th column.

Your solution assumes one line of data that is in one cell. Is there
a way to make the macro work against a 4 cell row of data instead of a
1 cell row?

As an aside, I did convert my 4 column xls into a one column file. I
did this by saving the 4 column xls to a csv, and then bringing it
back in as a fixed length text file. I was able to get all 4 columns
into one column. So if I had to, I could convert the data into a one
column report for processing.
And how would I apply it against 1000 rows of data?
Again thanks for your help.

 

[jfrick]

Thank you very much.

It works very well.

I did have to make one minor change.

It was to the line that reads

strBase = VBA.Left$(rngCell.Value, lngLast) & " "

I changed it by adding a coma inside the quotes.

Now it reads
strBase = VBA.Left$(rngCell.Value, lngLast) & ","

The reason was that the output no longer had a comma between the
company code and the previous field. And I needed this comma to
separate the field values.

Again it works great. Thnaks fo your help on this.