Needing a format to merge cells

[leighamarigoni]

Hey guys, 

So Im currently trying to merge to cells that are in different columns. But they aren't keeping the numbers that Im needing them to keep when they merge. Is there a formula that Im completely missing that would by chance help me do this. 

Example: 

Empolyee Number| First Name| Last Name| Host jurisdiction| Annual Income| Social tax| State tax| 

-----------------------------------------------------------------------------------------------

000123271            | John          | Doe          | TX                       | 200,000           | 101.00     | 0            |

000123271            |                   | Doe         | United States      | 0                      | 0             | 0            | 

Both the columns belong to the same person but they have different numbers in the place due to being able to put the certain numbers in either the first column belonging to the person instead of the second column. But Im looking to combine both columns into one colum while keeping the numbers that I have.

When I tried merging before it only keeps the numbers in the top column, and doesn't bring up the number from the bottom column if the top one has a 0 in it.. 

Example 

Annual income | State Tax

-----------------------------

150,000            | 0                                                                             

----------------------------- (these two rows won't combine)                            

0                      | 150.00 

[#2]

@leighamarigoni If it's always a zero in one of the rows for a particular column, why not just sum the two? But, perhaps these numbers are in fact texts and then it doesn't make much sense, without a few extra steps. You haven't really explained how you are combining the rows for the other columns. Perhaps a workbook demonstrating a realistic data set and the formulae that you already have in place to combine the two rows would be helpful.

[#3]

Hi @leighamarigoni 

Even with advanced steps such as power query or power pivot, it is un-mergeable because of the values in the Host Jurisdictions as illustrated in the first example, what expression would one implement on which logic to use to keep which Host Jurisdiction (which I would assume to be multiple) in which case one would have to create a precedence table to support the expression otherwise one would be lost in implementing that expression ( where in the step was I in the decision expression?)