Concatenate based on values in another column

[Steve1977]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai[/TD]
[TD]i30[/TD]
[/TR]
[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai[/TD]
[TD]i40[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Subaru[/TD]
[TD]Impreza[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Toyota[/TD]
[TD]Celica[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota[/TD]
[TD]Celica[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota[/TD]
[TD]MR2[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai Accent, i30, i40[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Hyundai Accent, Subaru Impreza, Toyota Celica[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota Celica, MR2[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai Accent, i30, i40[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Hyundai Accent[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Subaru Impreza[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Toyota Celica[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota Celica, MR2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope this has been explained well and appreciate any feedback to guide me along the way

 

[Fluff]

Try

' Removes Brackets
    Cells.Replace What:=" (*)", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Note the space before the bracket

 

[Steve1977]

Try

' 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!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Fluff]

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.

 

[Steve1977]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD]01/01/94[/TD]
[TD]31/12/99[/TD]
[/TR]
[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai[/TD]
[TD]i30[/TD]
[TD]01/01/98[/TD]
[TD]31/12/16[/TD]
[/TR]
[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai[/TD]
[TD]i40[/TD]
[TD]01/01/99[/TD]
[TD]31/12/17[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Hyundai[/TD]
[TD]Accent[/TD]
[TD]01/01/94[/TD]
[TD]31/12/99[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Subaru[/TD]
[TD]Impreza[/TD]
[TD]01/01/93[/TD]
[TD]31/12/98[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Toyota[/TD]
[TD]Celica[/TD]
[TD]01/01/95[/TD]
[TD]31/12/01[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota[/TD]
[TD]Celica[/TD]
[TD]01/08/94[/TD]
[TD]31/05/02[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota[/TD]
[TD]MR2[/TD]
[TD]01/01/91[/TD]
[TD]31/12/01[/TD]
[/TR]
</tbody>[/TABLE]


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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]PARTNO1[/TD]
[TD]Hyundai Accent, i30, i40 01/94>12/17[/TD]
[/TR]
[TR]
[TD]PARTNO2[/TD]
[TD]Hyundai Accent 01/94>12/99. Subaru Impreza 01/93>12/98. Toyota Celica 01/95>12/01[/TD]
[/TR]
[TR]
[TD]PARTNO3[/TD]
[TD]Toyota Celica, MR2 08/94>05/02[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Fluff]

This is totally different question & needs a new thread please.