How to concatenate multiple columns/rows based on criteria?

kjdowden

New Member
Joined
Dec 13, 2013
Messages
4
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!!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
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.
 
Last edited:

kjdowden

New Member
Joined
Dec 13, 2013
Messages
4
The issue is that the ID# changes; it is not a constant
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
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)
 
Last edited:

CafeJr

New Member
Joined
Dec 13, 2013
Messages
3
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

Well-known Member
Joined
Dec 29, 2006
Messages
620
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

New Member
Joined
Oct 29, 2013
Messages
3
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??
 

Forum statistics

Threads
1,082,334
Messages
5,364,677
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top