Concatenating non adjacent cells 01-29-10

Hello - I am trying to create a field that concatenates cells that are 
populated from the previous 12 cells on that row, but excluding blanks and 
adding a * delimited character between each instance. Please find a 4 column 
example below

ID     1      2      3       4         Result required
Z       A             C       D         A*C*D
Y              B       C                 B*C
X       A     B               D         A*B*D

I will be applying this to a 2007 version spreadsheet containing in excess 
of 10,000 lines. There will be at least 5 blank cells on each row.

Many thanks - Bob


0
Utf
1/29/2010 1:06:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
858 Views

Similar Articles

[PageSpeed] 28

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&mid=c7dcb592-3341-44ad-b2cf-28607adcf2c1&sloc=en-us


"Bob Freeman" wrote:

> Hello - I am trying to create a field that concatenates cells that are 
> populated from the previous 12 cells on that row, but excluding blanks and 
> adding a * delimited character between each instance. Please find a 4 column 
> example below
> 
> ID     1      2      3       4         Result required
> Z       A             C       D         A*C*D
> Y              B       C                 B*C
> X       A     B               D         A*B*D
> 
> I will be applying this to a 2007 version spreadsheet containing in excess 
> of 10,000 lines. There will be at least 5 blank cells on each row.
> 
> Many thanks - Bob
> 
> 
0
Utf
1/29/2010 1:26:01 PM
Assume that you are having the example database from A1 to E4 range like the 
below:-

	COL A	COL B	COL C	COL D	COL E
ROW1	ID	1	2	3	4
ROW2	Z	A	BLANK	C	D
ROW3	Y	BLANK	B	C	BLANK
ROW4	X	A	B	BLANK	D

In cell F2 paste the below formula:

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(TRIM(B2),"*",TRIM(C2),"*",TRIM(D2),"*",TRIM(E2)),"BLANK*",""),"*BLANK","")

Copy the F2 cell and paste it for the remaining cells of F Column.

Now the results will show like the below:-
A**C*D
*B*C*
A*B**D

Now place the cursor in B1 and select upto E4 that is the selection range 
should be B1:E4.  Press CNTRL+G>>SPECIAL>>select BLANKS Option Button and 
press ok.  Now you will see the blank cells will be getting selected (i.e.) 
B3,C2, D4 & E3.  Now press Cntrl+H which will open the find and replace 
dialog box.  In FIND WHAT: field leave it as blank and in REPLACE WITH: field 
type BLANK and give REPLACE ALL and click close.

Now you can notice that the cell F2 to F4 will show the desired results.  
Copy and paste the F Column formula to values.  

Now select B1:E4 again press Cntrl+H which will open the find and replace 
dialog box.  In FIND WHAT: type BLANK and in REPLACE WITH: field leave it as 
blank and give REPLACE ALL and click close, which will restore the cell which 
is having the character BLANK from B1:E4 to blank cells.

That’s It!!!

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Bob Freeman" wrote:

> Hello - I am trying to create a field that concatenates cells that are 
> populated from the previous 12 cells on that row, but excluding blanks and 
> adding a * delimited character between each instance. Please find a 4 column 
> example below
> 
> ID     1      2      3       4         Result required
> Z       A             C       D         A*C*D
> Y              B       C                 B*C
> X       A     B               D         A*B*D
> 
> I will be applying this to a 2007 version spreadsheet containing in excess 
> of 10,000 lines. There will be at least 5 blank cells on each row.
> 
> Many thanks - Bob
> 
> 
0
Utf
1/29/2010 1:49:03 PM
Reply:

Similar Artilces:

IF FORMULA 04-06-10
Hi: I am trying to write an if formula that if B2 is equal to a specific date in a range of dates then it is the end of a pay period, if B2 is equal to a date in a second range of dates then it state pay day. I have set up two tables with dates. One has pay period ending dates and the other table has pay day dates. I keep getting a Value error. Please help. -- Donna =IF(COUNTIF(A1:A10,B2),"end of pay period",IF(COUNTIF(C1:C10,B2),"pay day","")) Change ranges to suit. -- Regards Dave Hawley www.ozgrid.com "Donna" ...

deselect cells within a range of cells
I have found this to be most frustrating!!! Windows Explorer allows you to select an entire list of items and then deselect individual items by holding the ctrl key down and clicking the items you wish to deselect. Why does this not work in Excel??????? I have a list of 100 items and wish to deselect about 10 that are randomly dispersed in the selection. This seems to such a simple thing, but I've found no solution. Try this: =INDIRECT("E"&C1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all m...

Specifying next cell to "jump" to
When a user completes as much of a specified cell range as necessary an wishes to move on to the next range, I would like to give them a option to go "automatically" to the first cell of the next range a opposed to having to tab through the remainder of the range or click i the first cell of the range they wish to go to. For example, the firs range is A10 through D25. After inputting data in D15, the user want to go to the next range, the first cell of which is A30. How can th user most quickly and easily go from D15 to A30? I appreciate your help -- Message posted from http://...

