Place sub category's letters in all items

Panoos64 · Oct 15, 2019

All hi, Kindly require to provide me a support so that a VBA code to run through col. “G” and where is missing the sub category, should place the 4 letters on left of dash in each item. However, the subcategory which consists with 4 initial letters exists in some of the items.
Therefore, that the spreadsheet’s row are 15000+. Please see below an extract of original date and expected result. Thank you all in advance

Original data

 [TABLE="class: MsoNormalTable, width: 266"]
<tbody>[TR]
[TD="width: 42"][/TD]
[TD="width: 183"]

F

[/TD]
[TD="width: 218"]

G

[/TD]
[/TR]
[TR]
[TD="width: 42"]

13

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-Royal Gala Apples x 6

[/TD]
[/TR]
[TR]
[TD="width: 42"]

14

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-Kiwi Fruit x 6

[/TD]
[/TR]
[TR]
[TD="width: 42"]

15

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-Red Grapes 600gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

16

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Oranges x 5

[/TD]
[/TR]
[TR]
[TD="width: 42"]

17

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Raspberries 100gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

18

[/TD]
[TD="width: 183"]

Fruits Total

[/TD]
[TD="width: 218"]

FRUITS

[/TD]
[/TR]
[TR]
[TD="width: 42"]

19

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-Sliced Wholemeal 1000gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

20

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-White Rolls x 24

[/TD]
[/TR]
[TR]
[TD="width: 42"]

21

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-Penne Pasta 800 gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

22

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

-White Baguettes x 3

[/TD]
[/TR]
[TR]
[TD="width: 42"]

23

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

BAKE-Spaghetti 500gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

24

[/TD]
[TD="width: 183"]

Bakeries & Grains Total

[/TD]
[TD="width: 218"]

BAKERIES & GRAINS

[/TD]
[/TR]
</tbody>[/TABLE]

Expected result

 [TABLE="class: MsoNormalTable, width: 266"]
<tbody>[TR]
[TD="width: 42"][/TD]
[TD="width: 183"]

F

[/TD]
[TD="width: 218"]

G

[/TD]
[/TR]
[TR]
[TD="width: 42"]

13

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Royal Gala Apples x 6

[/TD]
[/TR]
[TR]
[TD="width: 42"]

14

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Kiwi Fruit x 6

[/TD]
[/TR]
[TR]
[TD="width: 42"]

15

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Red Grapes 600gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

16

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Oranges x 5

[/TD]
[/TR]
[TR]
[TD="width: 42"]

17

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

FRUI-Raspberries 100gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

18

[/TD]
[TD="width: 183"]

Fruits Total

[/TD]
[TD="width: 218"]

FRUITS

[/TD]
[/TR]
[TR]
[TD="width: 42"]

19

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

BAKE-Sliced Wholemeal 1000gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

20

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

BAKE-White Rolls x 24

[/TD]
[/TR]
[TR]
[TD="width: 42"]

21

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

BAKE-Penne Pasta 800 gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

22

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

BAKE-White Baguettes x 3

[/TD]
[/TR]
[TR]
[TD="width: 42"]

23

[/TD]
[TD="width: 183"]

FOOD

[/TD]
[TD="width: 218"]

BAKE-Spaghetti 500gr

[/TD]
[/TR]
[TR]
[TD="width: 42"]

24

[/TD]
[TD="width: 183"]

Bakeries & Grains Total

[/TD]
[TD="width: 218"]

BAKERIES & GRAINS

[/TD]
[/TR]
</tbody>[/TABLE]

steve the fish · Oct 15, 2019

Where does FRUI and BAKE come from??

Panoos64 · Oct 15, 2019

Hi Steve, there is not stable cells which contains the codes of sub category, (4 letters on left of the dash). However are in the last cells of each category, e.g. category FRUITS have range of items from "G13" TO "G250" the code FRUI will be at least on last cells, G250,G249,G248,G247,G246,G245. Approximately the last 5-8 cells. Therefore that the first row of items is the "G13" and the rows above are headings. Thank you for your interesting to resolve my issue

steve the fish · Oct 15, 2019

First check this formula does what you expect in all of your data. I need to understand your rule.

=IF(LEFT(G13)="-",LEFT(INDEX(G13:G24,MATCH(1,INDEX(--(RIGHT(F13:F24,5)="Total"),0),0)),4)&G13,G13)

Fluff · Oct 15, 2019

Similar idea

Book1

F

G

H

13

FOOD

-Royal Gala Apples x 6

FRUI-Royal Gala Apples x 6

14

FOOD

-Kiwi Fruit x 6

FRUI-Kiwi Fruit x 6

15

FOOD

-Red Grapes 600gr

FRUI-Red Grapes 600gr

16

FOOD

FRUI-Oranges x 5

17

FOOD

