dissimilation of data from a column to multiple column

[quillmaster]

Hi,
I apologize for the vague subject as i was unsure about what to do, i have lots and lots of data in single column, which is as follow
Menu item Name
menu item price
menu description

all three variable are in the single column and one above each other and i have a very long column about 5000 rows, also it is not consistent as well, sometimes there is menu description and sometimes it is not, so we cannot predict the place of a variable in the column as well ( like every fourth entry should be item name, but it is not ), now i want to sort this data into three columns
Menu item name menu item price menu item description

i tried to attach the excel here but didn't work, hence attaching my skydrive link... please check here.

http://sdrv.ms/ZKUfIY

can i anyone help me.....
Mohit

 

[erik.van.geit]

Hi, Mohit,
WELCOME to MrExcel!

The sample is not a single column as you stated in your first sentence.
It would be good if you showed us a list with about 20 rows + the expected result.
You can simply copy and paste from your excel sheet. There are also tools to do that, which you can discover by browsing the forum.

kind regards,
Erik

 

[raj2206]

Hi,

Thanks for writing, have copied and pasted your data in "A1", for prices you can use the formula in D2 (Column= HalfPrice") and drag it along:

=IF(ISTEXT(C2),(IF(ISNUMBER(INDEX(C2:$C$82,SMALL(IF(--ISNUMBER(C2:$C$82)=0,100000,--ISNUMBER(C2:$C$82))*(ROW(C2:$C$82)-ROW(C2)+1),1))),INDEX(C2:$C$82,SMALL(IF(--ISNUMBER(C2:$C$82)=0,100000,--ISNUMBER(C2:$C$82))*(ROW(C2:$C$82)-ROW(C2)+1),1)),"")),"")

Please change the row references (in my case C2:C82) to the extent of the ending row.

I think this will help for prices, please give your feedback so that we can also work to extract the descriptions.

Also make sure that you use Control+Shift+Enter while entering the formula, since this is an array operation.

Thanks/ Raj

 

[quillmaster]

Hi,

Thanks for writing, have copied and pasted your data in "A1", for prices you can use the formula in D2 (Column= HalfPrice") and drag it along:

=IF(ISTEXT(C2),(IF(ISNUMBER(INDEX(C2:$C$82,SMALL(IF(--ISNUMBER(C2:$C$82)=0,100000,--ISNUMBER(C2:$C$82))*(ROW(C2:$C$82)-ROW(C2)+1),1))),INDEX(C2:$C$82,SMALL(IF(--ISNUMBER(C2:$C$82)=0,100000,--ISNUMBER(C2:$C$82))*(ROW(C2:$C$82)-ROW(C2)+1),1)),"")),"")

Please change the row references (in my case C2:C82) to the extent of the ending row.

I think this will help for prices, please give your feedback so that we can also work to extract the descriptions.

Also make sure that you use Control+Shift+Enter while entering the formula, since this is an array operation.

Thanks/ Raj

You are awesome Raj..... it almost fixed my problem..... when i use this it fetches the price of the dish in front of it but it leaves me with two problems
1. it still leaves the number in column C ( do i have to manually delete the numbers ?)
2. it cannot distinguish between the description and dish name ( so when i apply the formula it basically gives me the number in front of every text column...

can we do something about this...

Really grateful
Mohit

 

[quillmaster]

Raj,
this is the result that i got after apply your soultion...

http://sdrv.ms/Y7mHkk

Mohit

 

[raj2206]

You are awesome Raj..... it almost fixed my problem..... when i use this it fetches the price of the dish in front of it but it leaves me with two problems
1. it still leaves the number in column C ( do i have to manually delete the numbers ?)
2. it cannot distinguish between the description and dish name ( so when i apply the formula it basically gives me the number in front of every text column...

can we do something about this...

Really grateful
Mohit

Thanks, I appreciate your feedback. For column C although you have to delete manually but its fast and easy, apply filter in the data and click the filter on Column C and filter all the numbers and press delete and remove filters. You need a certain delimiter for descriptions and dish name there, will try to work over it again and get back.


Thanks/Raj

 

[raj2206]

Hi,

Do you want any changes in the result?
For "Half Price" just Divide the price by 2 in D, and For "Full Price" E just multiply D by 2 in E.

Thanks/Raj

 

[quillmaster]

actually i was thinking of not having to do any manual work as this is just sample there are approx. 5 million dish items for which i have to do this operation.... i think i have to write a VBA code..... my formula side is not that strong.....
Anyways thanks for your help and time.... you have certainly pointed me a right direction.... let me see if i can find some sort of concatenation to fix this...

Mohit