Excel help

[Captain Mike]

Need to be able to do a lookup or something on excel. Unfortunately I spent 90% of my time in IT lessons playing games so did not take in much of the excel "training".

I have a spreadsheet and it has addresses on it in one column. some of the addresses have a comma in them with the second part of the address after the comma.

I need to be able to move anything to the right of the comma in to a new column. (to be labelled address line 2)

I didn't get much further than it will involve ,*

any help greatly appreciated

 

[herpsuk]

When i did a data search for lots of companies i copied data from yell etc. This ment spaces, commas and so on were where i didnt want them. Did a bit of researched and faved these....

http://www.business-spreadsheets.com/forum.asp?t=14

http://office.microsoft.com/en-us/excel/HP052510211033.aspx

http://www.automateexcel.com/2004/08/15/excel_formula_remove_blank_spaces_from_t/

 

[Captain Mike]

When i did a data search for lots of companies i copied data from yell etc. This ment spaces, commas and so on were where i didnt want them. Did a bit of researched and faved these....

http://www.business-spreadsheets.com/forum.asp?t=14

http://office.microsoft.com/en-us/excel/HP052510211033.aspx

http://www.automateexcel.com/2004/08/15/excel_formula_remove_blank_spaces_from_t/

you the man

 

[herpsuk]

you the man

:spin: :slayer:

 

[Eklipze3k]

Without even looking at the links I would favour the 'Text to columns' approach

 

[mehstg]

Without even looking at the links I would favour the 'Text to columns' approach

This is eerie. i had to do this yesterday at work for the first time ever, doing some data cleansing for our CRM and i had to google it.

Used the text to columns approach and it worked a treat

 

[Eklipze3k]

I miss Excel

 

[ChrisUK]

I miss Excel

Are you some sort of perv? .

 

[herpsuk]

so the helper needs some help now.

Ive got a spreadsheet that is basicly an end of year accounts sheet for balance, p+l, capital etc etc.

Ive got 2 different issues. Related to the same section I have three Cells in all that are all Totals of numbers in the sheet.

Cell1 - This could be a negative or a positive number
Cell2 - This could be a negative or a positive number
Cell3 - This could be a negative or a positive number

Cell3 is the Total of Cell1 Less Cell2 e.g.

Cell1 = '-3902'
Cell2 = '3902'
Now the total shown in Cell 3 should equal '0' But if i do it 'Cell1 - cell2' the total is 7804 but it should actually be 0. I can fix this by doing 'cell1 + cell2'.

This is a fix until Cell1 becomes a positive and cell2 becomes a negative. I can fix both these issues by using '=ABS(cell1)-ABS(cell2)'. But this doesnt fix the issue i have when both numbers are negative.

Right i know this sounds very very confusing, if you want me to explain more please say.

Anyone got any advice or knowledge to share as im pulling my hair out?

regards

Jonathan

 

[herpsuk]

anyone?

 

[Stu DemonD]

What are the definitions for cells 1 to 3? I reckon there is some confusion.

 

[Eklipze3k]

I'm confused, but mostly because Excel is doing what it should do (sort of). If you start with -3,902 and deduct 3,902 then you will end up with -7,804 not 0

-3,902 minus -3,902 would equal 0

 

[herpsuk]

Sorry about the late reply, had a busy time last few days.

Attached is an example of what im trying to do. The Capital accounts are as they should be. without any formulas being used.

Closing Calculation is '= Cell1 Less(-) Cell2'. This doesnt calculate how i want it to.

Closing Calculation (ABS) is =ABS(Cell1) Less(-) ABS(Cell2)'. This works on some of the calculations but not others.

Anyways ill let you have a look and see what you think.

 

[hmallett]

I'm not sure if I'm looking at this too simply, but:

The first section is what each partner has received in assets (e.g. directors loan account and profit share).
The second section is the payments that have been made, which you are going to subtract. HOWEVER, you've entered them as negative figures. So when you go to subtract them at the end, you're subtracting a negative number.

I don't know whether from an accountancy point of view it's better to enter the figures as negative, then add at the end, or to enter the "less" figures as positive then subtract them. Either way, you need to do one of them!

Also, poor partner 1, having to pay their way out of the business (if I've read the figures right).

 

[herpsuk]

Basicly it is a copy of some accounts tweaked for you lot. So everything there is how it should be apart from one number or so being changed. This was done by an accountant. So as far as im aware its correct.

Im just lost on how to calculate this automatically.

 

[hmallett]

This was done by an accountant. So as far as im aware its correct.

Accountants aren't always right

Cell1 = '-3902'
Cell2 = '3902'
Now the total shown in Cell 3 should equal '0' But if i do it 'Cell1 - cell2' the total is 7804 but it should actually be 0. I can fix this by doing 'cell1 + cell2'.

-3902 - 3902 is always -7804.
If you're not getting the figures you expect, either the formula is wrong, or the figures you're entering are wrong.
If the spreadsheet has been set up for you and you don't want to alter the formulae, I'd say that the figures you've entered in the "less" section should be positive values. In fact, now I think about it I think that's how it's normally done in company accounts. This will give you the correct figures at the end.
If you were an accountant, and put a "less" section, you wouldn't expect to put -ve numbers in unless it was actually "more".

 

[herpsuk]

Yeh i understand. No the sheet wasnt already done, ive done this from scratch and have alot more to go with that sheet.

Think ill just let it go on as it is and try reading a bit deeper. Or ill speak to the accountant tomorrow when he rings.

Thanks alot

 

[Captain Mike]

Right new issue...

We have a system where users fill out a survey and all their answers get tracked and sent back to the server. We can then export the results from the server for analysis.

At the moment the format the results come out in is crap! So I need to be able to make a merge type thing so the user can export the data press a button or select a file and it transfers all the data from the crap results to a nice lovely excel spreadsheet I have made.

Basically my nice lovely spreadsheet needs to be able to drag the data from certain columns on the crap spreadsheet to corresponding columns on the nice spread sheet.

Any links/help/advice greatly appreciated.

 

[adamcot]

Do the results come out in an Excel readable file? If so, maybe you can create a macro to get the spreadsheet to how you want it. We used to have this problem when sales data was exported in a bad format, but a click of the macro and it was all tidied up

 

[Captain Mike]

Do the results come out in an Excel readable file? If so, maybe you can create a macro to get the spreadsheet to how you want it. We used to have this problem when sales data was exported in a bad format, but a click of the macro and it was all tidied up

Yeah they come out in excel format anyway.