Announcement

Collapse
No announcement yet.

Merge column based on condition

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Merge column based on condition



    If column Stack is more than 1, merge the data on Color, Stack and size on the row and the rows below depending on the value eg. if stack value is 4, the data on Color, Stack and Size column on that row and the next 3 rows will merged together except for Row column.

    Thanks in advance.
    Attached Files

  • #2
    I have read your request several times and looked at your workbook. I don't know what you mean by merge. Please explain clearly what your intentions are with an example. If necessary, show an after "merge" sheet even if you have to mock it up manually.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      The after merge result that I want is like in the photo. Basically, The Combined sheet is the main. Let's say I added another row of data in it,, the layout sheet will auto-populate and merge the columns according to the Stack value,

      Comment


      • #4
        Your explanation is not making any sense to me. Suppose you use your examples and explain step by step 1) what you are entering and where, 2) what you expect to happen (in detail using cell addresses) for each type action. Think that we are standing in a coffee shop waiting to place our order and you explain to me your issue.
        Alan

        Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
        FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

        If someone has helped you, say "thank you" by clicking on the Like Button.

        Comment


        • #5
          In the Combined sheet, supposed I have added a row of data (highlighted in red) and sorted it by Row eg.
          Row Color Stack Size
          1 Black 2 Big
          3 Blue 1 Small
          4 Yellow 2 Big
          8 Green 4 Small
          The Layout sheet will be auto-filled (by using formula) like below:
          Row Color Stack Size
          1 Black 2 Big
          2 #N/A #N/A #N/A
          3 Blue 1 Small
          4 Yellow 2 Big
          5 #N/A #N/A #N/A
          6 #N/A #N/A #N/A
          7 #N/A #N/A #N/A
          8 Green 4 Small
          9 #N/A #N/A #N/A
          10 #N/A #N/A #N/A
          11 #N/A #N/A #N/A
          12 #N/A #N/A #N/A
          Now I wanted it to be merged depending on the Stack value as in the photo.

          I really like to thank you and appreciate your help.
          Attached Files

          Comment


          • #6
            The Combined sheet is where the data is entered eg. the Row no., Color, no. of Stack and Size. The Layout sheet will just get the data from the Combined sheet and display it based on the Stack value ie. the Stack value will determine how many row of columns to merge.

            Let's say that in the Combined sheet, Row 4 has the Stack value 2. That means at the Layout sheet; after getting the data from the Combined sheet; the columns for Color, Stack and Size on Row 4 and 5 will be merged together automatically by running a vba script or macro. I'm not good in vba and macro and I hope someone can assist me on this.


            Comment


            • #7
              I have played with your example for the last hour. I have not been able to make any vba code work as you wish. I will no longer work on this as the solution is not in my wheelhouse. Good luck finding a solution. If you are successful, please post your code so that others may benefit.
              Alan

              Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
              FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

              If someone has helped you, say "thank you" by clicking on the Like Button.

              Comment


              • #8
                try this is you wanted ,work for me
                Code:
                Sub tst()
                 Dim x, i&, ii&, j&, z(), r As Range
                 With Sheets("Combined").[a1].CurrentRegion
                   x = .Value: j = 1
                   ReDim z(1 To Application.Sum(.Columns(1).Offset(1)) + 1, 1 To 4)
                 End With
                    z(1, 1) = "Row": z(1, 2) = "Color": z(1, 3) = "Stack": z(1, 4) = "Size"
                   For i = 2 To UBound(x)
                       If Len(x(i, 1)) Then
                          For ii = 1 To Val(x(i, 1))
                              j = j + 1
                              z(j, 1) = j - 1
                              z(j, 2) = Application.VLookup(j - 1, x, 2, 0)
                              z(j, 3) = Application.VLookup(j - 1, x, 3, 0)
                              z(j, 4) = Application.VLookup(j - 1, x, 4, 0)
                          Next ii
                       End If
                   Next i
                   Application.DisplayAlerts = False
                   With Sheets.Add.[f1]
                      .Resize(j, 4) = z
                     Set r = .CurrentRegion
                      For i = r.Rows.Count To 1 Step -1
                         If IsNumeric(r.Cells(i, 3)) = True Then
                           If r.Cells(i, 3).Value > 1 Then
                             j = r.Cells(i, 3).Value
                             r.Cells(i, 2).Resize(j).Merge: r.Cells(i, 3).Resize(j).Merge: r.Cells(i, 4).Resize(j).Merge
                           End If
                         End If
                      Next i
                      With r
                       .HorizontalAlignment = xlCenter: .VerticalAlignment = xlTop
                       .Borders.Value = 1
                      End With
                 End With
                  Application.DisplayAlerts = True
                 End Sub

                Comment


                • #9


                  Nice. This work quite alright but I want the Row column to be fixed at 12.

                  Comment

                  Working...
                  X