How to concatenate multiple columns/rows based on criteria?

[kjdowden]

Hello, I am new to this forum. In the past I have found extremely helpful
information. I have a dilemma with a project that I can currently working on
that I desperately need assistance on.

Issue:
I have data that has been broken up into multiple cells; sometimes into multiple consecutive rows when it was exported. I need to be able to concatenate this broken data back into one cell.
The concatenate formula I am familiar with and use often.
However, with the data mentioned above I have data in another column that is the
data point that I need to utilize to determine which of the “broken up” cells
need to be concatenated together.

Example:
ID# DATE OUTCOME1 OUTCOME2
OUTCOME3 OUTCOME4
1234 12/13/13 TODAY WA S THE DAY IN WHICH T HE WORK
STARTED
1234 12/13/13 AND THE TE AM WAS READY TO G ET THE PROJECT
1234
12/13/13 STARTED.

As you can see from the above there is a unique ID#
that I need to base the logic on to determine with outcomes need to be
concatenated. I need the results to look like:
ID# DATE OUTCOME1
1234
12/13/13 TODAY WAS THE DAY IN WHICH THE WORK STARTED AND THE TEAM WAS READY TO
GET THE PROJECTED STARTED.

Any assistance would be greatly appreciated as
I have some 4000 lines of broken data that I need to splice back together and by
the end of today. HELP!!!

 

[CafeJr]

Could you post a spreadsheet with that example...

 

[Rick Rothstein]

If you are willing to try a UDF (user defined function), then you might want to consider the one I posted in my mini-blog article here...

<!-- title / author block -->LookUp Value and Concatenate All Found Results

Once you have installed the code (as per the instructions in the article), and assuming your data is in Columns A and B, the formula you would use would look something like this...

=LookUpConcat(1234,A1:A100,B1:B100 )

where you would need to change the ranges for Columns A and B to cover your actual data.

 

[kjdowden]

I do not see a way to attach the sample data

 

[kjdowden]

The issue is that the ID# changes; it is not a constant

 

[Rick Rothstein]

The issue is that the ID# changes; it is not a constant

When there are multiple active responders to your question, you should really "Reply With Quote" instead of just using "Reply" (delete non-relevant text from the quoted material to save space though) so that we know who you are replying to. The above which you posted seems to be directed at my response to you in Message #3. If so, then I am guessing you are concerned with my hard-coding the 1234 in my example formula that you would use after installing my LookUpConcat UDF. If so... that was just an example... the first argument can be a hard-coded number or text string OR a cell reference where the cell's value is the number or text you want to search for (that kind of duality is pretty much standard for any function used in Excel). So, if C1 contained 1234, then my example formula would have become this...

=LookUpConcat(C1,A1:A100,B1:B100)

 

[CafeJr]

I do not see a way to attach the sample data

On advanced you can write some thing using a table construction... But no problem, I think that Rick solve your problem!...

 

[bs0d]

When there are multiple active responders to your question, you should really "Reply With Quote" instead of just using "Reply" (delete non-relevant text from the quoted material to save space though) so that we know who you are replying to. The above which you posted seems to be directed at my response to you in Message #3. If so, then I am guessing you are concerned with my hard-coding the 1234 in my example formula that you would use after installing my LookUpConcat UDF. If so... that was just an example... the first argument can be a hard-coded number or text string OR a cell reference where the cell's value is the number or text you want to search for (that kind of duality is pretty much standard for any function used in Excel). So, if C1 contained 1234, then my example formula would have become this...

=LookUpConcat(C1,A1:A100,B1:B100)

Solution works, just seems to take an incredible amount of time to calculate results.

 

[Brooke_Z]

Hi Rick. I've asked this question to you on another forum, but can this formula be modified to exclude blanks, or does that need to be show in the UDF??