Can this be done? (Excel Macro Probably Needed)

[The_Grudge]

I dont' think even the mighty ASAP utilities can help me with this one.

I have a spreadsheet and each line looks something like this....

JOHN SMITH A333 B404 A405 C404
PETER GRIFFIN A202

When infact I need it to look more like this....

JOHN SMITH A33 1
JOHN SMITH B404 2
JOHN SMITH A405 3
JOHN SMITH C404 4
PETER GRIFFIN A202

Easy to do by hand when there are 20 records, but I have thousands.

How hard would this be?

[Comintern]

Wouldn't be hard at all. It's hard to tell from your post, but are the A### entries in cells across the row, or is the whole line in one cell?

[The_Grudge]

Sorry, yes...each entry has it's own column and is in it's own cell.

So column A would be "name" and then B,C,D,E etc may have codes, up to a maximum of probably 10.

[Comintern]

Try this:

Private Sub SplitRows()
 
    Dim oRange As Range, lRow As Long, lCol As Long, oOut As Worksheet, lOut As Long
    Dim sValue As String, sName As String
    
    Set oRange = Application.ActiveSheet.UsedRange          'Get the range of used cells.
    Set oOut = Application.ActiveWorkbook.Worksheets.Add    'Create an output worksheet.
 
    For lRow = 1 To oRange.Rows.Count                       'Loop through all rows.
        sName = oRange.Cells(lRow, 1).Value                 'Get the name from column A.
        sName = Trim$(sName)                                'Trim it.
        If Len(sName) <> 0 Then                             'Check to see if there is an entry.
            For lCol = 2 To oRange.Columns.Count            'Loop through the remaining columns.
                sValue = oRange.Cells(lRow, lCol).Value     'Get the value from that cell.
                sValue = Trim$(sValue)                      'Trim it.
                If Len(sValue) <> 0 Then                    'See if there is a value there.
                    lOut = lOut + 1                         'Increment the output row counter.
                    oOut.Cells(lOut, 1).Value = sName       'Write the name cell.
                    oOut.Cells(lOut, 2).Value = sValue      'Write the value cell.
                End If
            Next lCol
        End If
    Next lRow
    
    Set oRange = Nothing                                    'Release objects.
    Set oOut = Nothing
 
End Sub

[The_Grudge]

That works really well with a little tweak here and there (cell range) so thanks!

I just have one more question. If I have more columns across the top how do I copy them over as well.

So (commas indicate columns)

JOE SMITH, A303, A505, N858, 788 Beverly Hills Ave, Los Angeles

Should Read....

JOE SMITH, A303, 788 Beverly Hills Ave, Los Angeles
JOE SMITH, A505, 788 Beverly Hills Ave, Los Angeles
JOE SMITH, N858, 788 Beverly Hills Ave, Los Angeles

You follow?

Oh, the counter doesn't seem to print on the new page either...but I think I can tweak it.

[The_Grudge]

Nevermind, I got it.

Thanks man, you saved my mouse hand A LOT of dickin' around LOL

[Comintern]

Just copy them out at the same time the name is copied. It looks like they will be in a fixed column or always in the last column. If it's fixed, it's really easy, just always write that cell the same place in the code that the name writes. If it's always in the last cell (but not always in the same column), it's a little trickier. You might scan all of the cell values into an array, and then use first element / last element in each row, and then loop through the middle of the array.

[egghi]

Hi,

I am trying to adopt the macro you created back in March 2006, and was having some hard time revise it to meet my needs (help!!!) My problem is that I want to need the first 4 columns in every row = sName instead of just column A.

The second problem is that serial numbers in column A starts with zeros, and when they are copied to the oOut spreadsheet, the zeros are all gone due to the cell format. Please advise how I can define the cell format (i.e. selection.numberformat = "@") in the new spreadsheet!

My spreadsheet currently looks like this:

Procedure Description Category Price Modifier1 Modifier2
001234 Plastic bag V $12 TC890 TC990
001235 Paper box V $15 TB003
001236 Tin can J $20 TC999 13556

I would like to use the splitrow() and convert the spreadsheet into:

Procedure Modifiers Description Category Price
001234 TC890 Plastic bag V $12
001234 TC990 Plastic bag V $12
001235 TB003 Paper box V $15
001236 TC999 Tin can J $20
001236 13556 Tin can J $20

Thank you very much in advance,
egghi