Can this be done?

[nambi]

i have a list of about 10000 products that I'm trying to make a repor
on, They are in excel and can easily be cut and pasted to a ne
location, I am using crystal reports for the report and the repor
requires me to take these products and list them in a way that th
formula can work.


as of now the products are listed in excel in a single row such as

product1
product2
product3
etc etc

for my formula to work i need it listed EXACTLY as is

"product1","product2","product3" etc

Is there any way I can automate this process, this is a very tediou
job that can have a large margin of error. Any suggestions would b
greatly appreciated.

Thanks



I have the formula but i need to put these products in a specifi
format, ei "product1","product2","product3","product4" basically
presently have them as product1 product2, is there a way I ca
automatically get the "","","", in

 

[James]

You may have a fundamental problem with formula length since you have so
many products, so you might need to break the job up into several
sections. But the following is a quick and dirty method that might
help.

If your data is in row 1, starting from B1 (note it is important that
column A is blank for this implementation to work), then put this
formula in B2:

= A2 & ",""" & B1 & """"

Then you can fill right. In the right most column, you will have the
entire list. Note that there will a leading comma, to get rid of this,
just copy, paste as values and delete the comma at the beginning, or use
an if statement so that you don't have a comma in the first place:

= IF( A2="" , """" & B1 & """", A2 & ",""" & B1 & """" )

-james

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Don Guillett]

This should give you the idea. works on a typed list such as
prid1
prod2
prod3
then you can incorporate the string into a make a formula macro

Sub makeformula()
For Each c In Selection
mystring = mystring & c & ","
Next
MsgBox mystring
End Sub

 

[nambi]

Thank you I see that this is starting to work, the problem is i used th
wrong terminology when explaining my situation, I have my data liste
in a colum so it is a1a2a3 etc.
trying ot figure out how to move the data into a column instead an
then I will try out your formula

 

[Don Guillett]

column or row would make no difference.