Announcement

Collapse
No announcement yet.

Align data in two different columns to matching rows

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

  • Align data in two different columns to matching rows



    Hi, I have to sets of data.
    Data A has all of Data B, but Data B only has some items of Data A. I would like to somehow automatically align the data from these two columns so that blue will be in the same row as blue, green will be in the same row as green, and so on. I'd also like the numbers they are in the same row as to move with them.

    For example, if I start out with this:
    Data A Qty Data B Qty
    red 5 blue 15
    blue 6
    orange 9 green 19
    green 10 yellow 30
    yellow 3
    I would like to end up with this:
    Data A Qty Data B Qty
    red 5
    blue 6 blue 15
    orange 9
    green 10 green 19
    yellow 3 yellow 30
    As you can see, the rows are aligned with their matching data points and quantities.

  • #2
    Re: Align data in two different columns to matching rows

    Originally posted by carminav25 View Post
    Hi, I have to sets of data.
    Data A has all of Data B, but Data B only has some items of Data A. I would like to somehow automatically align the data from these two columns so that blue will be in the same row as blue, green will be in the same row as green, and so on. I'd also like the numbers they are in the same row as to move with them.
    ...
    ...
    here's a macro you can try.

    Data at start assumed to be cols A and B, and cols D and E.

    The macro only lines up values, but leaves original cell formats where they are, i.e. doesn't line those up too.

    This is easily changed if you like. You weren't explicit about what you wanted done with formats.
    Code:
    Sub lineup()
    Dim lra As Long, x, c(), i As Long
    lra = Cells(Rows.Count, "a").End(3).Row
    ReDim c(2 To lra, 1 To 2)
    For i = 2 To Cells(Rows.Count, "d").End(3).Row
        x = Application.Match(Cells(i, "d"), Cells(1, "a").Resize(lra), 0)
        If Not IsError(x) Then
            c(x, 1) = Cells(i, "d")
            c(x, 2) = Cells(i, "e")
        End If
    Next i
    Range("D2:E" & lra) = c
    End Sub

    Comment


    • #3
      Hi All
      My first post on this forum, so please let me know if I transgress in any way.
      This forum is commendable for the excellent help and advice it dispenses.

      This thread is really old but there's no fault in trying to get a response.

      The above solution from rugila is the perfect solution to my situation. I have noted his comment on formats not being moved and the option to enable moving formats.
      Unfortunately when contacting him I was informed that this was long ago and that I should post my request on the forum.
      So here goes my request:

      Sub AutoCat()
      Dim lra As Long, x, c(), i As Long
      lra = Cells(Rows.Count, "a").End(3).Row
      ReDim c(2 To lra, 1 To 5)

      For i = 2 To Cells(Rows.Count, "b").End(3).Row
      x = Application.Match(Cells(i, "b"), Cells(1, "a").Resize(lra), 0)
      If Not IsError(x) Then
      c(x, 1) = Cells(i, "b")
      c(x, 2) = Cells(i, "c")
      c(x, 3) = Cells(i, "d")
      c(x, 4) = Cells(i, "e")
      c(x, 5) = Cells(i, "f")
      End If

      Next i
      Range("B2:F" & lra) = c
      End Sub

      I need the formulas/functions to be moved with the cells, and not just the values.
      Please could you help me change the formula to accommodate this.

      Kind Regards

      Comment


      • #4


        If anyone is interested, I solved it by changing this:

        c(x, 1) = Cells(i, "b")
        c(x, 2) = Cells(i, "c")
        c(x, 3) = Cells(i, "d")
        c(x, 4) = Cells(i, "e")
        c(x, 5) = Cells(i, "f")

        to this: (my experimenting)

        c(x, 1) = Cells(i, "b").Formula
        c(x, 2) = Cells(i, "c").Formula
        c(x, 3) = Cells(i, "d").Formula
        c(x, 4) = Cells(i, "e").Formula
        c(x, 5) = Cells(i, "f").Formula

        and then doing this: ( full credit to http://users.wfu.edu/matthews/misc/E...isplayBug.html )
        • Select the cell.
        • Format the cell as "General". (Right-click the cell, select Format Cells, and choose "General.")
        • Delete the "=" at the beginning of your formula, and hit Enter.
        • Insert the "=" back in the formula at the beginning.

        Comment

        Working...
        X