Concatenate multiple rows based on a condition

S

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
98
I have a spreadsheet that looks like this (and sorry for doing it like this, but MrExcelHTML doesn't work for me any more).

<colgroup span="5" width="128"></colgroup><tbody>
[TD="align: left"] Technology [/TD]
[TD="align: left"] Brief description [/TD]
[TD="align: left"] Media Title [/TD]
[TD="align: left"] Excerpt Copy [/TD]
[TD="align: left"] Year of Publication [/TD]

[TD="align: left"] 3D images in air - Virtual presence in EO / IR [/TD]
[TD="align: left"] True 3D images are created in air using a strongly-focused, high energy IR pulse laser to create plasma dots of superheated air at selected points. [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-16 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] 3D printing weapons and munition [/TD]
[TD="align: left"] Application of 3D printing techniques to: 1) generate shapes of energetic components (warhead, gun propellant charge, in-flight propellant or fuel grain) that cannot be manufactured otherwise and that improves the performance of the energetic component; 2 [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-18 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] ∞ endurance UxS [/TD]
[TD="align: left"] unmanned systems that can function for a prolonged time (infinitely) because they extract energy from their environment (solar cells, electric fields, etc.) [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-19 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Active protection against high power Directed Energy [/TD]
[TD="align: left"] Active measures that counter damaging EM and thermal radiation [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-21 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Active systems for protection of vehicles, ships, containers and fixed infrastructure against fast-moving incoming threats (point defense) [/TD]
[TD="align: left"] Reduction of passive protection by using active protection systems [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-22 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Active systems for protection or tactical units, bases and fixed locations against fast-moving incoming threats (area defense) [/TD]
[TD="align: left"] Protection umbrella for tactical units during movement and basing. Less protected platforms are protected by other platforms with point defence systems [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-23 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Advanced Energetic Materials [/TD]
[TD="align: left"] High performance energetics that deliver the required effect against the lowest weight, under all environmental conditions (p.e. increased Temperature: Temperature independent propellants), but is insensitive against external threats (Insensitive Munition [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-31 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Aero Thermal energy harvesting projectiles [/TD]
[TD="align: left"] Projectiles that harvest electricity when they are fired, from aerodynamic heating, which is used for guidance and control [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-32 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Automatically reprogram redeemable and reconfigurable SIGINT / EPC Sensor Systems. [/TD]
[TD="align: left"] Sensor systems that quickly adapt to new electronic components and functionalities. [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-46 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Battery improvements [/TD]
[TD="align: left"] An alternative to traditional box-shaped batteries: the large variety of battery designs should, and almost certainly will, be reduced so that fewer battery models would be needed to cover all exigencies, the power demand of the electronics will be reduce [/TD]
[TD="align: left"] CTNSP-2014-Policy challenges of accelerating technological change.pdf [/TD]
[TD="align: left"] Areas that are now maturing and are potentially transformational include advanced solar cells, biofuels, exotic batteries, next-generation fuel cells, ultracapacitors, energy harvesting, and smart power grid systems [/TD]
[TD="align: right"] 2014 [/TD]

[TD="align: left"] Battery improvements [/TD]
[TD="align: left"] An alternative to traditional box-shaped batteries: the large variety of battery designs should, and almost certainly will, be reduced so that fewer battery models would be needed to cover all exigencies, the power demand of the electronics will be reduce [/TD]
[TD="align: left"] CTNSP-2007-Further look at technologies for stabilization operations.pdf [/TD]
[TD="align: left"] his will be reduced in several ways: the large variety of battery designs should, and almost certainly will, be reduced so that fewer battery models would be needed to cover all exigencies, the power demand of the electronics will be reduced by more caref [/TD]
[TD="align: right"] 2007 [/TD]

[TD="align: left"] Battery improvements [/TD]
[TD="align: left"] An alternative to traditional box-shaped batteries: the large variety of battery designs should, and almost certainly will, be reduced so that fewer battery models would be needed to cover all exigencies, the power demand of the electronics will be reduce [/TD]
[TD="align: left"] Unravelling the energy tether: new power technologies take aim at the dismounted soldier [/TD]
[TD="align: left"] The US Army's Communications-Electronics Research, Development, and Engineering Center (CERDEC), for example, is pursuing a wearable polymer-based battery that could eventually replace conventional but bulky systems currently carried by soldiers, such as [/TD]
[TD="align: right"] 2016 [/TD]

[TD="align: left"] Battery improvements [/TD]
[TD="align: left"] An alternative to traditional box-shaped batteries: the large variety of battery designs should, and almost certainly will, be reduced so that fewer battery models would be needed to cover all exigencies, the power demand of the electronics will be reduce [/TD]
[TD="align: left"] Technology Trends SurveyFuture Emerging Technology Trends [/TD]
[TD="align: left"] 3.5.6 Battery Technology The use of renewable energies such as solar and wind have a continuing challenge of ensuring that power supplies are maintained when the sources are intermittent e.g. when the wind is not blowing. To some extent this can be mitiga [/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Battery improvements [/TD]
[TD="align: left"] An alternative to traditional box-shaped batteries: the large variety of battery designs should, and almost certainly will, be reduced so that fewer battery models would be needed to cover all exigencies, the power demand of the electronics will be reduce [/TD]
[TD="align: left"] 2014 Preparing for War in the Robotic Age [/TD]
[TD="align: left"] Small, high-density power generation sys- tems. e rise of unmanned and increasingly autonomous systems is leading to platforms and concepts of operation that can take full advantage of the ability to loiter in geographic areas for long periods – sometimes [/TD]
[TD="align: right"] 2014 [/TD]

[TD="align: left"] Blue Brother (tagging civilians) [/TD]
[TD="align: left"] Tagging civilians in conflict area’s using small electronic devices. Only part of the population needs to be tagged. Tags embedded in clothing’s. Self powering tags (movements, sun). Capability of tracking individuals or groups of people. Building social [/TD]
[TD="align: left"] Technologieverkenning Defensie 2014-57 [/TD]
[TD="align: left"]
[/TD]
[TD="align: right"] 2015 [/TD]

[TD="align: left"] Chip-scale atomic clocks [/TD]
[TD="align: left"] Using CSACs, military patrols can travel on foot and still carry backpack-sized jammers that prevent radio-controlled improvised explosive devices from detonating. [/TD]
[TD="align: left"] AF_TechnologyHorizons2010-2030.pdf [/TD]
[TD="align: left"] Chip-scale atomic clocks [/TD]
[TD="align: right"] 2011 [/TD]

[TD="align: left"] Close air platforms [/TD]
[TD="align: left"] Using CSACs, military patrols can travel on foot and still carry backpack-sized jammers that prevent radio-controlled improvised explosive devices from detonating. [/TD]
[TD="align: left"] Heavy metal Trends in armoured fighting vehicle design.PDF [/TD]
[TD="align: left"] The second oft-quoted alternative to heavy armour is rotary-wing and fast air assets, particularly close air support platforms such as the Boeing AH-64 Apache attack helicopter and Fairchild Republic A-10 ground attack aircraft, as well as the wide range [/TD]
[TD="align: right"] 2016 [/TD]

[TD="align: left"] Close air platforms [/TD]
[TD="align: left"] Using CSACs, military patrols can travel on foot and still carry backpack-sized jammers that prevent radio-controlled improvised explosive devices from detonating. [/TD]
[TD="align: left"] Heavy metal Trends in armoured fighting vehicle design.PDF [/TD]
[TD="align: left"] The latest developments in precision weapons provide a further strength to the air argument, with systems such as the MBDA Brimstone anti-armour missile providing aircraft with a valuable low-yield fire-and-forget anti-tank capability [/TD]
[TD="align: right"] 2016 [/TD]

[TD="align: left"] Close air platforms [/TD]
[TD="align: left"] Using CSACs, military patrols can travel on foot and still carry backpack-sized jammers that prevent radio-controlled improvised explosive devices from detonating. [/TD]
[TD="align: left"] Heavy metal Trends in armoured fighting vehicle design.PDF [/TD]
[TD="align: left"] Regardless of the nature of a future conflict, the inherent advantages of heavy armour and the tank in particular cannot be completely supplanted by alternatives such as aircraft or ATGWs. [/TD]
[TD="align: right"] 2016 [/TD]

</tbody>

<!-- ************************************************************************** -->
As you see, some entries in the 'technologies' column recur in different rows. What I am looking for is a way to generate a new sheet with every technology mentioned only once, with in the third column an overview of the different media titles, separated with a ; S for instance for battery improvements, the third column would have: CTNSP-2007-Further look at technologies for stabilization operations.pdf; Unravelling the energy tether: new power technologies take aim at the dismounted soldier; Technology Trends SurveyFuture Emerging Technology Trends; 2014 Preparing for War in the Robotic Age. Can anybody please help with this? Thanks<style type="text/css">
body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small }
a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; }
a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; }
comment { display:none; }
</style>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
I can't think of a way to do it with formulas. A macro like that would take about 30 to 60 minutes to write. How many rows of data do you have? What about repeating data in the "Media Title" column; do you want to repeat those?

Jeff
 
Upvote 0
Jeff, Thanks. There are 1100 rows, so doing it manually it would take a lot longer than that. And no, the repeats in de media title column would not be important for this particular task. But so could I pay somebody for creating such a macro? I've reached out to MrExcel consulting on this...
 
Upvote 0
Just FYI - I was just trying to follow forum rules. But the email I sent bounced. Your message to <consult@mrexcel.com> was automatically rejected: Not sure whether you are aware of this problem, which is why I am posting it.
Quota exceeded (mailbox for user is full)


---------- Forwarded message ----------
From: Stephan De Spiegeleire <sdspieg@gmail.com>
To: consult@mrexcel.com
Cc:
Bcc:
Date: Thu, 30 Mar 2017 23:16:36 +0200
Subject: Consulting request
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top