Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pull data from one row into two or more rows (Excel 97 SR2)

    Hello. I think my problem can be solved by a macro, but I don't know for certain.

    We receive a monthly sales report from a sales office in China. We then input this data into a master sheet to include it in a worldwide sales view. We need some help getting the info from the sales report China sends us into the WW view.

    The problem is that occasionally, there will be more than one product per row on the China report, and we are trying to get the information from this report into a master database which we then run a pivot table on. Is there any way to run a macro over the data to pull the information out and separate what was once in one row into 2 or more rows?

    Since I know this makes no sense, I've attached an example of what we get and have input what we would like it to look like at the bottom.

    TIA.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pull data from one row into two or more rows (Excel 97 SR2)

    Here's the macro that you need. I've also attached my test workbook. HTH --Sam
    <pre>Option Explicit
    Sub splitRows()
    Dim wsOld As Worksheet, wsNew As Worksheet
    Dim i As Long, j As Long, k As Long
    Set wsOld = ActiveSheet
    Set wsNew = Worksheets.Add
    wsNew.Columns(2).NumberFormat = "d-mmm"
    wsNew.Cells(1) = "Customer"
    wsNew.Cells(2) = "Date"
    wsNew.Cells(3) = "Product"
    wsNew.Cells(4) = "Pounds"
    wsNew.Cells(5) = "Dollars"
    k = 2 ' Output row
    For i = 2 To wsOld.UsedRange.Rows.Count ' for each input row
    For j = 3 To 6 ' for each pound column
    If wsOld.Cells(i, j) <> "" Then
    wsNew.Cells(k, 1) = wsOld.Cells(i, 1)
    wsNew.Cells(k, 2) = wsOld.Cells(i, 2)
    wsNew.Cells(k, 3) = wsOld.Cells(1, j)
    wsNew.Cells(k, 4) = wsOld.Cells(i, j)
    wsNew.Cells(k, 5) = wsOld.Cells(i, j + 4)
    k = k + 1
    End If
    Next j
    Next i
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull data from one row into two or more rows (Excel 97 SR2)

    Sam,

    Thanks! This does exactly what we were looking for.

    Thanks again,

    Brett

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •