Populate a column with Categories

[leimst]

Good Morning All,

In the attached below, I would like to make the table on the left look like the table on the right by having a formula that looks in Column A and finds "Hershey's Total", extracts "Hershey's" and places it in C2-C5 leaving C6 blank. Then the formula would extract "Nestle" from "Nestle Total" in A11 and insert it into C7 - C10 leaving C11 blank. Lastly, the same thing with "Mars".

Thank you in advance for any help!

leimst

 

[bosco_yip]

Try,

In C2, formula copied down :

=IF(A2="",SUBSTITUTE(INDEX(A2:A$14,MATCH(1,INDEX(0+(A2:A$14<>""),0),0))," Total",""),"")

Regards
Bosco

 

[leimst]

Thank you Bosco, that worked perfectly...brilliant!

leimst

 

[rahulshewale1]

hiii @leimst ,

try below formula .

Cell C2
=SUBSTITUTE(IF(A2="",INDEX(A2:$A$14,AGGREGATE(15,6,ROW(A2:$A$14)-ROW(A2)+1/(A2:$A$14<>""),1)),""),"Total","")



Regard
Rahul shewale

 

[bosco_yip]

Here's one more shorter :

=IF(A2="",SUBSTITUTE(VLOOKUP("*",A2:A$14,1,0)," Total",""),"")

Regards
Bosco