Macro Help: Finding data

[rchio]

Hi

my data set is as follows:

spreadsheet 1 sheet 1 is the target and has the key we are looking for.
spreadsheet 2 sheet 1 & 2 is the source and contains different data but somewhere between the two sheets, it has the key in spreadsheet 1.

I would like to know how to find the key in either sheet and return the value of the field on the right from the source sheet into the target sheet.

Thanks in advance.

Regards
Ronnie

 

[Datsmart]

I assume where you say "spreadsheet 1 sheet1" and "spreadsheet 2 sheet 1 & 2" you really mean "workbook 1 sheet1" and "workbook 2 sheet 1 & 2".

Without knowing what "key" is I can not be very specific.
Here is an earlier thread with code to compare data between workbooks.
http://www.mrexcel.com/forum/showthread.php?t=269482

Maybe it will give you some ideas.

 

[rchio]

Thanks John for your response.

To clarify my predicament.

Workbook 1 has EKKO tab with a list of Purchase Order Headers i.e. PO #
Workbook 2 has EKPO & EKPO2 tab with a list of PO#, Purchase order line item & Company code.

I would like to extract the company code in either of the sheets in workbook 2. Each EKPO & EKPO2 have different sets of data as it could not fit into one sheet. (Yes it is a massive spreadsheet). The data does not exist in both sheets within workbook 2. The data is unique only to one sheet in workbook 2 i.e. either EKPO or EKPO2 has the data.

I would like to add in some smarts in the code that can cater looking for the data in both sheets and returning the company code. Because the workbook contains so much data and has many 50+ ranges to find the value in i.e. two columns per set of data totalling to 2.7 million PO line items it is not a simple vlookup as I would have to do too many if statements to cater for this and I don't think excel can handle 50+ ranges to do vlookup on.

Data View

WorkBook 1 (EKKO sheet)
PO
450000001
450000002
450002501
450003373

Workbook 2 (EKPO sheet)
PO Company Code
450000001 10000
450000002 20000

Workbook 2 (EKPO2 sheet)
PO Company Code
450002501 10000
450003373 20000


Expected result in Workbook 1
PO Company Code
450000001 10000
450000002 20000
450002501 10000
450003373 20000

Somehow I need to tell the code to look for the PO in both EKPO & EKPO2 sheets and return the company code next to it.

Any ideas?

Thanks in advance.

Regards
Ronnie

 

[Datsmart]

This code works on some test workbooks I put together.

Sub CompareSheets()
Dim RngS As Range
Dim RngT As Range
Dim SrngCell As Range
Dim TrngCell As Range
Dim SSheet As String
Dim TSheet As String

SFileP = ActiveWorkbook.Path
SFileN = "WorkBook2.xls"
TFileP = ActiveWorkbook.Path
TFileN = ActiveWorkbook.Name
SSheet = "EKPO"
TSheet = "EKKO"

'Set variables
Set RngS = Workbooks(SFileN).Sheets(SSheet).Range("A2:A" & Workbooks(SFileN).Sheets(SSheet).Range("A65536").End(xlUp).Row)
Set RngT = Workbooks(TFileN).Sheets(TSheet).Range("A2:A" & Workbooks(TFileN).Sheets(TSheet).Range("A65536").End(xlUp).Row)
'Compare data ranges
For Each TrngCell In RngT
    'Workbooks(TFileN).Sheets(TSheet).Cells(i, "A").Value = "No Match"
    For Each SrngCell In RngS
        If SrngCell = TrngCell Then
            TrngCell.Offset(0, 1).Value = SrngCell.Offset(0, 1).Value
        End If
        Next SrngCell
Next TrngCell

'Check other WorkSheet
'Set variables
SSheet2 = "EKPO2"
Set RngS = Workbooks(SFileN).Sheets(SSheet2).Range("A2:A" & Workbooks(SFileN).Sheets(SSheet2).Range("A65536").End(xlUp).Row)
'Compare data ranges
For Each TrngCell In RngT
    For Each SrngCell In RngS
        If SrngCell = TrngCell Then
            TrngCell.Offset(0, 1).Value = SrngCell.Offset(0, 1).Value
        End If
    Next SrngCell
Next TrngCell

'Clear memory of set objects
Set RngS = Nothing
Set RngT = Nothing
End Sub

I created two workbooks, one called WorkBook1, the other called Workbook2.
Each has worksheets named as you listed above.
This assumes you have both workbooks open and Workbook1 is active.