Split Value

[vijaychennai]

Hello All,

good day. I have excel file in the below format.


Sheet Name :- Sheet1

QTY	Data	Value			Solution	QTY	Data	Value
1	TSK	desc1				1	TSK	desc1
2	ASD	DESC201				1	ASD	DESC201
1	ASDF	des30				1	ASD	DESC201
						1	ASDF	des30

<tbody>
</tbody>

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​	G​
1​	QTY​	Data​	Value​		QTY​	Data​	Value​
2​	1​	TSK​	desc1​		1​	TSK​	desc1​
3​	2​	ASD​	DESC201​		1​	ASD​	DESC201​
4​	1​	ASDF​	des30​		1​	ASD​	DESC201​
5​					1​	ASDF​	des30​
6​
7​

Array formula in F2 copied down
=IF(ROWS(F$2:F2)>SUM(A$2:A$4),"",INDEX(B$2:B$4,IFERROR(1+MATCH(ROWS(F$2:F2)-1,SUBTOTAL(9,OFFSET(A$2,,,ROW(A$2:A$4)-ROW(A$2)+1))),1)))
confirmed with Ctrl+Shift+Enter, not just Enter

Formula in E2 copied down
=IF(F2<>"",1,"")

Formula in G2 copied down
=IF(F2="","",VLOOKUP(F2,B:C,2,0))

Hope this helps

M.

 

[vijaychennai]

Hello friend,

I am looking for Excel VBA.

Regards,

 

[MickG]

Try:-
NB:- This code will alter your data. !!

[COLOR="Navy"]Sub[/COLOR] MG21Mar22
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Cells(n, 1) > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] Ac = 1 To Cells(n, 1) - 1
            Cells(n, 1) = 1
            Cells(n, 1).Resize(, 3).Copy
            Cells(n + Ac, 1).Insert shift:=xlDown
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[vijaychennai]

Hi Mickg,

Great... It working fine... but it take time to update.

Ur Asset for Mrexcel.

Thanks once again.

 

[MickG]

You're welcome
NB:- You could try the code with the "Screenupdating" might improve the time !!!

[COLOR="Navy"]Sub[/COLOR] MG21Mar40
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.ScreenUpdating = False
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Cells(n, 1) > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] Ac = 1 To Cells(n, 1) - 1
            Cells(n, 1) = 1
            Cells(n, 1).Resize(, 3).Copy
            Cells(n + Ac, 1).Insert shift:=xlDown
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[vijaychennai]

Hi Mickg,

Thanks once again. It's working great.

Thanks.

 

[MickG]

You're welcome