Auto Splitting of Data

[Ruban S]

Dear,

I have data like this

SHORT	LONG	NOUN	MODIFIER	ATT N1	ATT V1	ATT N2	ATT V2	ATT N3	ATT V3
CHEMICAL,FAST-DRY SELF VULCANIZING CEMNT	CHEMICAL, TYPE:FAST-DRY SELF VULCANIZING CEMENT, PACK SIZE:236.6ML	CHEMICAL	--	TYPE		PACK SIZE
CHEMICAL,FLUSH,LOCO,236.6ML	CHEMICAL,FLUSH, BRAND:LOCO, PACK SIZE:236.6ML	CHEMICAL	FLUSH	MANUFACTURER		BRAND		PACK SIZE

I Need it like this

SHORT	LONG	NOUN	MODIFIER	ATT N1	ATT V1	ATT N2	ATT V2	ATT N3	ATT V3
CHEMICAL,FAST-DRY SELF VULCANIZING CEMNT	CHEMICAL, TYPE:FAST-DRY SELF VULCANIZING CEMENT, PACK SIZE:236.6ML	CHEMICAL	--	TYPE	FAST-DRY SELF VULCANIZING CEMENT	PACK SIZE	236.6ML
CHEMICAL,FLUSH,LOCO,236.6ML	CHEMICAL,FLUSH, BRAND:LOCO, PACK SIZE:236.6ML	CHEMICAL	FLUSH	MANUFACTURER		BRAND	LOCO	PACK SIZE	236.6ML

Likewise, I have many records, Please help me with some solution.
Data has to be Autofill based on LONG

 

[Ruban S]

Please let me know, if additional info Needed

 

[Ruban S]

Please, anyone, help me.

 

[Ruban S]

Below is a Simple Example for this Thread.

I have Data Like this

SHORT	LONG	X	Y	XX	XX1	YY	YY1	ZZ	ZZ1
A,B,1,2,3	A,B,C:1,D:2,E:3	A	B	C		D		E
A,1,3	A,B,C:1,E:3	A	--	C		D		E

And, I need it like this

SHORT	LONG	X	Y	XX	XX1	YY	YY1	ZZ	ZZ1
A,B,1,2,3	A,B,C:1,D:2,E:3	A	B	C	1​	D	2​	E	3​
A,1,3	A,B,C:1,E:3	A	--	C	1​	D	--	E	3​

 

[sandy666]

Could you explain the logic how you want to achieve expected result?

 

[Ruban S]

Actually, We have to consider LONG.
First, in column XX1, it has to check LONG whether XX (i.e. C) is available are not. If yes it has to fetch the value Corresponding to C (i.e. 1). Same way for the rest of the cells.
Please let me know if you need more clarification.

 

[sandy666]

I see you want to do it "in place"
Power Query can't do that so you need to wait for someone else with vba

 

[Ruban S]

OK Thank you.

 

[Ruban S]

Please let me know. If more info needed.

 

[Ruban S]

Here is another simple Example
i have like this

DESCRIPTION

NOUN

MODIFIER

ATT1

VAL1

ATT2

VAL2

ATT3

VAL3

ATT4

VAL4

ATT5

VAL5

ATT6

VAL6

ATT7

VAL7

ATT8

VAL8

ATT9

VAL9

ATT10

VAL10

ABRASIVE:FLAP WHEEL;TYPE:CRIMPED, BRUSH,OUTSIDE DIAMETER:8IN,FACE WIDTH:7/8IN,ARBOR SIZE:2IN

ABRASIVE

FLAP WHEEL

TYPE

OUTSIDE DIAMETER

FACE WIDTH

ARBOR SIZE

MOUNTING TYPE

GRIT

ABRASIVE MATERIAL

SPEED RATING

CERTIFICATION/STANDARD

ADDITIONAL FEATURES

I Need Like this as value populated in corresponding cells as Highlighted in Bold

DESCRIPTION

NOUN

MODIFIER

ATT1

VAL1

ATT2

VAL2

ATT3

VAL3

ATT4

VAL4

ATT5

VAL5

ATT6

VAL6

ATT7

VAL7

ATT8

VAL8

ATT9

VAL9

ATT10

VAL10

ABRASIVE:FLAP WHEEL;TYPE:CRIMPED, BRUSH,OUTSIDE DIAMETER:8IN,FACE WIDTH:7/8IN,ARBOR SIZE:2IN

ABRASIVE

FLAP WHEEL

TYPE

CRIMPED, BRUSH

OUTSIDE DIAMETER

8IN

FACE WIDTH

7/8IN

ARBOR SIZE

2IN

MOUNTING TYPE

--

GRIT

--

ABRASIVE MATERIAL

--

SPEED RATING

--

CERTIFICATION/STANDARD

--

ADDITIONAL FEATURES

--

Please anyone help me