"De-name" cell references in formulas
Hello, I'm working in Excel 2003 SP2, and I'm trying to replace all references to named ranges used within my formulas within a workbook with the underlying absolute cell references. I found a solution in the archives of this newsgroup ( http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc ) , but I can't get it to work (neither the manual version nor the macro version). My only guess is that the behavior has of TransitionFormEntry has changed in Excel 2003. If so, is anyone aware of any other solutions. I found...

Locking certain cells
I have an expense reporting template which I have locked certain cells that I do not want changed while allowing input into other cells. This is under the tools functions but its not coming to me right away how I did this. The users do not need to use any passwords --its just certain cells are read-only and cannot be changed unless unlocked. How'd I do this I need to update? Disregard- figured it out again "Scott" wrote: > I have an expense reporting template which I have locked certain cells that I > do not want changed while allowing input into other cells. Th...

IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a column of numbers and if they are greater than a certain value ie: 120 , I want the sell to have the color red. Any ideas how to do this in Excel 2007? THanks Take a look at format|conditional formatting. Ron Boetger wrote: > > I did this before but I can't remember how I did it. I want to have a > column of numbers and if they are greater than a certain value ie: 120 > , I want the sell to have the color red. > > Any ideas how to do this in Excel 2007? > > THanks -- Dave Peterson ...

non-matrix purchase order stylesheet?
When I export a purchase order to excel using either the "Transfer" or the "RMS Purchaseorder" stylesheets, the matrix items come out listed separately from the non-matrix item in a matrix format. Does anyone know where I can find a simpler stylesheet that just lists all the itmes in a single table? I've looked in MS/CustomerSource. Alternatively, anyone have any hints to get me started tweaking the stylesheet I have? ...

Removing Excess Digits From Cells
I have two lists of numbers that I'd like to use to join two tables in Access. One list has an extra "Check Digit" at the end. I'd like to know how to remove the "Check Digit". (It may not matter but one of the characters in the list of "numbers" is a letter.) I tried to "Format Cells" by typing in one less ###-##-#### but it did not do anything. Hi Raquel, try using the formula =LEFT(E6;1) for a column next to the list and use E6 for every cell... so you get rid of the last digit. Best Markus >-----Original Message----- >I hav...

Copying Info Into Numerous Cells
I have just completed creating and entering data into thousands of worksheets. Now someone has decided they want to change and add columns to my worksheets. Is there any way to have data copied from 1 cell to another in several worksheets at a time, even if the data is different? If there isn't...I'm going to have a melt-down right here at work! :eek: -- calimari ------------------------------------------------------------------------ calimari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24537 View this thread: http://www.excelforum.com/showthr...

Enter 1 but appears as 0.01
I'm having a problem entering data (numbers) into cells. If I type in number, say 1, it appears as 0.01. 100 appears as 1, 1000 as 10...an so on. I've tried going to format then cells, then on the number ta and clicking on general but that doesn't work. Perhaps there's a simple solution but I can't seem to find it. Pleas help -- Message posted from http://www.ExcelForum.com Try tools|options|Edit uncheck the Fixed Decimal checkbox. "Jane <" wrote: > > I'm having a problem entering data (numbers) into cells. If I type in a > number, say 1, i...

Incorrect cost basis for non default currencies
Hi all, I am running Money 2000 italian version, and I have Euro as default currency. I bought a corporate bond in canadian dollars. After entering it in Money, I see that in the Investments/Portfolio view the cost basis column reports a value different from market value - commissions. Since a similar test bond using euro as currency reports a correct cost basis, I am wondering if the problem can be related to the currency. Has anyone else experienced similar problems? Thanks in advance Enrico Toracca ...

Re: 05-19-10
"karlitos" <tito1973mexicli@hotmail.com> escribi� en el mensaje de noticias:... > ...

How to select a sheet and input data into certain cells
I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum AB1=1stQTR AC1=2ndQtr AD1=3rdQTR AE1=4thQT AA2=2004 Sheet2 Sheet3 etc . . . AA3=2005 Sheet6 Sheet AA4=2006 Sheet8 Sheet AA5=2007 Sheet4 Sheet I would like to be able to select a year and a quarter and it goes to the sheet, i.e 2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter I would also like to be able to print sheet5 and ask me if I want to print it agai or close and upon closing go back to sheet Is there an...

just a test 10-27-07
"Alexander F�rst" <alexander.fuerst@fh-steyr.at> wrote in message news:ufnammGGIHA.1208@TK2MSFTNGP03.phx.gbl... > >i am shiv saimarathore_2k6@yahoo.com "ABC" <MSNEWS.MICROSOFT.COM> wrote in message news:eC$4rYkMIHA.4228@TK2MSFTNGP02.phx.gbl... > > "Alexander F�rst" <alexander.fuerst@fh-steyr.at> wrote in message > news:ufnammGGIHA.1208@TK2MSFTNGP03.phx.gbl... > > > >i am shiv > > ...