FRUI-Raspberries 100gr

18

Fruits Total

FRUITS

19

FOOD

-Sliced Wholemeal 1000gr

BAKE-Sliced Wholemeal 1000gr

20

FOOD

-White Rolls x 24

BAKE-White Rolls x 24

21

FOOD

-Penne Pasta 800 gr

BAKE-Penne Pasta 800 gr

22

FOOD

-White Baguettes x 3

BAKE-White Baguettes x 3

23

FOOD

BAKE-Spaghetti 500gr

24

Bakeries & Grains Total

BAKERIES & GRAINS

Sheet4

Cell Formulas
Range	Formula
H13	=IF(LEFT(G13)="-",LEFT(INDEX(G13:G$24,MATCH("total",F13:F$24,0)),4),"")&G13

Panoos64 · Oct 15, 2019

Hi steve & Fluff, both of your formulas work and appear the expected result. Thank you both for your support and your time spent for my project. Hv a great day!

Fluff · Oct 15, 2019

Glad we could help & thanks for the feedback

mikerickson · Oct 15, 2019

I think this VBA will do what you want.

Code:

Sub test()
    Dim StartLine As Long, EndLIne As Long
    Dim i As Long, j As Long, prefix As String
    StartLine = 1
    With Range("F:F")
        For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
            If LCase(.Cells(i, 1).Value) Like "* total" Then
                GoSub ProcessBatch
            End If
            EndLIne = i
        Next i
    End With
Exit Sub
ProcessBatch:
    With Range("F:F")
        prefix = UCase(Left(.Cells(i, 1).Value, 4))
        For j = StartLine To EndLIne
            If .Cells(j, 2).Value Like "-*" Then
                .Cells(j, 3).Value = prefix & .Cells(j, 2).Value
            ElseIf Not (.Cells(j, 2).Value Like prefix & "-*") Then
                .Cells(j, 3).Value = prefix & "-" & .Cells(j, 2).Value
            End If
        Next j
    End With
    StartLine = i + 1
    Return
End Sub

Panoos64 · Oct 15, 2019

Thank you mike! Your code works based on my above extract but in addition i would like to express my apologies. I done mistakes in my above extract and therefore i prepare it by manual method, instead to copying some data from my actual data. Therefore that, in col. "F" is "Category" and not the name of category Total. Furthermore in col. "G" is "Sub Category Items". Please see below a part of my actual data. I would much appreciate if you could modify the code and i express once again my apologies for such fault. Thank you once again. Have a lovely day

[TABLE="class: MsoNormalTable, width: 385"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 199"]

F

[/TD]
[TD="width: 251"]

G

[/TD]
[/TR]
[TR]
[TD="width: 64"]

13

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -Royal Gala Apples x 6
[/TD]
[/TR]
[TR]
[TD="width: 64"]

14

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -Kiwi Fruit x 6
[/TD]
[/TR]
[TR]
[TD="width: 64"]

15

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -Red Grapes 600gr
[/TD]
[/TR]
[TR]
[TD="width: 64"]

16

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] FRUI-Oranges x 5
[/TD]
[/TR]
[TR]
[TD="width: 64"]

17

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] FRUI-Raspberries 100gr
[/TD]
[/TR]
[TR]
[TD="width: 64"]

18

[/TD]
[TD="width: 199"] Category

[/TD]
[TD="width: 251"] Sub Category Items
[/TD]
[/TR]
[TR]
[TD="width: 64"]

19

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -Sliced Wholemeal 1000gr
[/TD]
[/TR]
[TR]
[TD="width: 64"]

20

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -White Rolls x 24
[/TD]
[/TR]
[TR]
[TD="width: 64"]

21

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -Penne Pasta 800 gr
[/TD]
[/TR]
[TR]
[TD="width: 64"]

22

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] -White Baguettes x 3
[/TD]
[/TR]
[TR]
[TD="width: 64"]

23

[/TD]
[TD="width: 199"] FOOD
[/TD]
[TD="width: 251"] BAKE-Spaghetti 500gr
[/TD]
[/TR]
[TR]
[TD="width: 64"]

24

[/TD]
[TD="width: 199"] Category

[/TD]
[TD="width: 251"] Sub Category Items

[/TD]
[/TR]
</tbody>[/TABLE]

Panoos64 · Oct 15, 2019

Fluff & steve i express my apologies to both of you too. Your formulas works but is the same as i wrote to mike. However is my fault and i would be thankful if could change the formulas and advice me how to achieve so that to have the expected result. Apologies once again

Place sub category's letters in all items

Panoos64

Well-known Member

Excel Facts

steve the fish

Well-known Member

Panoos64

Well-known Member

steve the fish

Well-known Member

Fluff

MrExcel MVP, Moderator

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

mikerickson

MrExcel MVP

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Place sub category's letters in all items

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock