VBA compare two columns in separate workbooks and copy row information (named ranges created)

[kwoodz]

Hello,
I'm new to VBA and coding is NOT my primary job function--I'm just trying to make a tedious task far less tedious for everyone involved. I have two workbooks with varied number of rows--one has consistent columns, the other's change both in column order and content. I need to pull the cell content from the varied-WB to the standardized WB based on the only guaranteed given value between the two WBs. The goal is to write a macro that I can use to update the Standard WB if the information is out of date. I can't share the files, so I'll do my best to illustrate the workbooks with easy data:

WB1 (standard, some columns may be filled in with old data or blank, "Type" column is constant)

Type	Family	Class
cat
dog
snake

WB2 (varied, order not constant)

Family	Type	Order	Class	Person
felidae	cat	carnivora	mammalia	x
canidae	dog	carnivora	mammalia	y
Equidae	horse	Perissoda	mammalia	z
Colubridae	snake	Squamata	Reptilia	a

I have created range names using the following code in an effort to simplify the main code (match/copy)

Sub column_names()
'' column_names Macro
'
Dim rng As Range
Dim wb As Workbook, ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
Set rng = ws.Range("data")

Application.DisplayAlerts = False

rng.CreateNames Top:=True

End Sub

I don't know how to proceed further for the actual "heart" of the code, as I can't seem to get an "index/match" to work with the string variables (named ranges), and I can't hard set column locations as they are subject to change.

Any help is appreciated.

 

[alansidman]

Instead of VBA, I employed Power Query. I loaded each table to PQ, joined them with a left outer join to get the following:

Book1
	A	B	C
1	Type	Family	Class
2	cat	felidae	mammalia
3	dog	canidae	mammalia
4	snake	Colubridae	Reptilia
Sheet3

and the Mcode to make this happen is:

let
    Source = Table.NestedJoin(Table1, {"Type"}, Table2, {"Type"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Family", "Class"}, {"Family", "Class"})
in
    #"Expanded Table2"

 

[kwoodz]

Hi Alan,
Thanks for your reply! It makes a lot of sense, though I keep getting an "expression: cyclical reference error." I'm assuming WB1 = Table1, WB2 =Table2?

 

[alansidman]

Change the table names in the Mcode to what your tables are actually called.

 

[kwoodz]

Yes, I had done that. It's probably a user-error on my end

 

[alansidman]

Did you load the tables first into PQ and Load and Close each to a Connection. Then apply the merge. Did you look at the link in my signature to learn about Power Query?

 

[kwoodz]

Hi Alan,

Thanks again for your help. And yes, I did click on the links on your signature and I'm continuing to read on Power Query and trying different approaches to fix these issues myself, as I'm sure this is something I could figure out with practice.

Both tables are named appropriately, both loaded as connection only, then I applied the merge (Table1 first, then Table2, selecting the "type" column and LeftOuter Join). I then go to the Advanced Editor to copy over the code you've provided, and I the following error:
Expression.Error: The field 'Family' already exists in the record.
Details:
Name=Family
Value=

 

[alansidman]

Here is a link to the file I created from your data. You can review exactly how I created the merge and results shown in Post #2 and compare to how you are doing it.

AnimalMerge.xlsx | Powered by Box

app.box.com