Transfer a list of country names automatically and in alphabetical order into a next workbook

[Corried]

Hello Excel World.

Thank you in advance for your support.

I have an excel file name: "Heatwave.xlsm".

Now on the work sheet name: "Heat Map".

On Column "A". I have a few list of country names in alphabetical order with their data on Column "B, C and so on".

My problem here is this:

On a next workbook. I have a list of countries names on Column "A".

Some countries are listed in "Heatwave.xlsm" workbook and some are not.



My problem here is this: How can I copy and paste data into Heatwave.xlsm automatically and keep alphabetical order at the same time without losing and data in "Heatwave.xlsm" workbook.



Please view examples Below

Thks in advance

Heatwave.xlsx

Transfer Data to Heatwave workbook.xlsx

drive.google.com

 

[mumps]

Please also post a link to the Heatwave workbook. Explain in detail what data you want to copy and where you want to paste the data referring to specific cells, rows, columns, sheets and workbooks using a few examples from your data (de-sensitized if necessary)

 

[Norie]

Can't you simply use VLOOKUP?

For example to pull the data from column B on Heat Map.

=VLOOKUP(A2,'[Heatwave.xlsx]Heat Map'!$A:$F, 2,0)

 

[Corried]

Please also post a link to the Heatwave workbook. Explain in detail what data you want to copy and where you want to paste the data referring to specific cells, rows, columns, sheets and workbooks using a few examples from your data (de-sensitized if necessary)

I would like to copy the data on this file: Transfer Data to Heatwave workbook.xlsx

And paste it into this file: Heatwave.xlsx

There are some countries missing in "Heatwave workbook" that I want to fill in, without losing the current data and the same time paste in alphabetical order .

 

[Corried]

Can't you simply use VLOOKUP?

For example to pull the data from column B on Heat Map.

=VLOOKUP(A2,'[Heatwave.xlsx]Heat Map'!$A:$F, 2,0)

I am sorry. I don't know if I did anything wrong. I have tried your example. But it don't seems to work as I am getting an error message. How do you want me to process.

Thanks in advance

 

[sandy666]

you can try Power Query (works with both open or/and closed files).

change both lists to Excel Tables (Ctrl+T)
on Heatwave.xlsx use New Query - From File - From Workbook then select Transfer Data to Heatwave workbook.xlsx , then choose the table and Promote Header
then
on Heatwave.xlsx select the table and use From Table
in Power Query Editor Append both tables, Remove duplicates, sort ascending and result load to the shee
that's all

 

[Corried]

you can try Power Query (works with both open or/and closed files).

change both lists to Excel Tables (Ctrl+T)
on Heatwave.xlsx use New Query - From File - From Workbook then select Transfer Data to Heatwave workbook.xlsx , then choose the table and Promote Header
then
on Heatwave.xlsx select the table and use From Table
in Power Query Editor Append both tables, Remove duplicates, sort ascending and result load to the shee
that's all

you can try Power Query (works with both open or/and closed files).

change both lists to Excel Tables (Ctrl+T)
on Heatwave.xlsx use New Query - From File - From Workbook then select Transfer Data to Heatwave workbook.xlsx , then choose the table and Promote Header
then
on Heatwave.xlsx select the table and use From Table
in Power Query Editor Append both tables, Remove duplicates, sort ascending and result load to the shee
that's all

Thank you.

 

[sandy666]

You are welcome

 

[mumps]

Make sure that both workbooks are open. Place this macro in a regular module in the Transfer Data workbook and run it from there.

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = Workbooks("Heatwave.xlsx").Sheets("Heat Map")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In WS2.Range("A2", WS2.Range("A" & WS2.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next
    For Each Rng In WS1.Range("A2", WS1.Range("A" & WS1.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            With WS2
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = Rng
            End With
        End If
    Next
    WS2.Cells(1, 1).Sort Key1:=WS2.Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub