Announcement

Collapse
No announcement yet.

Merge column based on condition

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

  • RussSidney
    started a topic Merge column based on condition

    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

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

    Leave a comment:


  • graha_karya
    replied
    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

    Leave a comment:


  • AlanSidman
    replied
    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.

    Leave a comment:


  • RussSidney
    replied
    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.


    Leave a comment:


  • RussSidney
    replied
    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

    Leave a comment:


  • AlanSidman
    replied
    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.

    Leave a comment:


  • RussSidney
    replied
    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,

    Leave a comment:


  • AlanSidman
    replied
    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.

    Leave a comment:

Working...
X