Append data from one sheet to another and match up

[asramsey]

The file that I need help with appending can be downloaded here: www.susanramsey.net/APPEND-MAIN FILE.xls

The phone numbers in the APPEND sheet are also found in the MAIN sheet. I need to pull the data in the MAIN sheet over to the APPEND sheet...and have it match up by matching the phone numbers.

I sure could use some specific instructions on how in the world to do this

Thanks for any help!

 

[asramsey]

Here is the correct link for the sample file: http://www.susanramsey.net/APPEND-MAIN FILE.xls

 

[Lord Bowler]

I'm doing something similar, sort of the result of Vlookup via Macro because my data sheets can become very large.

I was able to pull the data similar to the INTERSECT function (which I could not get to work) by using the MATCH command.

In mine, I'm matching a column based on a Reference Number and a row based on a District Code and returning to my sheet the data.

    ' Dim variables - local
    Dim rows As Variant
    Dim cols As Variant
    Dim index As Integer
    index = 0 ' Initialize

    ' Declare to hold the Refkey & District Code
    Dim Refkey As String
    Dim DistrictCode As String

    With Worksheets(Year)
        ' Set Refkey value without the # symbol
        Refkey = Mid(startCell.Offset(-3, YearCol).Text, 2)
        DistrictCode = startCell.Offset(0, -2).Text 'Initial Value

        ' Get Column Location
        cols = Application.Match(Refkey, .Range("A4:IV4"), 0)
        
        ' If found value is null, then set value as #N/A
        If IsError(cols) Then
            startCell.Offset(YearCol, 0).Value = "#N/A"
        Else
            Do ' Loop for all Districts, with each Refkey
            ' Get Row Location
            rows = Application.Match(DistrictCode, .Range("A1:A800"), 0)
            
            startCell.Offset(index, YearCol).Value = .Cells(rows, cols).Value
            If startCell.Offset(index, YearCol).Value = Empty Then
                startCell.Offset(index, YearCol).Value = 0
            Else
                If startCell.Offset(index, YearCol).Value = Null Then
                    startCell.Offset(index, YearCol).Value = 0
                End If
            End If
            ' startCell.Offset(index, YearCol).Select
            index = index + 1 ' Increment here
            DistrictCode = startCell.Offset(index, -2).Text 'Next District
            Loop Until DistrictCode = ""
        End If
    End With

From what it sounds like, all you need to do is use the MATCH code to match the phone number in order to get the ROW that you need. Once you have that you can move any and all data from that row.

 

[asramsey]

Thank you Lord Bowler for your reply...and time. Unfortunately for me...I have no idea how to use the macro that you sent me. It has been so long since I worked with those...and I have never used one in excel. I was hoping to have just a simple function that might could work for me.

I sure could use some specific instructions on how in the world to do this. I have watched the video at http://www.youtube.com/user/ExcelIsFun#p/search/28/AeYOODc-Ejs several times now and used the =VLOOKUP(A4,L$4:M$8,J4:M4,0 however keep getting a #REF error. It looks like from this...that having just a simple function might work?

Can you tell me how to use the macro that you sent me...and is that macro specifically for the file exammple that I shared? Thank you again so much!

 

[Lord Bowler]

You can use a VLOOKUP to return a value, but cannot return a range of data.

You can use:
=VLOOKUP($A2,MAIN!$A$2:$J$10,COLUMN()-1,FALSE)
$A2 is your phone column with A being static, $A$2:$J:$10 is your static range, Column() will use the column number on your code sheet [ie: A=1, B=2, C=3] -1 so you can backup one column in your data sheet, FALSE (finds exact match only). Copy this formula to each cell to the right matching the number of columns that you want to pull from MAIN.

I'd recommend using a named range, Insert Menu>Name>Define, or selecting the Range and then typing a name in the box above Cell A1. You can then modify these via the Insert Menu>Name>Define:
=VLOOKUP($A2,Sheetrange,COLUMN()-1,FALSE) with your named range.

Vlookup will not work for multiple matches, only returns the first match.

The benefit of using a macro is that you can use multiple formulas, change data types, format, etc... For simple stuff a series of Vlookups will suffice to pull over each item from you MAIN sheet, one vlookup per item

 

[asramsey]

Thank you again for your help. I have been looking over this...and I am still working on understanding what all you are saying below. My skills in formulas/programming are very very rusty. I am having to brush up...and read.

I will reply back when I reply with an understanding