Combine variable number of columns

I have a single spreadsheet with a list of clients, addresses and their 
product interests. This table will be used to drive a Mailmerge document. In 
the document, I want to be able to refer to the products in which they 
indicated an interest. The problem is that while one client may have 
identified only one product (one column) others have selected anywhere from 
two to 170 products - each product is in a separate column. I can join two 
columns with "&", but when I have an inconsistent number of columns how do I 
do this efficiently? - I will have to do it for each client, each month, so 
am not interested in manually typing all 171 combinations. 


0
mack (2)
4/26/2007 8:37:26 PM
excel 39879 articles. 2 followers. Follow

1 Replies
885 Views

Similar Articles

[PageSpeed] 47

Maybe you can use an extra column which is a count of the number of
items in the columns covering up to 170, eg:

=COUNT(G2:FT2), or
=COUNTA(G2:FT2)

if your product columns start with G.

Hope this helps.

Pete

On Apr 26, 9:37 pm, "Mack Neff" <m...@swairproducts.com> wrote:
> I have a single spreadsheet with a list of clients, addresses and their
> product interests. This table will be used to drive a Mailmerge document. In
> the document, I want to be able to refer to the products in which they
> indicated an interest. The problem is that while one client may have
> identified only one product (one column) others have selected anywhere from
> two to 170 products - each product is in a separate column. I can join two
> columns with "&", but when I have an inconsistent number of columns how do I
> do this efficiently? - I will have to do it for each client, each month, so
> am not interested in manually typing all 171 combinations.


0
pashurst (2576)
4/27/2007 12:54:17 AM
Reply:

Similar Artilces:

Restart numbering ater a set number of rows
I have an excel spreadsheet with 4 columns. the first two columns contains numbers which will never change the third column starts off with 0 (zero) and is repeated for 255 rows then on the 256 row it needs to change to 1 (so basically increment by 1 every 255 rows) the fourth column starts at 0 (zero) and counts up to 255 but then needs to reset it self back to 0 on the 256 row. Help Please!!!!!:confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: I...

Row Number
I need hellp with a formula. In Cell E8 I need to write a formula that will search Column A rows 18 thru 32 and if it finds the date 09/16/2006 it will display the row number. Any help will be greatly appreciated. Thanks Dianne You didn't say what result you want if the date is not present. One way... A1 = some date =IF(COUNTIF(A18:A32,A1),INDEX(ROW(A18:A32),MATCH(A1,A18:A32,0)),"") If the date isn't present the formula returns blank. -- Biff Microsoft Excel MVP "Dianne" <Dianne@discussions.microsoft.com> wrote in message n...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Adding numbers in columns with letters
I need a little help. How do you add up the values of numbers in column which has letters next to the numbers. I have a column o numbers, but the letters "mb" are a space bar away from the numbers. Everytime I use the function to add the numbers I get a value of zero. Could anyone please help me out? Thanks in advance -- Message posted from http://www.ExcelForum.com Assuming you mean the "numbers" all end with " mb", e.g. "123 mb", you can add a "Helper" column containing a formula like this: =--LEFT(A1,LEN(A1)-3) Copy down as far as...

