Concatenate based on values in another column

Steve1977

New Member
Joined
May 16, 2019
Messages
33
Hi all, first time post so I hope I'm following the correct procedure :)

Was really hoping someone could give me guidance. Basically I have a file as per the information below and I need to merge Column2 and Column3 - but based on the value in Column 1.

PARTNO1HyundaiAccent
PARTNO1Hyundaii30
PARTNO1Hyundaii40
PARTNO2HyundaiAccent
PARTNO2SubaruImpreza
PARTNO2ToyotaCelica
PARTNO3ToyotaCelica
PARTNO3ToyotaMR2

<tbody>
</tbody>

So in the past I've done a CONCATENATE formula for each PARTNO in Column 1, but I now have significantly more part numbers and it's way too time consuming to do them all in one.

Basically the finished file needs to look like this:

PARTNO1Hyundai Accent, i30, i40
PARTNO2Hyundai Accent, Subaru Impreza, Toyota Celica
PARTNO3Toyota Celica, MR2

<tbody>
</tbody>

What's important to mention with this is that if the Car Make in Column 2 is different, it then includes the next unique Car Make from Column 2 as long as it's associated with the PARTNO in Column A.

However, I also don't mind if it looks like this with the next unique car make on a second line.

PARTNO1Hyundai Accent, i30, i40
PARTNO2Hyundai Accent
PARTNO2Subaru Impreza
PARTNO2Toyota Celica
PARTNO3Toyota Celica, MR2

<tbody>
</tbody>


Hope this has been explained well and appreciate any feedback to guide me along the way :)
 
Try
Code:
' Removes Brackets
    Cells.Replace What:=" (*)", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Note the space before the bracket
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try
Code:
' Removes Brackets
    Cells.Replace What:=" (*)", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Note the space before the bracket

:) :) Nice one sir!
 
Upvote 0
For future reference

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Apologies for the cross posting - certainly won't be doing that again especially with how helpful this place is :)

I'm revisiting my file and wondered if it's possible to tweak and to apply a minimum and maximum date. Basically, this is my data

PARTNO1HyundaiAccent01/01/9431/12/99
PARTNO1Hyundaii3001/01/9831/12/16
PARTNO1Hyundaii4001/01/9931/12/17
PARTNO2HyundaiAccent01/01/9431/12/99
PARTNO2SubaruImpreza01/01/9331/12/98
PARTNO2ToyotaCelica01/01/9531/12/01
PARTNO3ToyotaCelica01/08/9431/05/02
PARTNO3ToyotaMR201/01/9131/12/01

<tbody>
</tbody>


Using this fine thread I have the necessary code to put all applications on one line and also on how to seperate Column 2.
But...how would I incorporate the date? So based on the above sample, the output would be as follows:


PARTNO1Hyundai Accent, i30, i40 01/94>12/17
PARTNO2Hyundai Accent 01/94>12/99. Subaru Impreza 01/93>12/98. Toyota Celica 01/95>12/01
PARTNO3Toyota Celica, MR2 08/94>05/02

<tbody>
</tbody>


Trying best to explain but basically it now groups up the dates by Car manufacturer (Column 2). So PARTNO1 is Hyundai with a minimum date value of 01/94 and a maximum date value of 12/17.
PARTNO2, Hyundai's Min and Max is 01/94>12/99 and so on.


Thank you for any help :)
 
Last edited:
Upvote 0
This is totally different question & needs a new thread please.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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