Excel Question...

pclancy

Sory for stupid question but I have a 28000 line excel spreadsheet of spare parts which im trying to edit to be loaded into Sage. All I want to do is insert the manufacturer name into each description cell without disturbing whats in there...how the feck do i do it?

Ashamed at myself for asking as I used to teach the ECDL :rolleyes:

Thomas_S_Hunterson

Would a new column be in order?

pclancy

No must keep columns in the exact way sage expects them. Thought of creating a column beside and merging but excel only keeps the data from the left colum.

MickyJoe

How about copying existing column data to a new column, add another column to enter description. Original column is now a CONCATENATE function [e.g =CONCATENATE(A1," ", B1)] and replicate this througout the other columns ?

bbbbb

MickyJoe wrote:

How about copying existing column data to a new column, add another column to enter description. Original column is now a CONCATENATE function [e.g =CONCATENATE(A1," ", B1)] and replicate this througout the other columns ?

Yes, to expand on this I would do:
- say Col A has the original data
- add the manufacturer to Col B
- Col C has the concatenate function above.
- then I would copy Col C, do a "paste special" and paste "values" into col D.
- Then delete col A, B, C, your left with a single Col A with "Part Manufacturer"
(obviously save your original data and work with a copy before deleting anything)

iMrk

bbbbb wrote:

Yes, to expand on this I would do:
- say Col A has the original data
- add the manufacturer to Col B
- Col C has the concatenate function above.
- then I would copy Col C, do a "paste special" and paste "values" into col D.
- Then delete col A, B, C, your left with a single Col A with "Part Manufacturer"
(obviously save your original data and work with a copy before deleting anything)

Original OP..... The "Help" function in MS products is your friend. And the guys above are correct.

Had you have typed in "Join data" you would have more or less been led to the "concatenate" function. Google is also good

Basicaly (And I use this a lot as some one who has a data base of 48,000 parts lines...)

Col A = Product description.
Make new col B with manufacturer. e.g Delco.

So lets say A1 is "Windscreen Fluid"
B1 = "Delco"

Go to C1...

Go to the formulae wizard.....Look for the text ones and choose Concatenate.
(I say go to the formulae wizard so you can see how much excel can do...pay no attention to these people who say use Access!)

A pop up box appears when you choose your formula type.

Click in the "Text1" box then click in the "A1" cell. your product name.

Click in the "Text2" box and hit the space bar (you are adding a space)

Click in the "Text3" box and then in "B1" The manufacturer.

Click ok.

What you should see in C1 is "Windscreen fluid Delco"

Now to kill of the fact that it is a formuale you need to copy the Col "C", Choose "paste special" and then "Values"

Happy? Delete the un needed original description col and Sage is happy...

Job done.

hope that helps.

pclancy

Thanks for the replies, tried that formula but cant get the results to appear? Paste special, choose values and still the formula appears in col d?

subway

pclancy wrote:

Thanks for the replies, tried that formula but cant get the results to appear? Paste special, choose values and still the formula appears in col d?

try it again,
paste special > values is the function you need,
if it didnt work, you did it wrong

iMrk

subway wrote:

try it again,
paste special > values is the function you need,
if it didnt work, you did it wrong

It's possible he is doing it right.

OP in the Formula col is it showing the forumlae rather than the result?


E.G =CONCATENATE(A1," ", B1)

If so I have come across this before. The problem is that the sheet is all some sort of text format. I think it is possible that the data sheet you are using was generated by an inhouse program? Possibly a .csv or a .xls from a DDE. (Dynamic Data Exchange or my own version before I was corrected Direct Data Export. I think mines better)

Or you have it all in text format for Sage?
Either way...

Your best and simplest bet is to open a new fresh [/b]work book and in put the formulae in that. Then copy and paste from that work book into the old one.

Or you can mess about with the col's in the work book you are using and set the format to general or into number and then into general.

I get this problem alot form files I recieve from Korea but I "mess about" to fix it. Some times using a col like AB works. Some times it is just easier to have the formulae in a new work book and copy the "Values" back.

Just remember that you should see the actual result in the Formulae cell. Not the Formulae.

Obviously that assuming that the above is your problem!

HTH.

And if you think thats bad wait till you come across the invisable ' at the start of data.... on that note click on one of the cells you have your original data in and view the cell contents in the Formulae bar/viewer at the top. These is no little ' at the start is there? cos then it's a complicated to exlpain fix.