Can this be done? #2

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

--
Message posted from http://www.ExcelForum.com

0
4/28/2004 3:22:49 PM
excel 39879 articles. 2 followers. Follow

4 Replies
378 Views

Similar Articles

[PageSpeed] 55

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 ***
0
james9028 (6)
4/28/2004 4:41:02 PM
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

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"nambi >" <<nambi.15fifz@excelforum-nospam.com> wrote in message
news:nambi.15fifz@excelforum-nospam.com...
> i have a list of about 10000 products that I'm trying to make a report
> on, They are in excel and can easily be cut and pasted to a new
> location, I am using crystal reports for the report and the report
> requires me to take these products and list them in a way that the
> 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 tedious
> job that can have a large margin of error.  Any suggestions would be
> greatly appreciated.
>
> Thanks
>
>
>
> I have the formula but i need to put these products in a specific
> format, ei "product1","product2","product3","product4" basically i
> presently have them as product1 product2, is there a way I can
> automatically get the "","","", in?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
4/28/2004 5:33:58 PM
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

--
Message posted from http://www.ExcelForum.com

0
4/28/2004 6:14:33 PM
column or row would make no difference.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"nambi >" <<nambi.15fqe7@excelforum-nospam.com> wrote in message
news:nambi.15fqe7@excelforum-nospam.com...
> Thank you I see that this is starting to work, the problem is i used the
> wrong terminology when explaining my situation, I have my data listed
> in a colum so it is a1a2a3 etc.
> trying ot figure out how to move the data into a column instead and
> then I will try out your formula.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
4/28/2004 7:53:15 PM
Reply:

Similar Artilces:

save as pipe delimited #2
hi, just an appendage: i found another link, which provides a file by Earl which does this. http://www.excelforum.com/showthread.php?t=265998&highlight=pipe+delimited i am not able to figure out where to input the filename for the data t be picked up from. any information regarding this would be helpful. thanks, mac -- icestationzbr ----------------------------------------------------------------------- icestationzbra's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=458 View this thread: http://www.excelforum.com/showthread.php?threadid=26877 ...

5.2.1 NDR
I have an issue with users unable to send messages to mail-enabled public folders in the same organization. Email can be sent to the public folder from outside. I found this is a pretty common issue but nothing helped resolve my issue. It appears to be fixed in SP2 however I am still having problems. I looked at the KB articles listed below and verified the Anonymous user has the appropriate rights. I see there was a hotfix in Exchange 2003 RTM and SP1. I also looked at all the AD permissions and the Exchange Enterprise Server group has permissions that was mentioned in various...

