Align data from two columns

[lslamp]

I am using this to filter and match extracted mx records related to domains.
I have data in three columns. column1 has static data with a list of domains that I need records for.
Column2 and column3 has data that is extracted from a script digging for domains and mx records.
column2 = domains
column3 = mx records.
looking at the results that I get not all domains have mx records allocated to them. So I would like to paste my extracted data into column2 and column3 and run a vba macro that sorts the data for me automatically.
example below.
column1
domain1
domain2
domain3

my script delivers the data in txt format.
domain1, ab.domain1
domain3, dc.domain2

As you can see I do not have an entry for domain2. This is because domain2 is inactive.

So I would manually populate or have a macro populate column2 and column3 with the relevant data.
column2
domain1
domain3

column3
ab.column1
dc.domain3

A1 and B1 are headers for the columns
After all 3 columns are populated, the macro will
compare A2 to B2, if A2 = B2 do nothing and move the curser to B3,
if A2 != B2, then insert a blank cell into B2, moving all the other cells with data one cell down,
move cursor to B3
compare A3 to B3, , if A3 = B3 do nothing and move the curser to B4 and so on until you reach a cell that has no data to compare, then stop the comparison run.

I have attached two images,
start_comp.png shows what the tables looks like before I start the comparison
result_comp.png shows the results after the script has been run.

I hope this is clear.
Lawrence

 

[Alex Blakenburg]

Welcome to the Forum.
We would normally use an array (even better a dictionary) and do this in memory but see how you go with this, it is closer to how you were thinking of doing it.
It is heavily dependant on both list being in the same order and that there is nothing in Column B that is not in Column A.

Sub InsertMoveDown()

    Dim ws As Worksheet
    Dim rngStatic As Range, cellStatic As Range
    Dim rowLast As Long
    
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    rowLast = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    If rowLast = 1 Then
        MsgBox "No Data in Column A"
        Exit Sub
    End If
    
    Set rngStatic = ws.Range("A2:A" & rowLast)
    
    If rngStatic.Cells(1).Offset(0, 1) = "" Then
        MsgBox "Not expecting no data in " & rngStatic.Cells(1).Offset(0, 1).Address
        Exit Sub
    End If
    
    For Each cellStatic In rngStatic
        If cellStatic <> cellStatic.Offset(0, 1) Then
            cellStatic.Offset(0, 1).Resize(1, 2).Insert Shift:=xlDown
        End If
    Next cellStatic
    
    Application.ScreenUpdating = True
    
End Sub

 

[lslamp]

Welcome to the Forum.
We would normally use an array (even better a dictionary) and do this in memory but see how you go with this, it is closer to how you were thinking of doing it.
It is heavily dependant on both list being in the same order and that there is nothing in Column B that is not in Column A.

Sub InsertMoveDown()

    Dim ws As Worksheet
    Dim rngStatic As Range, cellStatic As Range
    Dim rowLast As Long
   
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    rowLast = ws.Range("A" & Rows.Count).End(xlUp).Row
   
    If rowLast = 1 Then
        MsgBox "No Data in Column A"
        Exit Sub
    End If
   
    Set rngStatic = ws.Range("A2:A" & rowLast)
   
    If rngStatic.Cells(1).Offset(0, 1) = "" Then
        MsgBox "Not expecting no data in " & rngStatic.Cells(1).Offset(0, 1).Address
        Exit Sub
    End If
   
    For Each cellStatic In rngStatic
        If cellStatic <> cellStatic.Offset(0, 1) Then
            cellStatic.Offset(0, 1).Resize(1, 2).Insert Shift:=xlDown
        End If
    Next cellStatic
   
    Application.ScreenUpdating = True
   
End Sub

Incredible. Thanks for clearing this up, but I am new to excel and VBA, may I ask you to clarify your statement.
Just to clarify, my original list (source) is not in alphabetical order, the domain names are very much randomly ordered depending on each customer. The issue I have is I have primary domains that are in alphabetical order, but then there are subdomains that break that order under each primary domain.

If this was the case, could you still use an array? then lastly, you say,

how you were thinking of doing it.

Is there another way to do it, another logic?
Then lastly, where and how can I learn and understand your logic and how it works.

Thanks again for a great solution.
Lawrence

 

[Alex Blakenburg]

Are you able to instal the XL2BB add-in and show us 10 or so line of data ?
If you are doing an exact match of column A to column B then a dictionary is the preferred method since it has a key/index which lets you retrieve it directly.
Although most people don't get into it until they have been coding for a while, I think the code you finish up with is actually easier to understand.

What will work for you will depend on what your data looks like. It sounds like you might have multiple rows in B & C relating to a single row in A ?

We need a sample of data that is sufficient to see if there are patterns in the data that can be relied on.
(People often oversimplify the sample data set so there appear to be patterns where there are none or show a different data type which is also important)


XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[lslamp]

Are you able to instal the XL2BB add-in and show us 10 or so line of data ?
If you are doing an exact match of column A to column B then a dictionary is the preferred method since it has a key/index which lets you retrieve it directly.
Although most people don't get into it until they have been coding for a while, I think the code you finish up with is actually easier to understand.

What will work for you will depend on what your data looks like. It sounds like you might have multiple rows in B & C relating to a single row in A ?

We need a sample of data that is sufficient to see if there are patterns in the data that can be relied on.
(People often oversimplify the sample data set so there appear to be patterns where there are none or show a different data type which is also important)


XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

@Alex Blakenburg
thanks again for your reply. Right now what you showed me works like a charm.

you are correct I have 3 columns in my spreadsheet.
column#1 = Original data (list of domains on our database) Sourced from a text file domains
I run the following oneliner to create a new file called domain_mx_records
while read line; do dig $line mx| grep -A2 "ANSWER SECTION"; echo ""; done < domains | awk '{print $1,$5,$6}' > domain_mx_records
I use sed to manipulate the new file to extract the unwanted data, leaving me with two columns or data. a domainname and the correlating MX record.

domainanme;mail.domainname

The reason I need this is because of all the domains under our care, there are a number tat do not have M records and I would like to fine out which these are without running a dig on each domain line by line.
So the macro that you created now checks the original, and sets the same domainname next to it with the mx record. If the domain does not have an mx record then it inserts a cell. In my case it inserts 2 cells next to each other. So this works perfectly. Only it does screw up if I do not manipulate the text data that I place into the spreadsheet, but that is understandable.

With this solution, I much appreciate your input and no longer need help, but in the event I do I will most definitely install XL2BB.

Thanks
Lawrence

 

[Alex Blakenburg]

Thanks for providing a more complete picture. If there are patterns in what you manipulate manually there may be scope to add additional automation in the future. Your profile doesn't show what version of Excel you are running. If you have access to Power Query and have not looked at it, you might find that it can help with some of the data manipulation.
In the meantime, glad I could help.