Hi all,
I'm trying to set up a Match+Index or Vlookup loop with VBA to return Countries for First and Last names, if the Country cell is empty.
This question is posted on ExcelForum as well link: https://www.excelforum.com/excel-pr...value-vba-loops-with-example.html#post5070771
I have posted an example workbook there as well.
So,
There is Sheet1 and Sheet2.
On Sheet1 there is a table called DataTable, with First Names, Last Names and Countries, but for some names, the country cell is empty.
On Sheet2 there is another table called AidTable, and it also has First Names, Last Names and Countries and in that one, all the countries are there for all the names, but not all the names are there.
So I think I would need a loop, to go through the DataTable, and if the Country cell is not empty, then next loop, if the country cell is empty, then enter another loop, that loops through the Aid table and finds the same First Name - Last Name combination, and returns the country from the Aid table to the Data table.
The twist is that the First Names and Last Names are written correctly in the DataTable, but in the AidTable they are separated wrong.
(eg: First Name in DataTable: Adam Piotr Last Name in DataTable: Wrobel
First Name in AidTable: Adam Last Name in AidTable: Piotr Wrobel)
Sheet1 DataTable Names and countries are also surrounded by other data, so I can't just copy over the AidTable and remove duplicates.
Practically I have a lookup table for the Countries its just that the lookup value is in 2 columns, and they should be joined in a way that excel recognizes that they are the same thing.
If possible to solve without helper columns that would be the best!
Could someone help please?
I'm trying to set up a Match+Index or Vlookup loop with VBA to return Countries for First and Last names, if the Country cell is empty.
This question is posted on ExcelForum as well link: https://www.excelforum.com/excel-pr...value-vba-loops-with-example.html#post5070771
I have posted an example workbook there as well.
So,
There is Sheet1 and Sheet2.
On Sheet1 there is a table called DataTable, with First Names, Last Names and Countries, but for some names, the country cell is empty.
On Sheet2 there is another table called AidTable, and it also has First Names, Last Names and Countries and in that one, all the countries are there for all the names, but not all the names are there.
So I think I would need a loop, to go through the DataTable, and if the Country cell is not empty, then next loop, if the country cell is empty, then enter another loop, that loops through the Aid table and finds the same First Name - Last Name combination, and returns the country from the Aid table to the Data table.
The twist is that the First Names and Last Names are written correctly in the DataTable, but in the AidTable they are separated wrong.
(eg: First Name in DataTable: Adam Piotr Last Name in DataTable: Wrobel
First Name in AidTable: Adam Last Name in AidTable: Piotr Wrobel)
Sheet1 DataTable Names and countries are also surrounded by other data, so I can't just copy over the AidTable and remove duplicates.
Practically I have a lookup table for the Countries its just that the lookup value is in 2 columns, and they should be joined in a way that excel recognizes that they are the same thing.
If possible to solve without helper columns that would be the best!
Could someone help please?
