Match data in two columns add rows to line up

[Kim B]

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

 

[hiker95]

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
RichardSchollar’s beta HTML Maker -...his signature block at the bottom 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.

 

[Kim B]

Excel Workbook
	A	B	C	D	E	F	G
1		Mine				Yours
2	Cust Nbr	Cust Name	Amount		Cust Nbr	Cust Name	Amount
3	750111	ABC	50000		540611	AAA	3500
4	980511	ACON	526400		750111	ABC	354021
5	8750111	BATTLE	4501		980511	ACON	526400
6	760511	CONJO	856400		8750111	BATTLE	65770
7	5705111	DIEBOLD	51236		87065011	CONAIR	8540
8	6570211	FORD	578500		5705111	DIEBOLD	56840
9	3564011	GALAXY	853
Original data

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

Excel Workbook
	A	B	C	D	E	F	G	H
1		Mine				Yours
2	Cust Nbr	Cust Name	Amount		Cust Nbr	Cust Name	Amount
3	This row was inserted as a result of function				540611	AAA	3500
4	750111	ABC	50000		750111	ABC	354021
5	980511	ACON	526400		980511	ACON	526400
6	8750111	BATTLE	4501		8750111	BATTLE	65770
7	This row was inserted as a result of function				87065011	CONAIR	8540
8	760511	CONJO	856400		This row was inserted as a result of function
9	5705111	DIEBOLD	51236		5705111	DIEBOLD	56840
10	6570211	FORD	578500
11	3564011	GALAXY	853
Result

 

[hiker95]

Kim B,


Sample raw data in worksheet Sheet1 before the macro:

Excel Workbook
	A	B	C	D	E	F	G
1		Mine				Yours
2	Cust Nbr	Cust Name	Amount		Cust Nbr	Cust Name	Amount
3	750111	ABC	50000		540611	AAA	3500
4	980511	ACON	526400		750111	ABC	354021
5	8750111	BATTLE	4501		980511	ACON	526400
6	760511	CONJO	856400		8750111	BATTLE	65770
7	5705111	DIEBOLD	51236		87065011	CONAIR	8540
8	6570211	FORD	578500		5705111	DIEBOLD	56840
9	3564011	GALAXY	853
10
11
12
Sheet1

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

Excel Workbook
	A	B	C	D	E	F	G
1		Mine				Yours
2	Cust Nbr	Cust Name	Amount		Cust Nbr	Cust Name	Amount
3					540611	AAA	3500
4	750111	ABC	50000		750111	ABC	354021
5	760511	CONJO	856400
6	980511	ACON	526400		980511	ACON	526400
7	3564011	GALAXY	853
8	5705111	DIEBOLD	51236		5705111	DIEBOLD	56840
9	6570211	FORD	578500
10	8750111	BATTLE	4501		8750111	BATTLE	65770
11					87065011	CONAIR	8540
12
Sheet1

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.

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,

 

[xlmug]

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

 

[xlmug]

Hi again,

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

https://drive.google.com/folderview?id=0B3KbJMccHm-jb1VwQ2JsMlROSVE&usp=sharing

Any problems accessing please let me know.

Thanks

xlmug

 

[hiker95]

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

 

[xlmug]

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/gy4ho03eznzad5i/AAArWUzieSRZERwuNTUFFbpca?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

 

[hiker95]

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.

 

[xlmug]

BUMP

I have uploaded new xl files located here:

https://drive.google.com/file/d/0B3KbJMccHm-jUXFkUHpHeGhldEU/view?usp=sharing
https://drive.google.com/file/d/0B3KbJMccHm-jdlBjb2FMbGUxbE0/view?usp=sharing

I have downloaded and opened to verify file integrity.

Thanks
xlmug