Please help - Importing Columns

Excel Excel 2007
April 26, 2010 at 15:14:33
Specs: Windows Vista

I'm working on a project at work and really
need help. We're working on a big spreadsheet
with thousands of columns and rows of data.
When my supervisor imports new data from a
separate worksheet is there a way for the new
data to always seek out and line up with the

For example, if I have a person's last name in
column A. First name in column B. Address in
column C. I receive a separate spreadsheet
from a co-worker who has similar information
only more. With thousands of rows of
information, I can't tell why or where the
differences occur. Instead, I need to be able to
take his column C and import to my worksheet
but keep the data in columns A and B in the
same order. "Brown" in column A should
always line up with "Amy" in column B and
"123 Pinola Road" in column C. Is there an
easy way to do this? A macro?

Thank you in advance for your help.

See More: Please help - Importing Columns

Report •

April 26, 2010 at 16:07:58
I'm not understanding what you are asking. You need to give some more details, perhaps an example of the data from the 2 spreadsheets and how they are laid out.

re: I need to be able to take his column C and import to my worksheet but keep the data in columns A and B in the
same order.

You've lost me here.

re: "Brown" in column A should always line up with "Amy" in column B and "123 Pinola Road" in column C.

What's causing them not to line up? You need to explain this in more detail.

Please follow this posting tip when you post your examples:

Above the comments box there is a little symbol that reads pre

If you click on that symbol it will insert the pre tags in your post. If you paste your data between the pre tags, you can arrange the data in columns and they will stay lined up. If you try to arrange the data by simply inserting spaces between the values - without using the pre tags - the extra spaces will be deleted.

Paste the data in between the pre tags and click the Preview Follow Up button. This will allow you see how the data lines up without actually submitting your post.

If you don't like how the data lines up, you can edit your post below the preview window.

To preview your post again, click in the box next to:

Check To Show Confirmation Page Again

and click either Confirm button. This will allow you keep checking your post until the data is lined up so that we can tell which column contains what.

Once you are satisfied that the data lines up correctly, just click either Confirm button.

Finally, if you include the sheet names and column letters, we might be able to provide a final solution. If not, we will have to assume sheet names and columns and you will have to modify the solution to match your workbook.

It should something like this when you are done:

      A        B        C
1   Brown     Amy    123 Pinola Road
2   Green     Bob    456 Alonip Ave
3   etc.

Report •

April 26, 2010 at 17:42:07
Thanks for the lesson - I'll keep it in mind. Sorry about the
confusion, but I don't think I will be able to make it any clearer. I
cannot post the worksheet -- it's confidential information.

Thanks again,


Report •

April 26, 2010 at 19:18:42
You don't have to post the actual data - change it. Use name1, address1, name2, address2, etc.

All we need is a clearer understanding of what your 2 different worksheets look like as far as column layout and an explanation how what you need it to look like after the "import".

Report •

Related Solutions

April 26, 2010 at 21:16:51

As DerbyDad03 pointed out, you need to provide more information.

From what you have said, it is not possible to provide a workable solution.

However, in general terms there is no reason why a macro could not be created to match data in two workbooks and copy some of the data from one workbook to the other, based on matching data in column C.

The following macro is placed in the recipient file and starts by asking for the name of the import workbook.
(The import file must be open for this example to work).
The macro compares the addresses in column C of both workbooks.
When there is a match in column C, the address, then the Last and First names are copied to the recipient files using columns D, E & F.

This macro is very basic, and it was produced just to show that data can be matched between certain columns of two workbooks and copied to matching rows.

Here is the code:

Sub MatchImports()

Dim strRecipFileName As String
Dim ImportFileName As String
Dim rngImportStart As Range
Dim rngImportEnd As Range
Dim rngRecipStart As Range
Dim rngRecipEnd As Range
Dim rngImpCell As Range
Dim rngRecipCell As Range

'get filenames
strRecipFileName = ActiveWorkbook.Name
strImportFileName = InputBox("Enter import file name (.xls required)", _
                "Import and Match")

'set start of address column in Recipient (this) file
Set rngRecipStart = Workbooks(strRecipFileName).Worksheets("Sheet1").Range("C2")
'find end of address column in Recipient file
Set rngRecipEnd = Workbooks(strRecipFileName).Worksheets("Sheet1") _
                .Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'set start of address column in Import file (name supplied by user)
Set rngImportStart = Workbooks(strImportFileName).Worksheets("Sheet1").Range("C2")
'find end of address column in Import file
Set rngImportEnd = Workbooks(strImportFileName).Worksheets("Sheet1") _
                .Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'get each address from Import file
For Each rngImpCell In Workbooks(strImportFileName).Worksheets("Sheet1") _
                .Range(rngImportStart, rngImportEnd)
    'loop through all Recipient addresses
    For Each rngRecipCell In Workbooks(strRecipFileName).Worksheets("Sheet1") _
                .Range(rngRecipStart, rngRecipEnd)
        'test if addresses are the same
        If rngImpCell.Value = rngRecipCell.Value Then
            'there is a match so copy data from Import file to recipient file
            'copy all three columns - address then last, then first name
            rngRecipCell.Offset(0, 1).Value = rngImpCell.Value
            rngRecipCell.Offset(0, 2).Value = rngImpCell.Offset(0, -2).Value
            rngRecipCell.Offset(0, 3).Value = rngImpCell.Offset(0, -1).Value
            'no need to search any more - so jump out of the loop
            Exit For
        End If
    Next rngRecipCell
Next rngImpCell
End Sub

Note that the data is in a worksheet named "Sheet1" in both recipient and import workbooks.
Data starts on row 2 of both workbooks and the data to be compared is in column C.

As changes made by macros cannot be undone only try this on copies of your workbooks.
Perhaps trying this will help you explain what it is that you want to do.


Report •

Ask Question