Advanced excel

Hi,

There are many ways to do this. I found a quick solution that works for the example file you gave here.
Hardly 3-4 steps and you are done.

Hi

Very simple. Just type this formula in cell A2  :  =LEFT(B2,FIND(" ",B2&" ")-1)  and drag it till end. Done! As mentioned by Mehul, there are lot of ways to do it.

Hi, Thanks mehul and Ishav. I think i have given too simple format. Let me share the actual data only so that you can understand the question better. The formula you shared is not possible to apply for my issue. You will be able to understand why once you will see the file.

I believe and hope you will be able to crack it down. Thank You Guys in advance. Even if you can't together we can scratch our brains and crack it down.

Hi,
Just saw your reply, here's is my other solution its a macro.

Select your data click on the button i have create, and it will print the data in next 2 columns.

then just delete some unwanted rows which have "delete by sorting" written in them.

Macro Magic always works :)

only one thing i can say is wooooooooowwwwwww!!! awesome mate. Frankly i have zero knowledge of macro. I Guess you used power of visual basic. 

Can you explain what logic you used?? Thanks a TON bro. It will help tremendously.

Hi,
Am glad it worked. Yes I have used macros/VBA.

You can goto Developer tab > Macros > You will see something called itemnameloop, so just click on it and select edit. That way you can see the code.

The logic I used is as follows:
 I observed your item names have 5 digits generally towards end, so if digits are found at end I copy that to right side, if digits not found then its presumed item grp and keep repeating it till new item grp/head is found.

Mr.Kaushik, I think may I know A simple ways(without macros) in this above subj., I explain step by step..

>Select all and press ctrl + alt + L (already filter option is applied in your sheet)

>select filter by colour(in filter option) _ eg. select Red colour (head)

>Insert 1 entire column

>select the head and press ctrl + R (copy to new column)

> Remove your filter and select all > copy and past in value format then enjoy

I have nothing against macros personally. But I find using macros for such a simple problem seems like a overkill. I have attached the solved file here for everybody's reference. This does not even require any formulas other than an equal formula. Hint I use the Goto dialog box for solving this

Anyone interested in my method of doing this, please feel free to ask.

I am attaching the partially solved file. Anyone can make out the rest.

Good to see so many solutions.
Since excel is an open tool any method that gives the correct end result is fine I guess.

Lesser the no. of steps the better it is.
I would have even suggested something like Power Query, but its awareness is a problem.