How to concatenate without repeating

[bigsi1984]

Hello,

I run a spread sheet to record orders for manufacturing. To produce a purchase order for suppliers I use a macro that pastes my raw data into a template tab which in turn is linked to an Purchase Order tab. This just rearranges the data into a format that is a bit more readable.

My issue is that the Purchase Order number needs to include the job number to help with paperwork down the line. I have tried writing a massive formula to basically concatenate the PO number with the various job numbers but it comes unstuck when the job numbers differ.

If I have an order where the goods are for a single job then I want the order number to be PO/JOB i.e. 12345/aaaaa. This is quite simple.

If I order for multiple jobs on the same order I want the order number to be PO/JOB1/JOB2 i.e. 12345/aaaaa/bbbbb. Again, this is fairly straight forward.

The problem occurs when I have multiple line for the same job. Example: if there are 5 lines but only 3 job numbers. Therefore I would only want the PO number to be PO/JOB1/JOB2/JOB3. When I try to do this it repeats like this PO/JOB1/JOB1/JOB2/JOB3/JOB3.

Hopefully this makes sense and someone can help.


Thanks,

bigsi1984

 

[Gerald Higgins]

I think I know what you mean but I'm not completely sure.

Can you please post a small sample of your data showing PO numbers and Job numbers, with some different scenarios, and explain exactly what you want to do in each scenario ?

 

[bigsi1984]