Incrementing Cell Numbers
All: I have created an excel "Workorder" form for our employees to write in what machine they are working on, what parts they used, and how long they spent on the repair... The problem... I need to print out hundreds of these forms for the men to use, and each copy of the form needs to have an unique number. Is there a way to program a cell to automatically increment by 1 every time it is printed? Again, I need to print hundreds at a time, and I need them to automatically increment by 1. TIA, Lance Place this in ThisWorkbook module: Private Sub Workbook_BeforePrint(Cancel ...

Sum of numbers
I have a report that pulls numbers from one main table and 4 linked tables. The report is fairly basic showing a product quantity x price = total. The quanity value comes from one of the 5 tables. Here's the problem. My report needs to show 5 products, or one from each table. Howver, the customer may not be a customer for a product in table 5 so it is not listed in that table...therefore, no data to pull. I was hoping the control source would display a Zero value for the quanity. It displays nothing. So I end up with: 5 x 2.00 = 10.00 7 x 1.50 = 10.50 3 x 2.00 = 6.00 7 x 3.0...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

How do I unwrap text to columns?
I have a spreadsheet where the addresses have been saved as wrapped text in one column. I want to spilt the addresses into columns as Address1, Address2 etc but I can't enter the wrap character (which I think is ALT+ENTER) as a delimiter in the Text to Columns wizard. I have also tried search and replace but again Excel will not recognise ALT+ENTER as a delimiter Any ideas much appreciated! Thanks Re-format the column to eliminate the wrap.............then use Data > TextToColumns with Alt010 (from the keypad) as the delimiter Vaya con Dios, Chuck, CABGx3 "ChristineR&...

number of emails per day
Is there a way to monitor Outlook 2007 usage statistics, such as avg number of emails sent/received per day? ...

Can't divide the columns by twelve anymore
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC On this forum someone taught me to divide columns by twelve -- ie everything above by twelve you just put: <br> =B52/12&#09; <br> =C52/12 <br> =D52/12 <br> =E52/12 <br> =F52/12 <br> etc. <br> Now when I do that I get this: <br> <a href="http://tinypic.com/r/15wnc3o/6">http://tinypic.com/r/15wnc3o/6</a> Click OK and it will open a debugging tool that will point to the problem for you. On 27/11/09 4:15 AM, in article 59bada6c.-1@...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

convert cells to negative number
I have a list of numbers that I need a quick and easy way to convert to a negative number. I have tried highlighting the numbers and using a custom format to add the negative sign and parenthesis but when you add the column up; it is not recognizing the numbers as negative. Any suggestions? Why not create a helper column to do it? "sparker" wrote: > I have a list of numbers that I need a quick and easy way to convert to a > negative number. I have tried highlighting the numbers and using a custom > format to add the negative sign and parenthesis but when you add t...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

XML int column duplication & column alphabetizing
I have an XML file saved as a .xls file. It was dynamically created i VB.NET through a web app, pulling data from a SQL Server database (i that makes a difference). Here are my 2 problems: When I open the file in Excel 2002, it duplicates every column that ha only integers in it, adding a *#agg* to the duplicate column title. don't want the columns duplicated (obviously). The second issue is that it is alphebetizing my columns. I don't wan them alphebetized. I want them in the order in which they are listed. Here is my XML code. You could simply cut and paste this into a blan text ...

Using Get() and Set() instead of accessing the variable directly
Is it better to access the variable using Set() and Get() methods, rather than accessing the variable directly? I suppose I learned early on to access variables directly, instead of using Set() and Get() methods. It was easier for simple stuff, but it caught up with me as I progressed further. Now, I feel as though accessing variables directly is just bad practice. An example would be that even within the same class, I can access private/protected variables, but when I divide up the class into manageable parts, I often have to add Get() and Set() methods anyways. private: int m_x; i...

Restricing total number of recipients a user can send to.
Hi Folks Is it possible to set a restriction at the organisation level to manage the total number of recipients a message can be sent to by a user, while still allowing a user to send to distribution lists which contains more users that the organisation restriction? Example: In Exchange manger we set a value of 20 under global settings / message delivery\defaults\recipients limit. We then create a DL that contains 30 users. Is it possible to allow the user to send to the DL of 30 users while maintaining a global recipient limit of 20? Why do we want to do this: We want to stop pe...

Input variable contains a duplicate document
Somehow I got this error message GP 10 when I did PO import "Input variable contains a duplicate document" It affects all PO import item. Thanks, Tim Tim, Based on the error message, it sounds like eConnect (or the eConnect adapter in Integration Manager) is being used to bring in these transactions. The first place I'd recommend checking would be the eConnect Event Log in the Event Viewer. That should give more details regarding the error along with the XML string being passed in so that you can see the exact document number that is throwing this error. Thanks, &qu...

Adding columns in a query
Hi, this is very strange. I am trying to add 2 columns in a query but it does not come out right. Column1 Column2 Column3 $15.00 $30.00 $15.00$30.00 Column3 should equal $45.00. When I subtract, divide or multiply it's fine. It does not work when i tried to add. this is the code behind Column3: Format(nz([Column1])+nz(Column2), "Currency") Thanks in advance -- Message posted via http://www.accessmonster.com It is interpreting the + as a concatenation operator. Try this instead: Format(Val(nz([Column1]))+Val(nz(Column2)), "Currency") On Jan 18, 8:2...

Using Variables in filenames
I want to automatically name a file created from a particular template. I want the filename to be todays date. i.e. I open timesheet.xlt and the file is created as My Documents\Timesheets\ddmmyyyy.XLS This template will be distributed to several users. I am using Excel 2002 SP3 ActiveWorkbook.SaveAs Filename:= "C:\MyDocuments\Timesheets\" & Format(Date,"ddmmyyyy") & ".xls" although I would suggest you use a format of yyyymmdd, so that it is ordered. -- HTH RP (remove nothere from the email address if mailing direct) "Blinky Bill"...

average last 17 cells (variable)
using formula in A2 =average(IT:Index(A1:IT1,sumproduct(large(column(A:IT)* (A1:IT1<>""),A4)))) A4 is variable cell for calculation. However, I require result to be in row 1. Keep getting curcular ref. Require result in B3 Can anyone assist? brian(at )thompson3001.fsnet.co.uk Regards Brian -- Message posted via http://www.officekb.com A2 is within the range A:IT, and would therefore produce a circular reference. It looks like you might be able to use COLUMN(A1:AT1) instead. If that doesn't solve the problem, try copying the formula from the formula bar and pas...

Max number of characters in a cell
Is it possible to have more than 1024 characters in a cell? I have tried to use char(10) and char(13) however while the formula allows me to copy and paste more than 1024 characters it limits my statements to 100 characters after 1024. Is there any way to go around this and be able to have as many characters as one wish in a cell. Thanks You might search on "limits" in Excel help for this and other limits. A cell can hold a text entry of a max of 32k characters. A formula can have about 1000 characters. -- Jim "Dajana" <Dajana@discussions.microsoft.com>...

Combine 2 Charts 2007 Excel
I have a line chart (looks like an S curve graph) with two rows of data below & this chart looks exactly like I want it to. Period % Complete Dec-04 7% Jan-05 9% Feb-05 10% Mar-05 12% Apr-05 14% May-05 19% Jun-05 22% Jul-05 23% Aug-05 24% Sep-05 26% Oct-05 28% Nov-05 32% Dec-05 36% Jan-06 40% Feb-06 45% Mar-06 50% Apr-06 55% May-06 59% Jun-06 63% Jul-06 66% Aug-06 71% Sep-06 75% Oct-06 78% Nov-06 81% Dec-06 85% Jan-07 87% Feb-07 89% Mar-07 91% Apr-07 93% May-07 94% Jun-07 94% Jul-07 95% Aug-07 95% Sep-07 95% Oct-07 95% Nov-07 95% Dec-07 95% Jan-08...