I can see you did a full outer join. This might not help because the Host names are slightly different in each table. Have you considered doing a union instead?
For union to work, try having similar number of columns, with same column names and data types. If there is no data for a particular column/field in a data source then leave those rows blank. I don't really understand your data structure and what you are trying to achieve but I believe union might be a good solution for you.
However, if you want to keep your current data join and create a single host field, then you can use the formula below;
Create calculated field [Host]
IF ISNULL([Host Cheetah]) AND NOT ISNULL([Host Aardvark]) THEN [Host Aardvark]
ELSEIF ISNULL([Host Cheetah]) AND NOT ISNULL([Host Bengal]) THEN [Host Bengal]
ELSE [Host Cheetah]
Hope this helps. See attached workbook
Basic Host Test_OO.twbx 44.5 KB
Thanks for the quick response. That may work! Though if I have 8-10 spreadsheets instead of 3, it could get a little lengthy, but still work.
You may have some more ideas based upon what I'm trying to do:
Each spreadsheet (approx 8 total), for the most part, contains unique data. Each sheet will have different columns of data, however, they all have in common the "Host". The goal is to be able to have one column of "Host" and add/sort on various dimensions to see which "Hosts" those dimensions apply to.
1. Based upon this, do you think a union would work better in this case if the columns are different for each of the 8 spreadsheets?
2. If I want to display only certain hosts that have values in a certain column (e.g. "Version"), would union be the best or keep as you've shown?
End Result Sample (for thousands of lines/30+ different columns):
Basic Host Test_OO (1).twbx 37.8 KB
From what you described, Union will help you avoid writing long calculations. However, adding 8-10 column names to the calculated field may not be too much of a hassle either. So it depends on you, both approaches will give you the correct result.
Could you please mark this as the correct answer if it resolved your question?
Hope this helps.
It was easiest to make the calculation for the sheets. So far everything is going well. I think your recommendation solved the issue. Thank you!
You're welcome. Glad it helped.