# dissimilation of data from a column to multiple column

#### quillmaster

##### New Member
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

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

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
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
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
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...

Really grateful
Mohit

#### quillmaster

##### New Member

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

http://sdrv.ms/Y7mHkk

Mohit

#### raj2206

##### Board Regular
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...

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
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
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

Replies
1
Views
158
Replies
4
Views
349
Replies
1
Views
135
Replies
1
Views
105
Replies
3
Views
75

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.

### Which adblocker are you using?

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

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