Add multiple values into a single row

I have a very simple excel sheet. It lists people, and the states they have mortgage licenses in.

People listed on the right, States across the Top. (All 50 States are listed across the top like this: AL AZ CA CO CT, etc..)

For each person, a date is put into the column of the state they have (telling me when they received the license)

MY DILEMMA:

All I want to do is create a formula to list all of the state abbreviations (top of columns) at the end of each row LIKE THIS: Joe Smith......... CA, IA, DE, etc.
(its a lot to go through each row and then put the states at the end)

How do I create a formula to add up the "value" in each column aka (the date) and LIST the Abbreviations for each State at the end of the row?

Is it an "IF" or "COUNT"? I have no clue what to do.

Is it possible? Let me know if I am not explaining this well. Thank you!

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

In this instance, I fixed your title to follow the rules regarding titles.
===================================

What you're describing is called String Concatenation, and believe it or not, Microsoft STILL hasn't added a simple String Concatenation formula we can use. So we have to write User Defined Functions (UDFs) of our own or resort to real-time active VBA. Either way, it's a VBA solution.

If you're OK adding VBA to your project, post a copy of your workbook and I'll install one for you.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi CapitalChick,

You would be a lot better off keeping your data like the attached example. Jerry is correct in that a "simple" formula is not available in Excel (yet). A user defined function looks like your best alternative.

THis is the UDF I installed in your workbook:

Please Login or Register  to view this content.

You can see this by pressing Alt-F11 to open the VBEditor. It is installed in the new Module1 I added.

Once the VBEditor is closed, you use this new function in a cell, like so in AP2, then AP2 is copied downward:

=CONCATIFS($J$1:$AO$1, $J2:$AO2, "<>")

The first parameter is the range of values you want strung together and returned.

The second parameter is the range of cells on this row to test.

The third parameter is the "test" on the second parameter cells. In this test, I'm just testing for any value at all. "<>" simply means to Excel, this cell is not blank.


So, for every cell that is not blank on row 2, the value from row1 is added to the result. If no results, you will get a "none".

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.