complicated extract items from cells into multiple cells based on four or five or six items

[abdo meghari]

hi

i need way to seperate data cell for the column into multiple cells for multiple columns based on four or five or six items . so in column E contains some items but are differnt items numbers

if I have six items lik in E2 then when split into multiple cells for multiple columns ,then the column G should be three items and the column H should be two items and the column I should be one item and the column J should be the value as in column E. and if I have five items like in E2,3,7,12,13 , then the column G should be two items and the column H should be two items and the column I should be one item and the column J should be the value as in column E. and if I have four items like E6,8,9,10,11,14, then the column G should be two items and the column H should be one item and the column I should be one item and the column J should be the value as in column E.

note: when collect some items into the cell when split the data ignore the different space . the most important should be space among the items to split for each items into multiple cells based on spaces among the items
the orginal data are in column E,F

SS .xlsm
	E	F
1	MRGED	QTY
2	BS 1200R20 18PR G580 TCF JAP	100.00
3	BS 1200R20 G580 TC THI	200.00
4	BS 1200R24 20PR G582 JAP	300.00
5	BS 13R22.5 R187 JAP	400.00
6	BS 1400R20 VSJ JAP	500.00
7	BS 155R12C T R623 INDO	600.00
8	BS 165R13C R624 INDO	700.00
9	BS 175/70R13 EP150 INDO	800.00
10	BS 175/70R14 EP150 THI	200.00
11	BS 1800R25** VKT JAP	230.00
12	BS 20.5R25** 2RR VUT IND	120.00
13	BS 1600-25 28PR RLS JAP	100.00
14	BS LT285/75R16 AT001 JAP	120.00
ITEM

result from column G: J

SS .xlsm
	G	H	I	J
1	BRAND	TYPE	ORIGIN	QTY
2	BS 1200R20 18PR	G580 TCF	JAP	100.00
3	BS 1200R20	G580 TC	THI	200.00
4	BS 1200R24	20PR G582	JAP	300.00
5	BS13R22.5	R187	JAP	400.00
6	BS 1400R20	VSJ	JAP	500.00
7	BS 155R12C T	R623	INDO	600.00
8	BS 165R13C	R624	INDO	700.00
9	BS 175/70R13	EP150	INDO	800.00
10	BS 175/70R14	EP150	THI	200.00
11	BS 1800R25**	VKT	JAP	230.00
12	BS 20.5R25**	2RR VUT	IND	120.00
13	BS 1600-25	28PR RLS	JAP	100.00
14	BS LT285/75R16	AT001	JAP	120.00
ITEM

I hope what I ask for it can be possible and logical

 

[Sektor]

Link to workbook

Sub SplitItemsVarSize()

    Dim r As Long, strNorm As String
    Dim items_count As Integer, col As Integer
    Dim items_size As Variant
    Dim start_index As Variant, end_index As Variant
    Dim index As Variant
    Dim arrItems As Variant
    Dim dic As Object
    Dim arrCounts As Variant
    Dim txt As String
    Set dic = CreateObject("Scripting.Dictionary")
   
    dic(4) = Array(2, 1, 1)
    dic(5) = Array(2, 2, 1)
    dic(6) = Array(3, 2, 1)
   
    For r = 2 To 14
        strNorm = WorksheetFunction.Trim(Cells(r, "E"))
        arrItems = Split(strNorm, , , vbTextCompare)
        items_count = UBound(arrItems) + 1
        arrCounts = dic(items_count)
        end_index = -1
        col = 7 '//Column G
        For Each items_size In arrCounts
            txt = vbNullString
            start_index = end_index + 1
            end_index = end_index + items_size
            For index = start_index To end_index
                txt = txt & " " & arrItems(index)
            Next
            Cells(r, col) = Trim$(txt)
            Cells(r, "J") = strNorm
            col = col + 1
        Next
    Next
   
    MsgBox "Well done!", vbInformation
   
End Sub

 

[abdo meghari]

thanks, but the last column should be the numbers as in column F , your code repeat the column E into column J !

 

[Sektor]

You wrote:

the column J should be the value as in column E

?

 

[Sektor]

Changed this (also have update workbook):

Cells(r, "J") = strNorm

to this

Cells(r, "J") = Cells(r, "F")

 

[abdo meghari]

You wrote:

the column J should be the value as in column E

?

sorry ! my mistake .
just I have question . If I have many sheets with the same structure as in OP . how implement your code for all sheets?
thanks

 

[Sektor]

1. You need to change the signature of SplitItemsVarSize() to SplitItemsVarSize(sheet As Worksheet).
2. Add procedure which loops over required sheets' name. The way you obtain the array of sheets' names is up you. In my example I'm using hard-coded inline array:

Sub ProcessSeveralSheets()
    Dim sheetName As Variant
    For Each sheetName In [{"Sheet1", "Sheet2"}]
        Call SplitItemsVarSize(Sheets(sheetName))
    Next
    MsgBox "Well done!", vbInformation
End Sub

Workbook is updated.

 

[abdo meghari]

thanks , but it gives error as in pic in this line

 Call SplitItemsVarSize(Sheets(sheetName))

 

[abdo meghari]

You need to change the signature of SplitItemsVarSize() to SplitItemsVarSize(sheet As Worksheet).

sorry ! I forgot change this as you said
many thanks ! all things are good

 

[Sektor]

You're welcome! ?