[TABLE="width: 1478"]
<colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="4"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" span="2"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" span="2"> <tbody>[TR]
[TD="class: xl64, width: 67, bgcolor: #D9D9D9"]PO[/TD]
[TD="class: xl64, width: 47, bgcolor: #D9D9D9"]Line Item[/TD]
[TD="class: xl64, width: 67, bgcolor: #D9D9D9"]Unique Identifier[/TD]
[TD="class: xl65, width: 69, bgcolor: #D9D9D9"]Date Ordered[/TD]
[TD="class: xl64, width: 118, bgcolor: #D9D9D9"]Requested by[/TD]
[TD="class: xl64, width: 117, bgcolor: #D9D9D9"]Supplier[/TD]
[TD="class: xl64, width: 92, bgcolor: #D9D9D9"]Supplier Contact[/TD]
[TD="class: xl64, width: 93, bgcolor: #D9D9D9"]Category[/TD]
[TD="class: xl64, width: 79, bgcolor: #D9D9D9"]Item[/TD]
[TD="class: xl64, width: 110, bgcolor: #D9D9D9"]MS No.[/TD]
[TD="class: xl65, width: 73, bgcolor: #D9D9D9"]Requested Delivery Date[/TD]
[TD="class: xl66, width: 79, bgcolor: #D9D9D9"]Qty[/TD]
[TD="class: xl64, width: 99, bgcolor: #D9D9D9"]Material Origin[/TD]
[TD="class: xl64, width: 78, bgcolor: #D9D9D9"]Material / Section[/TD]
[TD="class: xl64, width: 77, bgcolor: #D9D9D9"]DIM 1 Width / OD
(mm)[/TD]
[TD="class: xl64, width: 77, bgcolor: #D9D9D9"]DIM 2
Leg / 2nd Box Dim (mm)[/TD]
[TD="class: xl64, width: 77, bgcolor: #D9D9D9"]Depth / Wall Thickness (mm)[/TD]
[TD="class: xl64, width: 77, bgcolor: #D9D9D9"]Length (mm)[/TD]
[TD="class: xl64, width: 98, bgcolor: #D9D9D9"]Grade / Spec[/TD]
[TD="class: xl68, width: 88, bgcolor: #D9D9D9"]Mass per Metre
(KG)[/TD]
[TD="class: xl68, width: 88, bgcolor: #D9D9D9"]+Roll[/TD]
[TD="class: xl67, width: 98, bgcolor: #D9D9D9"] Unit Price [/TD]
[TD="class: xl69, width: 98, bgcolor: yellow"] Line Total [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833[/TD]
[TD="class: xl70, width: 47, bgcolor: transparent"]1[/TD]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833-1[/TD]
[TD="class: xl71, width: 69, bgcolor: transparent"]29/08/17[/TD]
[TD="class: xl70, width: 118, bgcolor: transparent"]Test[/TD]
[TD="class: xl70, width: 117, bgcolor: transparent"]Supplier 1[/TD]
[TD="class: xl70, width: 92, bgcolor: transparent"]Sales[/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"]Flat Bar[/TD]
[TD="class: xl70, width: 79, bgcolor: transparent"]RIM[/TD]
[TD="class: xl70, width: 110, bgcolor: transparent"]22419[/TD]
[TD="class: xl71, width: 73, bgcolor: transparent"]05/09/17[/TD]
[TD="class: xl72, width: 79, bgcolor: transparent"]5.00[/TD]
[TD="class: xl70, width: 99, bgcolor: transparent"]NA[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent"]FLAT BAR[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]110[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]30[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]6100[/TD]
[TD="class: xl70, width: 98, bgcolor: transparent"]EN10025 S275JR[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]+Roll[/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833[/TD]
[TD="class: xl70, width: 47, bgcolor: transparent"]2[/TD]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833-2[/TD]
[TD="class: xl71, width: 69, bgcolor: transparent"]29/08/17[/TD]
[TD="class: xl70, width: 118, bgcolor: transparent"]Test[/TD]
[TD="class: xl70, width: 117, bgcolor: transparent"]Supplier 1[/TD]
[TD="class: xl70, width: 92, bgcolor: transparent"]Sales[/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"]Channel[/TD]
[TD="class: xl70, width: 79, bgcolor: transparent"]BARREL SUPPORT[/TD]
[TD="class: xl70, width: 110, bgcolor: transparent"]22419[/TD]
[TD="class: xl71, width: 73, bgcolor: transparent"]05/09/17[/TD]
[TD="class: xl72, width: 79, bgcolor: transparent"]3.00[/TD]
[TD="class: xl70, width: 99, bgcolor: transparent"]NA[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent"]PFC[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]100[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]50[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]6100[/TD]
[TD="class: xl70, width: 98, bgcolor: transparent"]EN10025 S275JR[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]10.35[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]+Roll[/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833[/TD]
[TD="class: xl70, width: 47, bgcolor: transparent"]3[/TD]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833-3[/TD]
[TD="class: xl71, width: 69, bgcolor: transparent"]29/08/17[/TD]
[TD="class: xl70, width: 118, bgcolor: transparent"]Test[/TD]
[TD="class: xl70, width: 117, bgcolor: transparent"]Supplier 1[/TD]
[TD="class: xl70, width: 92, bgcolor: transparent"]Sales[/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"]Column / Beam[/TD]
[TD="class: xl70, width: 79, bgcolor: transparent"]Beam[/TD]
[TD="class: xl70, width: 110, bgcolor: transparent"]22420[/TD]
[TD="class: xl71, width: 73, bgcolor: transparent"]05/09/17[/TD]
[TD="class: xl72, width: 79, bgcolor: transparent"]2.00[/TD]
[TD="class: xl70, width: 99, bgcolor: transparent"]NA[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent"]UC[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]203[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]203[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]52[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]1730[/TD]
[TD="class: xl70, width: 98, bgcolor: transparent"]EN10025 S275JR[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]52.00[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"] [/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833[/TD]
[TD="class: xl70, width: 47, bgcolor: transparent"]4[/TD]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833-4[/TD]
[TD="class: xl71, width: 69, bgcolor: transparent"]29/08/17[/TD]
[TD="class: xl70, width: 118, bgcolor: transparent"]Test[/TD]
[TD="class: xl70, width: 117, bgcolor: transparent"]Supplier 1[/TD]
[TD="class: xl70, width: 92, bgcolor: transparent"]Sales[/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"]Flat Bar[/TD]
[TD="class: xl70, width: 79, bgcolor: transparent"]RIM[/TD]
[TD="class: xl70, width: 110, bgcolor: transparent"]22446[/TD]
[TD="class: xl71, width: 73, bgcolor: transparent"]05/09/17[/TD]
[TD="class: xl72, width: 79, bgcolor: transparent"]14.00[/TD]
[TD="class: xl70, width: 99, bgcolor: transparent"]NA[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent"]FLAT BAR[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]110[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]30[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]6100[/TD]
[TD="class: xl70, width: 98, bgcolor: transparent"]EN10025 S275JR[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]+Roll[/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833[/TD]
[TD="class: xl70, width: 47, bgcolor: transparent"]5[/TD]
[TD="class: xl70, width: 67, bgcolor: transparent"]53833-5[/TD]
[TD="class: xl71, width: 69, bgcolor: transparent"]29/08/17[/TD]
[TD="class: xl70, width: 118, bgcolor: transparent"]Test[/TD]
[TD="class: xl70, width: 117, bgcolor: transparent"]Supplier 1[/TD]
[TD="class: xl70, width: 92, bgcolor: transparent"]Sales[/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"]Channel[/TD]
[TD="class: xl70, width: 79, bgcolor: transparent"]BARREL SUPPORT[/TD]
[TD="class: xl70, width: 110, bgcolor: transparent"]22446[/TD]
[TD="class: xl71, width: 73, bgcolor: transparent"]05/09/17[/TD]
[TD="class: xl72, width: 79, bgcolor: transparent"]10.00[/TD]
[TD="class: xl70, width: 99, bgcolor: transparent"]NA[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent"]PFC[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]100[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]50[/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 77, bgcolor: transparent"]6100[/TD]
[TD="class: xl70, width: 98, bgcolor: transparent"]EN10025 S275JR[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]10.43[/TD]
[TD="class: xl72, width: 88, bgcolor: transparent"]+Roll[/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[TD="class: xl73, width: 98, bgcolor: transparent"] £ 5.00 [/TD]
[/TR]
</tbody>[/TABLE]

 

[bigsi1984]

[TABLE="width: 950"]
<colgroup><col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="201" style="width: 151pt; mso-width-source: userset; mso-width-alt: 7350;"> <col width="145" style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="196" style="width: 147pt; mso-width-source: userset; mso-width-alt: 7168;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody>[TR]
[TD="class: xl82, width: 198, bgcolor: white, colspan: 3"] [/TD]
[TD="class: xl71, width: 161, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]Supplier:[/TD]
[TD="class: xl91, width: 299, bgcolor: white, colspan: 2"]Supplier 1[/TD]
[TD="class: xl68, width: 204, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]Order No:[/TD]
[TD="class: xl77, width: 403, bgcolor: yellow, colspan: 3"]53833/22419/22420/22446[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]Quote Ref:[/TD]
[TD="class: xl81, colspan: 2"] [/TD]
[TD="class: xl68, width: 145, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Issue No:[/TD]
[TD="class: xl70, width: 59"] [/TD]
[TD="class: xl68, width: 403, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 3"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]Contact:[/TD]
[TD="class: xl72, bgcolor: white, colspan: 2"]Sales[/TD]
[TD="class: xl68, width: 145, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Special Instructions:[/TD]
[TD="class: xl73, width: 462, bgcolor: transparent, colspan: 4"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]Delivery:[/TD]
[TD="class: xl81, bgcolor: white, colspan: 2"] [/TD]
[TD="class: xl68, width: 145, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Tolerances:[/TD]
[TD="class: xl73, width: 462, bgcolor: transparent, colspan: 4"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[bigsi1984]

So I need the job numbers to appear next to the PO number on the order. Just like in the yellow box. But if I have a job number appearing more than once then I don't want it to appear twice in the yellow box.

 

[Mike Szczesny]

Can you use something like this? Perhaps a more ideal solution would come with a macro. But I don't use macros. First, you would need to copy all your numbers in a column. The first formula finds the unique numbers. I put the column "Unique" in cell 1. The formula is =index(range,match(0,countif($C$1:C1,countif(range),0)). Use Cntrl+Shift+Enter. Copy down. In other cell is your formula to concatenate these numbers. The formula is =concatenate(transpose(unique range&" /")). Before you enter, you need to highlight the transpose(.......) portion of your formula with your mouse. Hit F9. Remove the {,} and last "/". Hit enter.

[TABLE="width: 437"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD="align: right"]53833[/TD]
[TD][/TD]
[TD]Unique[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53833[/TD]
[TD][/TD]
[TD="align: right"]53833[/TD]
[TD][/TD]
[TD]53833 /22419 /22420 /22446 [/TD]
[/TR]
[TR]
[TD="align: right"]53833[/TD]
[TD][/TD]
[TD="align: right"]22419[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53833[/TD]
[TD][/TD]
[TD="align: right"]22420[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53833[/TD]
[TD][/TD]
[TD="align: right"]22446[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22419[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22419[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22420[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22446[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22446[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

If your system comes with the TEXTJOIN function, control+shift+enter, not just enter:

=TEXTJOIN("/",TRUE,IF(FREQUENCY(IF(1-(Jobs),MATCH(Jobs,Jobs,0)),ROW(Jobs)-ROW(INDEX(Jobs,1,1))+1),Jobs,""))

 

[bigsi1984]

Thanks for the help.

 

[arthurbr]

Multiple cross poster - reported