Consulting

Results 1 to 9 of 9

Thread: Solved: Help - Macro to convert column values into row values for various branches

  1. #1
    Banned VBAX Regular
    Joined
    Nov 2008
    Posts
    20
    Location

    Solved: Help - Macro to convert column values into row values for various branches

    Hello dear

    I am attaching an excel file which would rather explain what I want to accomplish in Excel. As I am not so good with English, the attachment will explain my requirement in more detail.

    Something about the attachment:

    I am attaching 1 file. The file has 4 sheets. First 3 sheets namely (B1, B2 & B3) is the data what I have. The last worksheet is what I want to accomplish. I could not think over any sort of vba code or something like it to get me started. I am doing it manually right now and spending hours of time to create it like an idiot. Only God knows how many mistakes my boss will find when checks it

    Thank you very much for any help.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Dear Mogambo,

    I thought this was a fine explanation, and really only have but one question. In your example workbook, the source data is consistent layout-wise. That is to say, there are not five products for one of the expense types, while four products for all others.

    Can we depend upon this?

    The reason I am asking is that this would make a substantial difference in the way we look at a code and/or formula response, as we don't need to worry about searching, finding, or otherwise making our best guess at what to "grab up" and change the layout to.

    Hope that made sense,

    Mark

  3. #3
    Banned VBAX Regular
    Joined
    Nov 2008
    Posts
    20
    Location
    First of all, thank you again for your wonderful help, Mark. You are a great help to this forum.

    Okay. Your question is correctly placed. In response to your question, I would provide a very accurate answer. My answer is:

    No, the number of Products will remain at a constant for all the Expense types. In other words, for example, there will not be 12 products for one of the expense types and 15 for all the other expense types.

    HOWEVER - I prefer some solution which can accomodate a change to the number of Products, AND/OR, a change to the number of Expenses but again that will be applicable to an altogether New Report. Needless to say, the branches will differ.

    A little explanation in detail.

    I will have to do this work everytime my Boss or the management wants a new report. So I would like to tell you that the number of Products and Expenses will remain at a constant for a particular report.

    The number of Products AND/OR Expenses can increase or decrease in the future reports but they will again remain at a constant, the minute, I start creating that report.

    I hope you can understand what I explained above and help me with something. Please tell me if you want to ask me something in relation to this topic. I mean, any questions then I will be happy to answer it.

    Thank you, sir.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [vba]

    Public Sub ProcessData()
    Dim i As Long, j As Long
    Dim NextRow As Long
    Dim LastRow As Long
    Dim LastCol As Long
    Dim sh As Worksheet
    Dim target As Worksheet

    Set target = Worksheets.Add
    target.Range("A11").Value = Array("Branch Code", "Expense Code", "Product Names", "Product Figures")
    NextRow = 1

    For Each sh In ActiveWorkbook.Worksheets

    If sh.Name <> target.Name And sh.Name <> "What I Want" Then

    LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
    LastCol = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column

    For j = 4 To LastCol

    For i = 2 To LastRow

    NextRow = NextRow + 1
    sh.Cells(i, "A").Resize(, 2).Copy target.Cells(NextRow, "A")
    target.Cells(NextRow, "C").Value = sh.Cells(1, j).Value
    target.Cells(NextRow, "D").Value = sh.Cells(i, j).Value
    Next i
    NextRow = NextRow + 2
    Next j
    End If
    Next sh

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Banned VBAX Regular
    Joined
    Nov 2008
    Posts
    20
    Location
    Yes, thanks for the code but what will this macro do ? I think you have forgotten to tell this in your reply.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    It will do what you asked it to do, what is the point of me repeating that?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Banned VBAX Regular
    Joined
    Nov 2008
    Posts
    20
    Location
    I am sorry, I have asked the question incorrectly.

    I mean to ask, if your code is flexible enough to work if there is a change in the number of Products or number of Expenses, as I have mentioned up and above in my earlier post ?

    This change in the number of Products & Expenses can occur in the future reports. For example, assume the number of Products were increased by 2 to the total number of Products now being 6.

    So my question is, what part of code will I have to modify in order to incorporate the change in number of Products or Expenses in a report ?

    xld, Thank you for the code and your help. One more thing, GTO (Mark) is a very kind member, always a help. Thanks to him too.

    Please stay with me in order to solve this problem of mine.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Try it and see.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Banned VBAX Regular
    Joined
    Nov 2008
    Posts
    20
    Location
    Thank you for all your help. I think I can play around with the code in order to suiting my requirements.

Posting Permissions

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