Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Match data in two columns add rows to line up

This is a discussion on Match data in two columns add rows to line up within the Excel Questions forums, part of the Question Forums category; Happy New Year to all. I have two columns. I need to match the data in the columns and insert ...

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Posts
    166

    Default Match data in two columns add rows to line up

    Happy New Year to all.

    I have two columns. I need to match the data in the columns and insert rows to end up across from each other. I apologize for the format, I can't seem to attach my data correctly. A match function with tell me there is a match between the two columns, but then I need the data to end up in the same row in both columns so I am thinking a macro might be needed.

    Thank you

    Mine Yours
    Cust Nbr Cust Name Amount Cust Nbr Cust Name Amount

    890111 Abc 5 750111 Battle 89
    75000 Ionic 349 890111 Abc 54
    5600011 ionic US 2789 75000 Ionic 762300
    576011 Tesa 9872



    Result


    890111 Abc 5

    750111 Abott 89
    890111 Battle 54
    75000 Ionic 349 7500 Ionic 762300
    5600011 ionic US 2789
    576011 Tesa 9872

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    15,552

    Default Re: Match data in two columns add rows to line up

    Kim B,

    Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

    Here are three possible ways to post small (copyable) screen shots directly in your post:

    Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
    http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

    or
    http://RichardSchollarís beta HTML M...om of his post

    or
    Borders-Copy-Paste


    Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    Board Regular
    Join Date
    Jun 2008
    Posts
    166

    Default Re: Match data in two columns add rows to line up

    Original data

     ABCDEFG
    1 Mine   Yours 
    2Cust NbrCust NameAmount Cust NbrCust NameAmount
    3750111ABC50000 540611AAA3500
    4980511ACON526400 750111ABC354021
    58750111BATTLE4501 980511ACON526400
    6760511CONJO856400 8750111BATTLE65770
    75705111DIEBOLD51236 87065011CONAIR8540
    86570211FORD578500 5705111DIEBOLD56840
    93564011GALAXY853    


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



    And the result should look like this when the formula/macro is applied

    Result

     ABCDEFGH
    1 Mine   Yours  
    2Cust NbrCust NameAmount Cust NbrCust NameAmount 
    3This row was inserted as a result of function   540611AAA3500 
    4750111ABC50000 750111ABC354021 
    5980511ACON526400 980511ACON526400 
    68750111BATTLE4501 8750111BATTLE65770 
    7This row was inserted as a result of function   87065011CONAIR8540 
    8760511CONJO856400 This row was inserted as a result of function   
    95705111DIEBOLD51236 5705111DIEBOLD56840 
    106570211FORD578500     
    113564011GALAXY853     


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    15,552

    Default Re: Match data in two columns add rows to line up

    Kim B,


    Sample raw data in worksheet Sheet1 before the macro:


    Sheet1

     ABCDEFG
    1 Mine   Yours 
    2Cust NbrCust NameAmount Cust NbrCust NameAmount
    3750111ABC50000 540611AAA3500
    4980511ACON526400 750111ABC354021
    58750111BATTLE4501 980511ACON526400
    6760511CONJO856400 8750111BATTLE65770
    75705111DIEBOLD51236 87065011CONAIR8540
    86570211FORD578500 5705111DIEBOLD56840
    93564011GALAXY853    
    10       
    11       
    12       


    Excel tables to the web >> Excel Jeanie HTML 4




    The two groups of data are sorted ascending separately, and then after the macro we have:


    Sheet1

     ABCDEFG
    1 Mine   Yours 
    2Cust NbrCust NameAmount Cust NbrCust NameAmount
    3    540611AAA3500
    4750111ABC50000 750111ABC354021
    5760511CONJO856400    
    6980511ACON526400 980511ACON526400
    73564011GALAXY853    
    85705111DIEBOLD51236 5705111DIEBOLD56840
    96570211FORD578500    
    108750111BATTLE4501 8750111BATTLE65770
    11    87065011CONAIR8540
    12       


    Excel tables to the web >> Excel Jeanie HTML 4




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

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Code:
    Option Explicit
    Sub AlignCustNbr()
    ' hiker95, 01/10/2011
    ' http://www.mrexcel.com/forum/showthread.php?t=520077
    '
    ' The macro was modified from code by:
    ' Krishnakumar, 12/12/2010
    ' http://www.ozgrid.com/forum/showthread.php?t=148881
    '
    Dim ws As Worksheet
    Dim LR As Long, a As Long
    Dim CustNbr As Range
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet1")
    LR = ws.Range("E" & ws.Rows.Count).End(xlUp).Row
    ws.Range("E3:G" & LR).Sort Key1:=ws.Range("E3"), Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    ws.Range("A3:C" & LR).Sort Key1:=ws.Range("A3"), Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Set CustNbr = ws.Range("A2:C" & LR)
    a = 2
    Do While CustNbr.Cells(a, 1) <> ""
      If CustNbr.Cells(a, 1).Offset(, 4) <> "" Then
        If CustNbr.Cells(a, 1) < CustNbr.Cells(a, 1).Offset(, 4) Then
          CustNbr.Cells(a, 1).Offset(, 4).Resize(, 3).Insert -4121
        ElseIf CustNbr.Cells(a, 1) > CustNbr.Cells(a, 1).Offset(, 4) Then
          CustNbr.Cells(a, 1).Resize(, 3).Insert -4121
          LR = LR + 1
          Set CustNbr = ws.Range("A3:C" & LR)
        End If
      End If
      a = a + 1
    Loop
    Application.ScreenUpdating = 1
    End Sub

    Then run the AlignCustNbr macro,
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    New Member
    Join Date
    Jan 2016
    Posts
    6

    Default Re: Match data in two columns add rows to line up

    Hi all,

    newbie to excel and here.
    I have been trying to achieve something very similar to the example above.

    WIN 7
    Excel 2010

    I have 2 files at different revisions. The format is the same. I need to align the information. To do this I have combined the two files into one, side by side. (New version A:T and Previous version V:AP)

    Manual process currently used:

    I added a column (Z) to compare the key data in column D and column Y using exact. When the formula returns "PROB" I manually insert a row/s in either New version or Previous version, depending on which side requires the addition to align the values in D: and Y:
    I then redo the formula until i identify the next misalignment.

    Colour code:

    Yellow - misaligned values requiring row insert in New Version
    Green - misaligned values requiring row insert in Previous Version
    Red - added rows to New Version
    Orange - added rrows to Previous Version

    Two files:
    Align test - base
    Align test - base required (where I have manually inserted rows)

    The example files I have attached are only ~200 rows, the parent data is 10,000 + rows.

    Any help would be greatly appreciated.

    Thanks in advance

    xlmug

  6. #6
    New Member
    Join Date
    Jan 2016
    Posts
    6

    Default Re: Match data in two columns add rows to line up

    Hi again,

    looks like I cannot attach files, so put on Google Drive here

    https://drive.google.com/folderview?...VE&usp=sharing

    Any problems accessing please let me know.

    Thanks

    xlmug

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    15,552

    Default Re: Match data in two columns add rows to line up

    xlmug,

    Welcome to the MrExcel forum.

    1. Are you using a PC or a Mac?

    When I attempted to download/open the first workbook I received the following message:

    Excel found unreadable content in 'Align test-base required.xlsm'. D you want to recover he content of this workbook? If you trust the source of this workbook, click Yes.


    I have had problems in the past when attempting to download a zipped file, or, an Excel file with macros, with the xlsm file extension.

    If you can post your workbook to the following site, then, please rename the workbook using the xlsx file extension.

    You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

    https://dropbox.com
    Last edited by hiker95; Jan 15th, 2016 at 09:18 PM.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  8. #8
    New Member
    Join Date
    Jan 2016
    Posts
    6

    Default Re: Match data in two columns add rows to line up

    Hi Hiker95,

    thanks for quick reply.

    I am using WIN7 and Excel 2010.

    Re did the files as xlsx and dropped them here:

    https://www.dropbox.com/sh/gy4ho03ez...NTUFFbpca?dl=0

    Any issues please advise.

    One other point I wish to make after re reading my original posting, the colours are not required, I only did to try to make the issues easier to understand.

    Thanks
    xlmug

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    15,552

    Default Re: Match data in two columns add rows to line up

    xlmug,

    I received a similar error message on both of your new/latest workbooks.

    Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    New Member
    Join Date
    Jan 2016
    Posts
    6

    Default Re: Match data in two columns add rows to line up

    BUMP

    I have uploaded new xl files located here:

    https://drive.google.com/file/d/0B3K...ew?usp=sharing
    https://drive.google.com/file/d/0B3K...ew?usp=sharing

    I have downloaded and opened to verify file integrity.

    Thanks
    xlmug

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com