I am new to the Query capabilities in excel. I have several tables that I am combining in various pairings and I have one table that can have multiple references to other tables in two columns.
The first column is essentially an index value for that table.
The second and third columns reference index values in other tables.
In the end I want to have segments from the three tables combined into one. If I separate the second and third columns by the ";" delimiter I end up with 16 rows for A_17 in the end (four each for C_003 and C_024, and 2 for each of the S* numbers) instead of 8.
When I merge the columns I end up with the image below - with extra delimiters at the null cells. How can I merge the columns and ignore the null cells / remove the unnecessary delimiters?
A second, minor, item would be to make the column delimiter "; " instead of ";" to make it visually cleaner since when breaking the combined column into rows some of the data will end up with a space in front of it (or if there was an easy way to remove the leading space when splitting the column into rows if the space exists in only some of the data).
Thank you.
The first column is essentially an index value for that table.
The second and third columns reference index values in other tables.
In the end I want to have segments from the three tables combined into one. If I separate the second and third columns by the ";" delimiter I end up with 16 rows for A_17 in the end (four each for C_003 and C_024, and 2 for each of the S* numbers) instead of 8.
When I merge the columns I end up with the image below - with extra delimiters at the null cells. How can I merge the columns and ignore the null cells / remove the unnecessary delimiters?
A second, minor, item would be to make the column delimiter "; " instead of ";" to make it visually cleaner since when breaking the combined column into rows some of the data will end up with a space in front of it (or if there was an easy way to remove the leading space when splitting the column into rows if the space exists in only some of the data).
Thank you.
