How to sort / group data from one cell over colums

MRCBA

New Member
Joined
Feb 1, 2018
Messages
1
Hi everybody,

I am a bit new into the Xcell world, and I am breaking my head around the following topic, hope you can help me because I dont know which formula I have to use.

So I have a bunch of orders wich all have a different sub content (order 1 variant 1 has content A, B, C, D, E - Order 2 variant 2 has content E, A, F, X, etc).

I would like to group this subcontent so that every data has it own column. If an order does not have the specific head of the column, it can be left in blank. I put all the data from one cell in columns and I filtered it alphabeticly but i cant get it structured.

Example beginning raw data (content of order is in one cell)
[TABLE="width: 1058"]
<tbody>[TR]
[TD]Order number[/TD]
[TD]Model type[/TD]
[TD]Content of order[/TD]
[/TR]
[TR]
[TD="align: right"]100001[/TD]
[TD]Version 1[/TD]
[TD] $21 $26 $48 $G3 C25 FTE P21 P24 PCI PG6 PK2 PNB PQS PSS PTC PW1 PXX PZ4 WAC WL3 X4A ZN4[/TD]
[/TR]
[TR]
[TD="align: right"]100002[/TD]
[TD]Version 1[/TD]
[TD] $21 $26 $G3 $GE C25 FTE P1Z P21 P24 PB3 PBR PCI PDU PFK PG6 PK2 PNB PQS PSS PW1 PXX PZ4 RKG X4A ZN4[/TD]
[/TR]
[TR]
[TD="align: right"]100003[/TD]
[TD]Version 1[/TD]
[TD] $21 $26 $G3 C25 FTE P1Z P21 P24 PB3 PBR PCI PDU PFK PG6 PK2 PNB PQS PSS PUK PW1 PXX PZ4 WL3 X4A ZN4[/TD]
[/TR]
[TR]
[TD="align: right"]100004[/TD]
[TD]Version 2[/TD]
[TD] $21 $26 $G3 C25 FTE P21 P24 PB3 PCI PDS PFK PG6 PK2 PNB PSS PUK PW1 PXX PZ4 WAC WL3 X4A ZN4[/TD]
[/TR]
[TR]
[TD="align: right"]100005[/TD]
[TD]Version 2[/TD]
[TD] $21 $26 $G3 C25 FTE P21 P24 PBR PCI PDS PFK PG6 PK2 PNB PQS PSS PTC PUK PW1 PXX PZ4 WAC WGR WL3 X4A ZN4[/TD]
[/TR]
[TR]
[TD="align: right"]100006[/TD]
[TD]Version 2[/TD]
[TD] $23 $48 $BI $CE $G3 C25 FTE P23 PAD PG6 PK2 PML PSP PSS PTC PW1 PXX WGR WH2 X4A ZB8[/TD]
[/TR]
[TR]
[TD="align: right"]100007[/TD]
[TD]Version 2[/TD]
[TD] $23 $48 $CE $G3 4L2 C25 FGU P23 PCO PK2 PML PXX WH2 X4A ZB8[/TD]
[/TR]
[TR]
[TD="align: right"]100008[/TD]
[TD]Version 2[/TD]
[TD] $23 $48 $CE $G3 4L2 C25 FGU P23 PK2 PML PW1 PXX WH2 X4A ZB8[/TD]
[/TR]
[TR]
[TD="align: right"]100009[/TD]
[TD]Version 2[/TD]
[TD] $23 $48 $CE $G3 C25 FGU P23 PCO PG5 PK2 PML PSS PUB PXX WH2 X4A ZB8[/TD]
[/TR]
[TR]
[TD="align: right"]100019[/TD]
[TD]Version 3[/TD]
[TD] $23 $CE $G3 4L2 C25 FGU P23 PCO PK2 PML PW1 PXX WH2 X4A ZB8[/TD]
[/TR]
[TR]
[TD="align: right"]100020[/TD]
[TD]Version 3[/TD]
[TD] $23 $CE $G3 4L2 C25 FTE P23 PK2 PML PXX WH2 X4A ZB8[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

I divided the content of order from one cell to +/- 24 columns and have placed the individual data as heads. I manually made an example (untill $48).

I would like to know if there is a formula for this... :confused:

Thank you, much appreciated!

MRBCA
[TABLE="width: 1364"]
<tbody>[TR]
[TD]Order number[/TD]
[TD]Model type[/TD]
[TD]$21[/TD]
[TD]$23[/TD]
[TD]$24[/TD]
[TD]$25[/TD]
[TD]$26[/TD]
[TD]$27[/TD]
[TD]$28[/TD]
[TD]$48[/TD]
[TD]Etcetera[/TD]
[TD]Etcetera[/TD]
[TD="colspan: 2"]Etcetera[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100001[/TD]
[TD]Version 1[/TD]
[TD]$21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$48[/TD]
[TD]$G3[/TD]
[TD]C25[/TD]
[TD]FTE[/TD]
[TD]P21[/TD]
[TD]P24[/TD]
[TD]PCI[/TD]
[TD]PG6[/TD]
[TD]PK2[/TD]
[TD]PNB[/TD]
[TD]PQS[/TD]
[TD]PSS[/TD]
[TD]PTC[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]PZ4[/TD]
[TD]WAC[/TD]
[TD]WL3[/TD]
[TD]X4A[/TD]
[TD]ZN4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100002[/TD]
[TD]Version 1[/TD]
[TD]$21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$G3[/TD]
[TD]$GE[/TD]
[TD]C25[/TD]
[TD]FTE[/TD]
[TD]P1Z[/TD]
[TD]P21[/TD]
[TD]P24[/TD]
[TD]PB3[/TD]
[TD]PBR[/TD]
[TD]PCI[/TD]
[TD]PDU[/TD]
[TD]PFK[/TD]
[TD]PG6[/TD]
[TD]PK2[/TD]
[TD]PNB[/TD]
[TD]PQS[/TD]
[TD]PSS[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]PZ4[/TD]
[TD]RKG[/TD]
[TD]X4A[/TD]
[TD]ZN4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100003[/TD]
[TD]Version 1[/TD]
[TD]$21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$G3[/TD]
[TD]C25[/TD]
[TD]FTE[/TD]
[TD]P1Z[/TD]
[TD]P21[/TD]
[TD]P24[/TD]
[TD]PB3[/TD]
[TD]PBR[/TD]
[TD]PCI[/TD]
[TD]PDU[/TD]
[TD]PFK[/TD]
[TD]PG6[/TD]
[TD]PK2[/TD]
[TD]PNB[/TD]
[TD]PQS[/TD]
[TD]PSS[/TD]
[TD]PUK[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]PZ4[/TD]
[TD]WL3[/TD]
[TD]X4A[/TD]
[TD]ZN4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100004[/TD]
[TD]Version 2[/TD]
[TD]$21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$G3[/TD]
[TD]C25[/TD]
[TD]FTE[/TD]
[TD]P21[/TD]
[TD]P24[/TD]
[TD]PB3[/TD]
[TD]PCI[/TD]
[TD]PDS[/TD]
[TD]PFK[/TD]
[TD]PG6[/TD]
[TD]PK2[/TD]
[TD]PNB[/TD]
[TD]PSS[/TD]
[TD]PUK[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]PZ4[/TD]
[TD]WAC[/TD]
[TD]WL3[/TD]
[TD]X4A[/TD]
[TD]ZN4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100005[/TD]
[TD]Version 2[/TD]
[TD]$21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$G3[/TD]
[TD]C25[/TD]
[TD]FTE[/TD]
[TD]P21[/TD]
[TD]P24[/TD]
[TD]PBR[/TD]
[TD]PCI[/TD]
[TD]PDS[/TD]
[TD]PFK[/TD]
[TD]PG6[/TD]
[TD]PK2[/TD]
[TD]PNB[/TD]
[TD]PQS[/TD]
[TD]PSS[/TD]
[TD]PTC[/TD]
[TD]PUK[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]PZ4[/TD]
[TD]WAC[/TD]
[TD]WGR[/TD]
[TD]WL3[/TD]
[TD]X4A[/TD]
[TD]ZN4[/TD]
[/TR]
[TR]
[TD="align: right"]100006[/TD]
[TD]Version 2[/TD]
[TD] [/TD]
[TD]$23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$48[/TD]
[TD]$BI[/TD]
[TD]$CE[/TD]
[TD]$G3[/TD]
[TD]C25[/TD]
[TD]FTE[/TD]
[TD]P23[/TD]
[TD]PAD[/TD]
[TD]PG6[/TD]
[TD]PK2[/TD]
[TD]PML[/TD]
[TD]PSP[/TD]
[TD]PSS[/TD]
[TD]PTC[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]WGR[/TD]
[TD]WH2[/TD]
[TD]X4A[/TD]
[TD]ZB8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100007[/TD]
[TD]Version 2[/TD]
[TD] [/TD]
[TD]$23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$48[/TD]
[TD]$CE[/TD]
[TD]$G3[/TD]
[TD]4L2[/TD]
[TD]C25[/TD]
[TD]FGU[/TD]
[TD]P23[/TD]
[TD]PCO[/TD]
[TD]PK2[/TD]
[TD]PML[/TD]
[TD]PXX[/TD]
[TD]WH2[/TD]
[TD]X4A[/TD]
[TD]ZB8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100008[/TD]
[TD]Version 2[/TD]
[TD] [/TD]
[TD]$23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$48[/TD]
[TD]$CE[/TD]
[TD]$G3[/TD]
[TD]4L2[/TD]
[TD]C25[/TD]
[TD]FGU[/TD]
[TD]P23[/TD]
[TD]PK2[/TD]
[TD]PML[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]WH2[/TD]
[TD]X4A[/TD]
[TD]ZB8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100009[/TD]
[TD]Version 2[/TD]
[TD] [/TD]
[TD]$23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$48[/TD]
[TD]$CE[/TD]
[TD]$G3[/TD]
[TD]C25[/TD]
[TD]FGU[/TD]
[TD]P23[/TD]
[TD]PCO[/TD]
[TD]PG5[/TD]
[TD]PK2[/TD]
[TD]PML[/TD]
[TD]PSS[/TD]
[TD]PUB[/TD]
[TD]PXX[/TD]
[TD]WH2[/TD]
[TD]X4A[/TD]
[TD]ZB8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]100019[/TD]
[TD]Version 3[/TD]
[TD] [/TD]
[TD]$23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$CE[/TD]
[TD]$G3[/TD]
[TD]4L2[/TD]
[TD]C25[/TD]
[TD]FGU[/TD]
[TD]P23[/TD]
[TD]PCO[/TD]
[TD]PK2[/TD]
[TD]PML[/TD]
[TD]PW1[/TD]
[TD]PXX[/TD]
[TD]WH2[/TD]
[TD]X4A[/TD]
[TD]ZB8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col span="2"><col span="3"><col><col><col span="2"><col><col span="3"><col span="12"><col><col><col><col><col></colgroup>[/TABLE]
 

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