Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Aligning Data Columns to Match

This is a discussion on Aligning Data Columns to Match within the Excel Questions forums, part of the Question Forums category; Is excel the right program to do this? It seems I can always find a way to do anything in ...

  1. #1
    EJY
    EJY is offline
    New Member
    Join Date
    Oct 2003
    Location
    MN
    Posts
    5

    Default Aligning Data Columns to Match

    Is excel the right program to do this? It seems I can always find a way to do anything in excel!



    I have a list of ID numbers in file A. In file B, I have a mailing list and corresponding ID numbers, some of which match those of file A.

    How do I delete the records in file B which do not match those in file A?

    Thanks!

    EJY in cold Minnesota

  2. #2
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    843

    Default Re: Aligning Data Columns to Match

    Seems easy enough to do using macros, but need some info

    Are the 2 lists in the 1 workbook? Are they on sheet1 and sheet2? What are the sheets called?

    Do you want File B data actually deleted or the results of matching data put an perhaps sheet3?
    George J

  3. #3
    EJY
    EJY is offline
    New Member
    Join Date
    Oct 2003
    Location
    MN
    Posts
    5

    Default Re: Aligning Data Columns to Match

    The two lists are in seperate files, but I can of course copy the list into the file.

    The sheets don't have names.

    Sure the data on one list can be deleted.

    Thanks for your help!

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Aligning Data Columns to Match

    Hi EJY:

    A little clearer explanation will help what you are working with. Please explain ...

    Are the two files A and B that you refered to are these two separate workbooks, or are these two worksheets within a single workbook? What are the names of the workbook(s) / worksheets. I do believe your data is in Excel workbook(s) -- is it not?

    what column is the list of IDs in File A?

    what column are the IDs in File B?

    Once it has been determined which IDs in File B do not match with the IDs in File A, are the corresponding rows in File B to be deleted?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    EJY
    EJY is offline
    New Member
    Join Date
    Oct 2003
    Location
    MN
    Posts
    5

    Default Re: Aligning Data Columns to Match

    Thanks for the interest in helping me!

    My 2 sets of data is currently in an ASCII file and workbook format. (If just imported it into Excel).

    File A is titled "Ramsey Municipal Voters 1999 Only"

    This is the file with just the ID number

    File B is title "Ramsey Labels"

    This is the file that has some of the ID numbers in File A. I want to keep only those ID numbers (and corresponding data on the same row) which are in File A.

    Both files currently have the same column title "ID Number"

    Yes, once it has been determined which IDs in File B do not match with the IDs in File A, the corresponding rows ARE to be DELETED.

    Thanks again!

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Aligning Data Columns to Match

    Hi EJY:

    One of the ways I can do what you are trying to accomplish is by using the Advanced Filter capability of Excel. So let us say your FileA (RamsyMunicipalVoters1999Only.xls) looks like ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - RamseyMunicipalVoters1999Only.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    ID***
    2
    a_1***
    3
    a_2***
    4
    a_3***
    5
    a_5***
    6
    a_6***
    7
    a_8***
    8
    a_9***
    9
    a_10***
    10
    a_11***
    11
    a_14***
    RamseyMunicipalVoters1999 Only*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Then the following simulation shows your FileB (MunicipalLabels.xls) ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - RamseyLabels.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    *******
    2
    ****TRUE**
    3
    *******
    4
    *******
    5
    IDName**IDName*
    6
    a_1Ramsey_1**a_1Ramsey_1*
    7
    a_2Ramsey_2**a_2Ramsey_2*
    8
    a_3Ramsey_3**a_3Ramsey_3*
    9
    a_4Ramsey_4**a_5Ramsey_5*
    10
    a_5Ramsey_5**a_6Ramsey_6*
    11
    a_6Ramsey_6**a_8Ramsey_8*
    12
    a_7Ramsey_7**a_9Ramsey_9*
    13
    a_8Ramsey_8**a_10Ramsey_10*
    14
    a_9Ramsey_9**a_11Ramsey_11*
    15
    a_10Ramsey_10**a_14Ramsey_14*
    16
    a_11Ramsey_11*****
    17
    a_12Ramsey_12*****
    18
    a_13Ramsey_13*****
    19
    a_14Ramsey_14*****
    20
    *******
    RamseyLabels*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    where the aqua colored cells show your original source data, the Yellow colored cells shows the criterion used, and the Brown colored cells show the matching records that have been extracted.

    The formula in cell E2 is ...

    =ISNUMBER(MATCH(A6,'[RamseyMunicipalVoters1999Only.xls]RamseyMunicipalVoters1999 Only'!$A$1:$A$11,0))
    Does it help?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    EJY
    EJY is offline
    New Member
    Join Date
    Oct 2003
    Location
    MN
    Posts
    5

    Default Re: Aligning Data Columns to Match

    Thought: Maybe I can email you the files and you can take a look at them? There is no top secret data...

    EJY

  8. #8
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    843

    Default Re: Aligning Data Columns to Match

    Ooh can you send me a mailing list too please (just kidding)

    This code should do it, got caught up helping someone else and doing some work (imagine)

    I'd suggest having a blank workbook and entering the data into the sheets until you are sure it works.

    Name the first sheet "File A" and the second "File B" as you described in your original post. (And paste the data in)


    NOTE - it has been assumed that on the File B sheet the id numbers are in column A and addresses are in B. if more columns are involved, the line that mentions cells in the code should be ammended (2 is for B)

    press ctrl + F11
    goto insert - module and paste this macro in.

    Sub Results()
    'Dim RowNdx As Long
    Dim ids As Range
    Dim idplus As Range
    Dim Rng As Range

    'RowNdx = 1

    Sheets("File A").Select
    Set ids = Range(Range("a2"), Range("a2").End(xlDown))

    Sheets("File B").Select
    Set idplus = Range(Range("a2"), Range("a2").End(xlDown))

    For Each Rng In idplus
    If Application.WorksheetFunction.CountIf(ids, Rng.Value) = 0 Then
    Sheets("File B").Range(Cells(Rng.Row, 1), Cells(Rng.Row, 2)) = ""
    End If

    Next Rng

    On Error Resume Next 'In case there are no blank rows
    Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0


    End Sub


    close down the vb editor
    goto tools macro macros and try running the macro (if you select options you can put a shortcut to the macro but as you will probably be running this just one time its not really worth it)

    In the file B sheet there should only be the id's and address details of those that are on the File A sheet.
    George J

  9. #9
    EJY
    EJY is offline
    New Member
    Join Date
    Oct 2003
    Location
    MN
    Posts
    5

    Default Re: Aligning Data Columns to Match

    Thanks for your help, but I'm having trouble...let me describe...I'm using Excel 2000 FYI

    At the step where I should press ctrl + F11, I go to Insert, then on my menu (fully enabled) there is no "module" selection in which to paste the macro.

    Am I supposed to just paste the macro in the sheet?

    The seconc part at which I am confused - You said "NOTE - it has been assumed that on the File B sheet the id numbers are in column A and addresses are in B. if more columns are involved, the line that mentions cells in the code should be ammended (2 is for B)"

    I am using 7 additional rows of data in file B. I don't understand which line in the code should be amended.

    Thanks again for your patience and help!

  10. #10
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    843

    Default Re: Aligning Data Columns to Match

    At the step where I should press ctrl + F11, I go to Insert, then on my menu (fully enabled) there is no "module" selection in which to paste the macro.
    Am I supposed to just paste the macro in the sheet?


    I've never used Excel 2000, but if there is a sheet there, paste it in.


    The seconc part at which I am confused - You said "NOTE - it has been assumed that on the File B sheet the id numbers are in column A and addresses are in B. if more columns are involved, the line that mentions cells in the code should be ammended (2 is for B)"

    I am using 7 additional rows of data in file B. I don't understand which line in the code should be amended.


    Sheets("File B").Range(Cells(Rng.Row, 1), Cells(Rng.Row, 2)) = ""
    means the range current row column A to current row column B = "" or simply put blank out the data in columns A and B - if you have more data in other columns, change column B to whatever.

    You don't need to worry about the no. of rows as this is what the macro will amend.
    George J

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com