Can I fix the exponent in scientific/engineering format?
This is sort of a continuation of my search to find a ppm number forma (http://www.excelforum.com/showthread.php?t=368806). Found someone who mentioned using engineering formats (kind of a extension of scientific format). After a little tinkering, here's wha I've found (using the values of 7E-3 and 7=-5 as examples): format 7e-3 displayed 7e-5 displayed .0E+0 .7E-2 .7E-4 0.0E+0 7.0E-3 7.0E-5 (REGULAR SCIENTIFIC NOTATION) #0.0E+0 70.0E-4 70.0E-6 (EXPONENTS ARE MULTIPLES OF 2) ##0.0E+0 7.0E-3 70.0E-6 (EXPONENTS ARE...

How can i asign MBF roles to user accounts?
Hello. I want to asign to user accounts the next roles: MBF roles: - Order Management Customer - Customer (Advanced role) I was wondering how can I make this Thanks for your help Jose Ponce Aguilar ...

Can i use Windows Search to find a number within Excel?
If i use Widnows search of the CONTENTS of files (not file names), i find in Excel, that for cells that contain text, they can be found. But, it does not appear to me that numbers can be found. Of course the number as Excel stores it may differ from how it is displayed. But, does anybody know if this is possible? For instance, i have a cell with the number 3,709,227 in it, as it appears in Excel (as formatted). I tried typing the number both with and without the commas, into WIndows search, but Windows search still does not find the Excel file that contains that number. So, any kn...

Paying a lot of money for a formal dress that you will only wear once can be frustrating. Used clothing stores can offer an extensive selection of formal wear at affordable prices that you or your d
Paying a lot of money for a formal dress that you will only wear once can be frustrating. Used clothing stores can offer an extensive selection of formal wear at affordable prices that you or your daughter will love. http://www.shoesbootjeans.com http://www.shoesbootjeans.com/Replica_Mens%20Shoes_1.html http://www.shoesbootjeans.com/Replica_Womens%20Shoes_1.html http://www.shoesbootjeans.com/Replica_Boots_1.html http://www.shoesbootjeans.com/Replica_Boots_1.html http://www.shoesbootjeans.com/newarrivals.html http://www.shoesbootjeans.com/hotproducts.html Used clothing stores are full of ite...

Newbie q: Can't modify a database
Hi, I've been handed an access database in order to modify it (in the programming sense), but when i open it it opens as an application, without any apparent means of modifying the database structure. All it will let me do is add or edit data. Is there a secret trick to opening the database for modification, or do I have to go to the owners of the database and have them save it in a different way? Thanks, Jon Hi Jon If you hold down the shift key while the database is opening it should bypass any startup options code and take you straight to the database window. If this doesn'...

Can I have two Internet Mail Services in one Exchange 5.5 Organiza
Hello, My current environment consists of 6 mailbox servers and one server hosting the internet mail service (Exchange 5.5 on Windows 2000). I need to use one of the 6 mailbox servers for a product that will need to route outbound mail to another server for other secure outbound services while the other 5 servers route to the current internet mail service. Is this possible? Can I add a secondary internet mail service just for this one server? I am not able to find any knowledge base articles for assistance. Thanks in advance! On Tue, 28 Jun 2005 15:36:10 -0700, "exadm" &l...

importing statements #2
I'm getting an error when trying to import my bank statement. It reads "A transaction in the QIF file has a date that could not be resolved and therefore was not recorded into your Money file. Check your Regional Settings in the Windows Control Panel to make sure they match the date format of the QIF file, and then try importing the file again." Please help! Thanks ...

how can i graph with log-log scales? #2
I wish to graph pricing curves using a logarithmic x-axis. Is this possible with Excel? If so, how do I do it? AirdromeTom - > I wish to graph pricing curves using a logarithmic x-axis. Is this > possible with Excel? If so, how do I do it? < Use an XY (Scatter) chart type. - Mike www.mikemiddleton.com ...

How can i get more 3D shapes for Auto shapes in excel?
Please suggest how i can get more shapes (simple as well as 3D ) in Auto Shapes of drawing toolbar in excel & word? ...

sorting by date #2
I am having trouble sorting a table by date. When I choose to sort th column by date - it sorts it alphabetically by date - i.e. with Augus coming first. How can I have Excel sort chronologically, when the date include month, day, yea -- Message posted from http://www.ExcelForum.com It sounds as if the dates are formatted as text. You can change this to an Excel date value by inserting a helper column and using the following formula (assuming your dates are in column A: =DATEVALUE(A1) and copying down. Excel will then recognize the values as date values, not text, and will sort chronol...

Can we Take layaway payments from store #2
Hello, I have a customer that would like to make a payment on his layaway at out other location. Can this be done? and how? -- Reynold Cycle This is a multi-part message in MIME format. ------=_NextPart_000_04C2_01C70A46.94719A50 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Jose, Not without an add-on, which I've never seen to do this. --=20 Jeff=20 Check Point Software =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=...

Business Portal Install Error #2
I am installing BP 3 on a GP 9 server which is running Win2k3. SQL 2K is on same server with an instance name (Not a Default server). When I try to install BP I receive a error. Declaring Type : Microsoft.BusinessSolutions.PCITeam.Install.BP.Packager.Packager. Target Site : LoadPackagerComponents Error Summary : Failed Loading Packager Data Element (PD_GP_COMMON) for Install Feature (BusinessPortal). and it was hanged with the following message Please wait while the setup wizard installs Microsoft Dynamics GP Business Portal. This may take several minutes. Status Loading data 1 of 21 T...

How can I sort and maintain formatting?
I have a very large excel doc (well over 1,000 rows, by 30 somethin columns) that require formatting (resizing the row height) each row b hand. I have done all of that tideous work, but now I need to sort b multiple columns, and when I do that, the formatting of the cells i not maintained. (The size of the row stays, rather than moves with th data) Please tell me there is a remedy and I haven't just wasted m time formatting for nothing! Thanks for any and all advice -- littlelostgr ----------------------------------------------------------------------- littlelostgrl's Profile: http:/...

can't save files in subfolders using Word #2
I'm running Word on a Mac running OS X. When I go to save a file in Word, it only gives me the option to save in the root files like "Documents" or "Applications." How do I save the file into a subfolder? On a PC, I would double click to open the folders until I got to the folder I needed. That doesn't work here. Can someone help? I'm sure this is something obvious I'm missing. Thanks - Taylor In article <1163740041.258317.226140@b28g2000cwb.googlegroups.com>, taylorgriffin@gmail.com wrote: > I'm sure this is something obvious I'...

can i not allow duplicate values in the same column?
is there a way to set columns to not accept duplicate information? Take a look at http://cpearson.com/excel/NoDupEntry.htm In article <4765FB37-8F89-4220-A901-B289FDCDE23F@microsoft.com>, excel newbie <excel newbie@discussions.microsoft.com> wrote: > is there a way to set columns to not accept duplicate information? If the values are typed in you can use this: http://www.cpearson.com/excel/NoDupEntry.htm Regards, Peo Sjoblom "excel newbie" wrote: > is there a way to set columns to not accept duplicate information? ...

toolbar macros #2
I've gone through the routine to assign a macro to a toolbar button, even down to getting the option to select the macro, but it isn't permanent. When I click on the button afterwards,I get the same dialog to assign the macro. However, I can permanently assign the same macro to a graphic in the sheet. Second quandary - Can I assign that same macro to a cell which has only text? ...

Conditional Formatting in Excel 2007 #2
Hi guys, I have been asked to prepare a spreadsheet that uses conditional formatting on another cell. What it needs to do is that is a cell contains a particular value i.e. between 2 and 6 the adjacent cell will turn say blue, but the issue is that there is up to 6 different rules that could be applied to that one cell. All of these rules then need to work for the subsequent entries. If anyone could help me with this issue I would be very grateful. Many Thanks Didi On Tue, 19 Oct 2010 03:10:07 -0700 (PDT), didi <diane.bush@hotmail.co.uk> wrote: >Hi guys, > >I have been a...

Excel 2002 error message #2
i am getting the following message in Excel 2002 "an outgoing call cannot be made since the application is dispatching an input-sychronous call". this appears when copying some cells in the spreadsheet. Any ideas? ...

Protecting hidden columns so they can't be unhidden ?
Hi, Is there a way to hide columns so that no one else can unhide them ? Need to send a file with data in 2 hidden columns that I don't want to be unhidden and I don't want to have to delete them. Thanks ! Gary, easiest way is to hide the columns then protect the worksheet (Tools > Protection > Protect Sheet). If there are cells that the recipients will use for data entry, unlock them (Format > Cells > Protection > Locked) first. You can also remove the row and column headers (Tools > Options > View > Row & column headers) so it won't be immediate...

How do I compare 2 worksheets, 1 old, 1 updated to find difference
I have 2 large worksheets of text information, the 2nd being an update of the first, I need to identify/highlight the updated fields. Is there a way to do this? There is a workbook compare add-in by Myrna Larsen and Bill Manville on Chip Pearson's site you might try: http://www.cpearson.com/Zips/Compare.ZIP -- Jim Rech Excel MVP "alienstew" <alienstew@discussions.microsoft.com> wrote in message news:A1CD24D8-93AB-47CA-AAF0-708A4D3EA14C@microsoft.com... |I have 2 large worksheets of text information, the 2nd being an update of the | first, I need to identify/highlig...

Can't install Windows Updates on Vista
I'm running Windows Vista Home Premium with Service Pack 2. I have 15 "Important" Windows Updates that won't install: 9 security updates, 3 windows updates for Vista, etc. One dates back to 12/8/09 and the most recent 4/13/10. They're starting to add up. When I try to install them, some will fail due to a 8007371C error and the rest will say pending (until restart), but then after the restart they fail to install (but no error code). I've tried installing them with UAC, anti-virus, and firewall off, but the result is the same. When I try to install any of them ...

2 Exchange Servers in the Same Domain
Microsoft Outlook SignatureHi, Please help as I'm new to Exchange.... I have 2 Windows 2003 Servers both with Exchange 2003 installed in the same domain, @ two different physical locations connected via a gateway to gateway VPN. If I have user on Server A, he can create a email in the gobal address list and send it without error to a user on Server B but it never arrives @ the server B's inbox? Is there a way to track it? K.J.B. You can enable message tracking... http://www.msexchange.org/tutorials/The_Exchange_Message_Tracking_Center_or_How_to_Save_Your_A_in_a_Pinch.html CA...

I can not delete a duplicate personal folders list
Vall... amazing that there isn't an easy fix for this. The instructions provided simply don't work ( tools-email accounts-view change email accounts-deliver to new account-finish-close outlook and reopen ), and none of the other offered explanations have worked either. As this is such a common problem; why hasn't microsoft provided a very EASY fix? I am more confused ( and frightened ) and don't want to make the problem worse! ...