Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Match and align data in Excel columns and rows

  1. #1
    Join Date
    25th February 2013
    Posts
    5

    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;

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    670

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th February 2013
    Posts
    5

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th February 2013
    Posts
    5

    Re: Match and align data in Excel columns and rows

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Match and align data in Excel columns and rows

    This should do it
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th February 2013
    Posts
    5

    Re: Match and align data in Excel columns and rows

    Perfect. Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    Re: Match and align data in Excel columns and rows

    You are welcome.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    670

    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).

    VB:
     
    Option Explicit 
    Sub AlignAB() 
         ' Stanley D. Grom, 02/26/2013
         ' [URL]http://www.ozgrid.com/forum/showthread.php?t=175524[/URL]
        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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    27th February 2013
    Posts
    7

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Align data into rows using Excel VBA
    By misprepaid.asm in forum EXCEL HELP
    Replies: 7
    Last Post: January 10th, 2013, 17:33
  2. Align data in two different columns to matching rows
    By carminav25 in forum EXCEL HELP
    Replies: 1
    Last Post: January 8th, 2013, 05:31
  3. match and align data across rows and down columns
    By aelroeiy in forum EXCEL HELP
    Replies: 2
    Last Post: April 8th, 2011, 18:56
  4. Match, Copy & Align Data By IDs
    By cold2000us in forum EXCEL HELP
    Replies: 2
    Last Post: February 1st, 2010, 12:32
  5. Match And Align Data Based On Unique ID Numbers
    By excelnb in forum EXCEL HELP
    Replies: 6
    Last Post: November 21st, 2008, 18:38

Bookmarks

Posting Permissions

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