Copy, Find in other sheet, Offset, Copy, Paste

[humbleguy01]

Hi,

I've been trying to find out how to do this all night, but the code is still jibberish to me. I need help.

I've got 2 csv files (with thousands of rows, each) with data I need to merge. The 2nd csv just has 2 columns: the record label; and the data value that needs to be copied. The 1st csv has 4 columns, with the first column being the record label. I just want to paste the data value from the 2nd csv to the 5th column of the corresponding record in the 1st csv.

I tried creating a macro where I:
1) copied the record label from the 2nd_CSV
2) made the 2nd_CSV the active one
3) used the Find command, pasted the label into Find Next,
4) moved the active cell over to the 5th column
5) Activated the 2nd_CSV & copied the data value
6) pasted the data value to the 5th column of the 1st_CSV
7) moved the active cell to A1, so the FindNext would work for the next search.



Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+j
'
Dim SearchValue As Variant

Windows("2nd_CSV").Activate
SearchValue = Selection.Copy
Windows("1st_CSV").Activate
Cells.Find(What:="SearchValue", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 4).Range("A1").Select
Windows("2nd_CSV").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("1st_CSV").Activate
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
Windows("2nd_CSV").Activate
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub

 

[SkipVought]

hi,

It would help to post a sample of each table and an example of how they would be joined.

I'd guess that joining with a query would be a simple method, after IMPORTING the 2 .csv's into 2 separate sheets of your workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[humbleguy01]

Thank you for the recommendation on how to make this post easier to understand.

I created a workbook. Sheet 1 is an example of 1st_CSV, Sheet 2 is an example of 2nd_CSV, and Sheet 3 is an example of the finished product.

 

[SkipVought]

Many of us are restricted from downloads by company security, so I cannot access your examples. I did request that you post that data here.

Now use MS Query, via Data > Get External Data > From Other Sources > From Microsoft Query ... Drill down to your workbook, Add the two sheets as tables and join. Unfortunately, not knowing how your data is organized, I cannot comment on the nature of the join, unless you post your examples where they can be viewed by all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Or sort the second sheet and use a lookup. Consider using this approach for maximum efficiency:

Binary Search with missing data: the solution

Fortunately its easy to check for missing data using VLOOKUP or MATCH with sorted data: the trick is to use the Lookup_Value to lookup itself.

IF(VLOOKUP(Lookup_Value, Table_Range,1,TRUE)<>Lookup_Value,”Not Found”, VLOOKUP(Lookup_Value, Table_Range,Col_Index,TRUE))

The first VLOOKUP looks up the Lookup_Value in column 1 and returns the value from column 1.
If this does not equal the Lookup_Value then return “Not Found”, otherwise do another VLOOKUP but this time using the answer Column Index.
Doing 2 Binary Search lookups is faster than doing one Linear Search lookup with anything more than about 20 rows of data.

This is from

http://fastexcel.wordpress.com/2011...ps-part-1-using-excels-functions-efficiently/

Gavin

 

[humbleguy01]

Thanks for the responses! I had some luck using MS Query, and I'll be sure to try the VLOOKUP procedure as well. Excel is a lot more versatile than I thought!

 

[SkipVought]

Excel is an extremely versatile tool. Many things that you think may need VBA, can actually be accomplished on the sheet, sans code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}