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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

QTY​

[/td][td]

Data​

[/td][td]

Value​

[/td][td][/td][td]

QTY​

[/td][td]

Data​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

TSK​

[/td][td]

desc1​

[/td][td][/td][td]

1​

[/td][td]

TSK​

[/td][td]

desc1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

ASD​

[/td][td]

DESC201​

[/td][td][/td][td]

1​

[/td][td]

ASD​

[/td][td]

DESC201​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1​

[/td][td]

ASDF​

[/td][td]

des30​

[/td][td][/td][td]

1​

[/td][td]

ASD​

[/td][td]

DESC201​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td]

ASDF​

[/td][td]

des30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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