Aligning Data Columns to Match

EJY

New Member
Joined
Oct 4, 2003
Messages
5
Is excel the right program to do this? It seems I can always find a way to do anything in excel!

:rolleyes:

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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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! :)
 
Upvote 0
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 ...
RamseyMunicipalVoters1999Only.xls
ABCD
1ID
2a_1
3a_2
4a_3
5a_5
6a_6
7a_8
8a_9
9a_10
10a_11
11a_14
RamseyMunicipalVoters1999 Only


Then the following simulation shows your FileB (MunicipalLabels.xls) ...
RamseyLabels.xls
ABCDEFG
1
2TRUE
3
4
5IDNameIDName
6a_1Ramsey_1a_1Ramsey_1
7a_2Ramsey_2a_2Ramsey_2
8a_3Ramsey_3a_3Ramsey_3
9a_4Ramsey_4a_5Ramsey_5
10a_5Ramsey_5a_6Ramsey_6
11a_6Ramsey_6a_8Ramsey_8
12a_7Ramsey_7a_9Ramsey_9
13a_8Ramsey_8a_10Ramsey_10
14a_9Ramsey_9a_11Ramsey_11
15a_10Ramsey_10a_14Ramsey_14
16a_11Ramsey_11
17a_12Ramsey_12
18a_13Ramsey_13
19a_14Ramsey_14
20
RamseyLabels


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?
 
Upvote 0
Thought: Maybe I can email you the files and you can take a look at them? There is no top secret data...

EJY
 
Upvote 0
Ooh can you send me a mailing list too please :devilish: (just kidding)

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

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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top