Macro Help: Finding data

rchio

New Member
Joined
Jul 1, 2008
Messages
2
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Jul 1, 2008
Messages
2
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code works on some test workbooks I put together.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,905
Messages
5,598,778
Members
414,259
Latest member
beetle12

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
Top