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

kwoodz

New Member
Joined
May 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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)
TypeFamilyClass
cat
dog
snake

WB2 (varied, order not constant)
FamilyTypeOrderClassPerson
felidaecatcarnivoramammaliax
canidaedogcarnivoramammaliay
EquidaehorsePerissodamammaliaz
ColubridaesnakeSquamataReptiliaa

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

VBA Code:
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.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,938
Office Version
  1. 2019
Platform
  1. Windows
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
ABC
1TypeFamilyClass
2catfelidaemammalia
3dogcanidaemammalia
4snakeColubridaeReptilia
Sheet3
and the Mcode to make this happen is:

VBA Code:
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

New Member
Joined
May 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,938
Office Version
  1. 2019
Platform
  1. Windows
Change the table names in the Mcode to what your tables are actually called.
 

kwoodz

New Member
Joined
May 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,938
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
May 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,938
Office Version
  1. 2019
Platform
  1. Windows
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,114,478
Messages
5,548,268
Members
410,825
Latest member
Dave12
Top