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

 

[xlmug]

i also did the same on Dropbox

https://www.dropbox.com/s/nvnnv0dw7gp92hr/BASE - compare.xlsx?dl=0

thanks

xlmug

 

[hiker95]

xlmug,

Similar message received again with your link in your reply #11.

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

https://www.dropbox.com/sh/gy4ho03eznzad5i/AAArWUzieSRZERwuNTUFFbpca?dl=0

 

[andrewcrc]

Dragging out an old post....

I'm using the following...edited from earlier post.

Sub AlignCustNbr()

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("N" & ws.Rows.Count).End(xlUp).Row
ws.Range("N3:Y" & LR).Sort Key1:=ws.Range("N3"), 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:L" & LR).Sort Key1:=ws.Range("A3"), Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Set CustNbr = ws.Range("A2:L" & LR)
a = 2

Do While CustNbr.Cells(a, 1) <> ""
  If CustNbr.Cells(a, 1).Offset(, 13) <> "" Then
    If CustNbr.Cells(a, 1) < CustNbr.Cells(a, 1).Offset(, 13) Then
      CustNbr.Cells(a, 1).Offset(, 13).Resize(, 12).Insert -4121
    ElseIf CustNbr.Cells(a, 1) > CustNbr.Cells(a, 1).Offset(, 13) Then
      CustNbr.Cells(a, 1).Resize(, 12).Insert -4121
      LR = LR + 1
      Set CustNbr = ws.Range("A3:L" & LR)
    End If
  End If
  a = a + 1
Loop
Application.ScreenUpdating = 1
End Sub

It works awesome and really appreciate the help. I'm curious if this could check a second column after the first for comparison. Right now it's comparing column A to N (employee ID). If this could also could compare column L and Y AFTER checking A and N.
So if A=N and L=Y, then leave it in one line. If A=N but L<>N then move it to the next line. Same with A<>N, move to the next line.

I can attach some example data if that helps. Thank you!!

 

[hiker95]

It works awesome and really appreciate the help. I'm curious if this could check a second column after the first for comparison. Right now it's comparing column A to N (employee ID). If this could also could compare column L and Y AFTER checking A and N.
So if A=N and L=Y, then leave it in one line. If A=N but L<>N then move it to the next line. Same with A<>N, move to the next line.

I can attach some example data if that helps. Thank you!!

andrewcrc,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


Because of the size of your data, screenshots will not be a good idea.

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


3. And, can we have an additional worksheet (manually formatted by you), of what the results should look like?

 

[andrewcrc]

Excel 2010 on a PC. Windows 7.

Here is some test data: https://www.dropbox.com/s/8i8kkgfqx2iahvr/Test_Macro.xlsm?dl=0

 

[hiker95]

andrewcrc,

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.

Please save your workbook as an xlsx file, and, repost on dropbox.

 

[andrewcrc]

Updated! https://www.dropbox.com/s/rp3hl6ry42rj0kk/Test_Macro.xlsx?dl=0

Thanks for your patience!

 

[hiker95]

3. And, can we have an additional worksheet (manually formatted by you), of what the results should look like?

andrewcrc,

Thanks for the new workbook.

Your workbook only contains Sheet1.

Site 1's raw data is in range A3:F19219

Site 2's raw data is in range N6:S18902

And, there are a lot of rows in both ranges that are blank?


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.

 

[andrewcrc]

Coffee before excel...
This one is should be good. Tab1 is the original output I would receive. Tab2 would best the hopeful results. The current Macro that I'm used from earlier in this thread aligns everything based on columns A and N but I'm hoping to go further and get columns L and Y aligned.
Thanks again!
https://www.dropbox.com/s/rp3hl6ry42rj0kk/Test_Macro.xlsx?dl=0