Announcement

Collapse
No announcement yet.

Match and align data in Excel columns and rows

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match and align data in Excel columns and rows



    In an Excel sheet; I am trying to match and align data in column B to data in column A, but it is essential that the data contained in the entire row moves when Column B is matched and aligned with column A..... I have the following VBA code [shown below]; when applied, this inserts a new column (B) where this displays the data that matches column A. As a new column is inserted... the orginial data in column B moves to column (C)..... In part this addresses my issue.... although I need all the other row data to move and be aligned with the matched data in the new inserted column B;

    Code:
    Sub Macro1()
        Dim rng1 As Range
        Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
        rng1.Offset(0, 1).Columns.Insert
        With rng1.Offset(0, 1)
            .FormulaR1C1 = _
            "=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
            .Value = .Value
        End With
    End Sub
    Could anyone please assist in modifiying this code. Many thanks.

  • #2
    Re: Match and align data in Excel columns and rows

    PJB,

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

    Have a great day,
    Stan

    Comment


    • #3
      Re: Match and align data in Excel columns and rows

      I have now
      added in an example...

      Raw Data
      Material Material Description Cond. currency Valid From Valid to Material Group Material Type MPG Vendor
      102060780 102060870 PANEL FRONT COMPLETE AUD 25.02.2010 31.12.9999 SA0044 HAWA 41 100345
      102060810 102060880 PUSH-BUTTON PANEL MOD.191/BAR AUD 25.02.2010 31.12.9999 SA0044 HAWA 41 100345
      102060850 102061300 PANEL ASSEMBLY UPPER FRONT K3/B AUD 01.04.2010 31.12.9999 SA0044 HAWA 41 100345
      102060870 102061470 PANEL FRONT COMPLETE AUD 09.12.2011 31.12.9999 SA0044 HAWA 41 100345
      102060880 102061500 PANEL FRONT COMPLETE AUD 23.03.2012 31.12.9999 SA0044 HAWA 41 100345
      102061140 102061660 PANEL-FRONT W/PUSH BUTTON AUD 20.08.2012 31.12.9999 SA0044 HAWA 41 100345
      102061170
      102061180
      102061230
      102061300
      102061310
      102061330
      102061350
      102061360
      102061470
      102061480
      102061490
      102061500
      102061550
      102061580
      102061630
      102061640
      102061650
      102061660
      Desired Result
      Material Material Description Cond. currency Valid From Valid to Material Group Material Type MPG Vendor
      102060780
      102060810
      102060850
      102060870 102060870 PANEL FRONT COMPLETE AUD 25.02.2010 31.12.9999 SA0044 HAWA 41 100345
      102060880 102060880 PUSH-BUTTON PANEL MOD.191/BAR AUD 25.02.2010 31.12.9999 SA0044 HAWA 41 100345
      102061140
      102061170
      102061180
      102061230
      102061300 102061300 PANEL ASSEMBLY UPPER FRONT K3/B AUD 01.04.2010 31.12.9999 SA0044 HAWA 41 100345
      102061310
      102061330
      102061350
      102061360
      102061470 102061470 PANEL FRONT COMPLETE AUD 09.12.2011 31.12.9999 SA0044 HAWA 41 100345
      102061480
      102061490
      102061500 102061500 PANEL FRONT COMPLETE AUD 23.03.2012 31.12.9999 SA0044 HAWA 41 100345
      102061550
      102061580
      102061630
      102061640
      102061650
      102061660 102061660 PANEL-FRONT W/PUSH BUTTON AUD 20.08.2012 31.12.9999 SA0044 HAWA 41 100345
      Thank you.

      Comment


      • #4
        Re: Match and align data in Excel columns and rows

        Data Example.xlsx

        Comment


        • #5
          Re: Match and align data in Excel columns and rows

          This should do it
          Code:
          Sub test()
              Dim a, i As Long, ii As Long, w, x, n As Long
              With Range("a3").CurrentRegion
                  a = .Value
                  .ClearContents
                  With CreateObject("Scripting.Dictionary")
                      For i = 1 To UBound(a, 1)
                          If a(i, 1) <> "" Then
                              If Not .exists(a(i, 1)) Then
                                  ReDim w(1 To UBound(a, 2))
                                  w(1) = a(i, 1): .Item(a(i, 1)) = w
                              End If
                          End If
                      Next
                      For i = 1 To UBound(a, 1)
                          If a(i, 2) <> "" Then
                              If Not .exists(a(i, 2)) Then
                                  ReDim w(1 To UBound(a, 2))
                              Else
                                  w = .Item(a(i, 2))
                              End If
                              For ii = 2 To UBound(a, 2)
                                  w(ii) = a(i, ii)
                              Next
                              .Item(a(i, 2)) = w
                          End If
                      Next
                      x = Application.Transpose(Application.Transpose(.items))
                      n = .Count
                  End With
                  .Resize(n).Value = x
              End With
          End Sub

          Comment


          • #6
            Re: Match and align data in Excel columns and rows

            Perfect. Thank you.

            Comment


            • #7
              Re: Match and align data in Excel columns and rows

              You are welcome.

              Comment


              • #8
                Re: Match and align data in Excel columns and rows

                PJB,

                jindon's code is much faster, but, here is another way to do the same thing.

                Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

                Code:
                Option Explicit
                Sub AlignAB()
                ' Stanley D. Grom, 02/26/2013
                ' http://www.ozgrid.com/forum/showthread.php?t=175524
                Dim r As Long, lr As Long, d As Range
                Application.ScreenUpdating = False
                lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
                Set d = Range("A1:A" & lr)
                r = 4
                Do While d.Cells(r, 1) <> ""
                  If d.Cells(r, 1).Offset(, 1) <> "" Then
                    If d.Cells(r, 1) < d.Cells(r, 1).Offset(, 1) Then
                      d.Cells(r, 1).Offset(, 1).Resize(, 9).Insert -4121
                    ElseIf d.Cells(r, 1) > d.Cells(r, 1).Offset(, 1) Then
                      d.Cells(r, 1).Resize(, 1).Insert -4121
                      lr = lr + 1
                      Set d = Range("A1:A" & lr)
                    End If
                  End If
                  r = r + 1
                Loop
                Application.ScreenUpdating = True
                End Sub
                Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

                Then run the AlignAB macro.

                Comment


                • #9
                  Re: Match and align data in Excel columns and rows

                  Hello! I want to do almost the exact same thing and Jindon's code works perfectly - there's only one thing that I need to be different. Unfortunately I don't know anything about VBA (yet - I do need to use it for work so I'm doing a course soon), so I'm hoping someone here will be patient enough to help me.

                  In my sheet, the column that is used as the matching criterion is column B rather than column A. There is an extra column A that doesn't do anything or affect anything but does need to be there. Can the code be modified to match to column B instead?

                  Incidentally, the reason I can't just remove column A, run the macro, then paste it back in again, is because the macro deletes rows with duplicates in the matching column. I recognise the need for that, but since my column B includes some duplicates, they would be stripped out and then column A and B wouldn't match any more when I went to paste it back in. So column A needs to stay there so it will be deleted with the rest of the row in these instances.

                  Thanks so much!

                  Comment


                  • #10
                    Re: Match and align data in Excel columns and rows

                    Tasso

                    You should open your own thread as per the rule here and suggest to attach the workbook.

                    Comment


                    • #11
                      Re: Match and align data in Excel columns and rows

                      I have similar type of question what should i do now - should i start a new thread or ask someone help me here ?
                      As far as my understanding the above code written by jindon will work for me with little amendment.
                      But i dont know how to do it...
                      Can someone please help me with this.....

                      Comment


                      • #12
                        Re: Match and align data in Excel columns and rows

                        You need to open a new thread for your own question.

                        Comment


                        • #13
                          Re: Match and align data in Excel columns and rows

                          Hi Jindon,

                          How would I modify your code to match Coln A to Coln C instead of Coln B?

                          Thanks for your help

                          Yeshan

                          Comment


                          • #14
                            Re: Match and align data in Excel columns and rows

                            You need to open a new thread for your own and uploading a sample workbook with before/after will be helpful.

                            Comment


                            • #15


                              Re: Match and align data in Excel columns and rows

                              Hi

                              l am trying to do something similar but my first two columns have data then I want to add to more columns for comparison, and then another two columns for comparison, and so on.

                              J

                              Comment

                              Working...
                              X