Integration Manager error 04-15-10
I am on GP10 sp3. I am trying to run an integration to just change the item description on a list of items. I have successfully set up many integrations but am really having trouble with this one. All I have is the item number and the description to update in the .csv source file. Here is the error I am getting: DOC 1 ERROR: A value exceeding the maximum length was found in the 'Item Number' field of the root recordset. Maximum length is 0. Any ideas on how to resolve this? Are you using an alternate Item Maintenance window? "davidf." wrote: >...

Inserting extra info into a cell
I currently have three columns in a spreadsheet. This spreadsheet is used as a bilingual glossary of terms. The first column simply contains the following: ** Now the second column contains my Spanish words and the third column my English words. So, it looks something like this:- ** Hola Hello Now, I wish to import this data into some new software I have however, it requires me to put the word <Spanish> before every words in the second column and <English> before every word in the third column. So, in essence, it will be ** <Spanish>...

Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is there a way to count the number of non-duplicated entries in this column by using a formula? Thanks for any assistance, Please respond to hers2keep @ yahoo . com. Thanks, carla carla Try Chip Pearson's site........ http://www.cpearson.com/excel/duplicat.htm#CountingUnique BTW. The customary response is to the News Group, not email. That way we all learn. Gord Dibben Excel MVP XL2002 On 28 Aug 2003 14:30:58 -0700, cbr@saturnsea.com (carla) wrote: >I have a list of trailers in a sheet that are designated as...

E mail 05-20-10
cant open e mail on screen icon Please explain your issue more fully. Where is this screen icon, on your desktop or on the Internet Explorer window? Is there an error message? What exactly does it say? --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "andrew mark dunne" <andrew.dunne@live.co.uk> wrote in message = news:O126%23VF%23KHA.5064@TK2MSFTNGP06.phx.gbl... > cant open e mail on screen icon Does any error message come up? -- Americano TRYING THE SAME THING OVER, AND EXPECTING A DIFFERENT ...

Conditional Formatting Across Multiple Cells
I have data file that I want to conditional format Red/Green fill based on >= conditional on a single row of data. In Excel 2003 it was fairly simple to lock a row but not a column and by selecting all the cells to format it would change the column relative to the cell. Just having an issue transitioning to conditional formatting in 2007. Sample Data a b c d 1 goal 10 245 125 2 day1 8 200 76 3 day2 8 250 125 4 day3 15 250 130 5 day4 15 300 150 6 day5 0 100 0 7 Avg 9 220 96 Cells Rows 2 - 7 should be conditional on Row ...

Lookup function 02-14-10
I have excel 2003. I did a vlookup for my transcripts page. In the first vlookup i needed it to look for the letter grade and give me the gradepoint average. Now in the other cell i need it to look for the grade. In the cell that gives the gradepoint average it has a formula in their already. Can i still do a vlookup if the cell that has the information has a formula in it already. Example. The cell that has the formula adds all the grades from each class and gives me an accumluated gradepoint average for the ten classes taken. Now that gradepoint average I want a letter ...

Ignoring blank cells in a combo box...
Is there any way I can ignore blank cells when using a combo box fro the forms menu? For instance, my combo box is being filled by th range A1:A5. However, if A3 is blank, I don't want it ( a blank) t show up in the drop down box. Is there is a more efficient way to d this or it might it be more simple to use a combo box from the contro toolbox? Any help would be appreciated. Thanks -- Message posted from http://www.ExcelForum.com The combobox from the Forms toolbar is also called a DropDown. And you could use a little code that filled up that dropdown: I chose to put it into the...

Bulk Import failures view for non-admin users
Hi, I got a requirement to show 'Failures' while clicking on Imports. CRM shows this only for System Administrators. Can we make it enable for other custom roles? if so how? Thanks in Advance Rakesh Narayan ...

Return a number in one cell to long hand text in another.
Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Take a look at http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP "CP" <CP@discussions.microsoft.com> wrote in message news:C0D9B8E3-3D66-4EA0-9184-57762BD663D6@microsoft.com... > Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Go to Google and search the newsgroups for "spell number" On Wed, 20 Oct 2004 15:07:02 -0700, "CP" <CP@discussions.microsoft.com> wrote: >Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents ...

Locking cells #3
hi there, I was wondering if it is possible to Lock a cell so that no-one ca enter data into it, the reason behind this is because the cell i update from a different worksheet. Thank you for your time Kind Regards Swmasso -- Message posted from http://www.ExcelForum.com Hi It's possible. Simply protect the sheet - by default all cells are locked then. When you want to allow users to access some cells, unlock them before protecting the sheet (Format.Cells.Protection). When you want to restrict the access to some cells only, and to allow for rest of worksheet, then unlock all cells ...

space between text strings with concatenate
I'm using the concatenate function to join two bits of text - but I want a space between, and the function joins them together without a space. How can I insert a space into the resulting text please? (It seems to me that we would want such a space in ANY two text strings which we join together with concatenate - wouldn't we?? Why doesn't it put one in by default?!) Thanks Jeff Hi Jeff, =text1&" "&text2 -- Kind Regards, Niek Otten Microsoft MVP - Excel "Jeff" <no_em@ilplease> wrote in message news:42275bd0@212.67.96.135... > I...