dissimilation of data from a column to multiple column

quillmaster

New Member
Joined
Feb 28, 2013
Messages
8
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Jul 23, 2012
Messages
213
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

New Member
Joined
Feb 28, 2013
Messages
8
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
 

raj2206

Board Regular
Joined
Jul 23, 2012
Messages
213
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

Board Regular
Joined
Jul 23, 2012
Messages
213
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

New Member
Joined
Feb 28, 2013
Messages
8
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
 

Forum statistics

Threads
1,148,181
Messages
5,745,204
Members
423,933
Latest member
ankushmukherjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top