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]

PO	Line Item	Unique Identifier	Date Ordered	Requested by	Supplier	Supplier Contact	Category	Item	MS No.	Requested Delivery Date	Qty	Material Origin	Material / Section	DIM 1 Width / OD (mm)	DIM 2 Leg / 2nd Box Dim (mm)	Depth / Wall Thickness (mm)	Length (mm)	Grade / Spec	Mass per Metre (KG)	+Roll	Unit Price	Line Total
53833	1	53833-1	29/08/17	Test	Supplier 1	Sales	Flat Bar	RIM	22419	05/09/17	5.00	NA	FLAT BAR	110		30	6100	EN10025 S275JR		+Roll	£ 5.00	£ 5.00
53833	2	53833-2	29/08/17	Test	Supplier 1	Sales	Channel	BARREL SUPPORT	22419	05/09/17	3.00	NA	PFC	100	50		6100	EN10025 S275JR	10.35	+Roll	£ 5.00	£ 5.00
53833	3	53833-3	29/08/17	Test	Supplier 1	Sales	Column / Beam	Beam	22420	05/09/17	2.00	NA	UC	203	203	52	1730	EN10025 S275JR	52.00		£ 5.00	£ 5.00
53833	4	53833-4	29/08/17	Test	Supplier 1	Sales	Flat Bar	RIM	22446	05/09/17	14.00	NA	FLAT BAR	110		30	6100	EN10025 S275JR		+Roll	£ 5.00	£ 5.00
53833	5	53833-5	29/08/17	Test	Supplier 1	Sales	Channel	BARREL SUPPORT	22446	05/09/17	10.00	NA	PFC	100	50		6100	EN10025 S275JR	10.43	+Roll	£ 5.00	£ 5.00

<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> </tbody>

 

[bigsi1984]

	Supplier:	Supplier 1	Order No:	53833/22419/22420/22446
Quote Ref:		Issue No:
Contact:	Sales	Special Instructions:
Delivery:		Tolerances:

<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> </tbody>

 

[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.

53833		Unique
53833		53833		53833 /22419 /22420 /22446
53833		22419
53833		22420
53833		22446
22419
22419
22420
22446
22446

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

 

[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