I have two Sheets in Excel. For this example, Sheet 1 and Sheet 2.
Sheet 1: 100 records with Name, Location, Gender
Sheet 2: 100 records with Name, Location, Age
The sources of the data are not synchronized, therefore, I have data that exist in Sheet 1 that doesn't exist in Sheet 2, I also have data in Sheet 2 that doesn't exist in Sheet 1 and I have data that exists in both sheets. So, there may be names in Sheet 1 that are not in Sheet 2 and vice versa.
I'm looking for an automated way to consolidate both sheets into a third sheet and populate the Name & Location and where available Age and Gender. I know this would be easy to do in MS Access, but due to my audience, I need to keep this in Excel and I'm a complete noob to VBA :S
What I have done: On sheet 3, I have made formulas to extract the data from Sheet 1 and in Sheet 2 I have added a field ("X"), which the formula will label the field with "1" if the field is not present in Sheet 1 and will label with "0" if the field is present in Sheet 1. In my mind, I'm looking for a way that will state: "If field "X" in Sheet 2 = "1", then extract that data and append it to Sheet 3, but am unsure on how to do that :(