I need to consolidate the data in two worksheets that do not contain numbers, only text.

Basically I have two worksheets, both with the same column headings; client, state of residence, and state filed. I am wanting to combine them both to complete the "like" client names with the remainder of the data for the state of residence, and state filed columns but keep the names that do not have data to combine.

For example:

Example.JPG


I have tried the consolidate function but I get "no data consolidated" every time. I'm not sure what I am doing wrong. Can someone please help? I am selecting the reference ranges of both sheets that are relevant and adding those and I was selecting "Product" as the function since I have nothing to "sum".