Comparing Columns In 2 different Excel Spreadsheets 2

[StayAtHomeMom]

Hello,

I don't work much in Excel. I program in Access most of the time, but this task requires me to stay in Excel. I have 2 files. I must compare the cell column of one spreadsheet(it's a PartNumber) to the cell column of a Master List of Part Numbers. If it's not found in the Master List, I need to delete the whole row from the first spreadsheet.

Do I do this in a Macro? How do I refer to an entirely different Excel file/worksheet/cell?

Any hints or possible FAQs would be most appreciated!

Thanks,
Donna

 

[Lightning]

StayAtHomeMom
This should get you started on the right track.
Copy, paste and modify this code and run it from the file that needs to be edited.

Private Sub CompareTwoFiles()
[COLOR=green]Open the second file from the first file[/color]
	Application.ScreenUpdating = False
	Sheets("Sheet 1").Select
	Application.StatusBar = "Now checking Part Numbers.  Please wait..."
SelectFile:
'  Select the required Master List file
	strFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select the required Recommendations By ESA file...")
	If strFile = "" Then
		varReply = MsgBox("Please select the valid file, or click CANCEL to stop processing.", _
				vbOKCancel + vbExclamation, "No Data File selected")
		If varReply = vbOK Then
			GoTo SelectFile
		Else
			Exit Sub
		End If
	End If
	Workbooks.Open strFile
	Workbooks(2).Activate
	strRec = Workbooks(2).Name
	ThisWorkbook.Activate
	Sheets("Sheet 1").Select
	Range("B5").Select
[COLOR=green]'Loop through each value and obtain the matching value from the master file[/color]
	Do Until ActiveCell.Value = ""
		strMyValue = ActiveCell.Value
[COLOR=green]	'  Activate the Master file[/color]
		Workbooks(strRec).Activate
		Range("B3").Select
[COLOR=green]	'  Match the Value [/color]
		Do Until ActiveCell.Value = ""
			If ActiveCell.Value = strMyValue Then
				Do stuff here
			Else
				ActiveCell.Offset(1, 0).Select
			End If
		Loop
	Loop
[COLOR=green]'2) Return to the editing worksheet and then close the Master file[/color]
	ThisWorkbook.Activate
	Sheets("Sheet 1").Select
	Application.DisplayAlerts = False
	Workbooks(strRec).Close savechanges:=False
	Application.DisplayAlerts = True
	Application.ScreenUpdating = True
	Application.StatusBar = False
End Sub

Note that this assumes there are no blank rows in the columns being compared.

HTH
Lightning

 

[xlbo]

To refer to another workbook:

dim theWB as workbook
set theWB = workbooks("WorkbookName")
'alternatively
'set theWB = workbooks.open("Full\Path\And\Name.xls")

to refer to another worksheet

dim theWS as worksheet
set theWS = theWB.worksheets("worksheetname")

You may then refer to a cell as:

theWB.theWS.Range("A1").value

I would suggest looping through your list of parts and picking up the value of each partnumber

dim PartNumber
For each c in range("YourRangeOfData")
PartNumber = c.value
Next c

You may then use the FIND method to search for the PartNumber in the MasterList (see help for FIND syntax)

If FIND returns a match then you can delete the row

An alternative would be to use a VLOOKUP formula against your partnumber list, looking up into the master list - non matches will return #N/A which you can then use the Autofilter to select and delete. This would not really require code and should only take 5 mins (depending on size of data set)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question