Report: Cell #1, Cell #2, Cell #3, Cell #4

I am stuck again and would love som help :(

I would like to repeat all words found inside ~25 cells, separated only
by ", ", ignoring empty cells. 

Data:
A1: [Apple  ]
A2: [Orange]
A3: [Banana]
A4: [Tomato]
A5: [Syrup  ]
A6: [          ]
A7: [          ]
A8: [          ]

The result should be something like:
[Apple, Orange, Banana, Tomato, Syrup]


-- 
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?threadid=518176

0
3/2/2006 11:19:16 AM
excel 39879 articles. 2 followers. Follow

10 Replies
836 Views

Similar Articles

[PageSpeed] 11

    iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
    For i = 1 To iLastRow
        If Cells(i,"A").value <> "" Then
            Range("B1").Value = Range("B1").Value & Cells(i,"A").Value & ","
        End If
    Next i

    With Range("B1")
        .Value = Left(.Value, Len(.Value)-1)
    End With

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JemyM" <JemyM.241hva_1141298400.5381@excelforum-nospam.com> wrote in
message news:JemyM.241hva_1141298400.5381@excelforum-nospam.com...
>
> I am stuck again and would love som help :(
>
> I would like to repeat all words found inside ~25 cells, separated only
> by ", ", ignoring empty cells.
>
> Data:
> A1: [Apple  ]
> A2: [Orange]
> A3: [Banana]
> A4: [Tomato]
> A5: [Syrup  ]
> A6: [          ]
> A7: [          ]
> A8: [          ]
>
> The result should be something like:
> [Apple, Orange, Banana, Tomato, Syrup]
>
>
> -- 
> JemyM
> ------------------------------------------------------------------------
> JemyM's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26945
> View this thread: http://www.excelforum.com/showthread.php?threadid=518176
>


0
bob.phillips1 (6510)
3/2/2006 11:56:59 AM
JemyM, here is another option,

Function ConCatRange(CellBlock As Range) As String
'By: Gord Dibben, **changed for your needs**
'concatenate only the non-empty cells puts a , between them
'******Usage is:  =ConCatRange(A1:A25)

Dim Cell As Range
Dim sbuf As String
    For Each Cell In CellBlock
        If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & ","
    Next
    ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"JemyM" <JemyM.241hva_1141298400.5381@excelforum-nospam.com> wrote in 
message news:JemyM.241hva_1141298400.5381@excelforum-nospam.com...
>
> I am stuck again and would love som help :(
>
> I would like to repeat all words found inside ~25 cells, separated only
> by ", ", ignoring empty cells.
>
> Data:
> A1: [Apple  ]
> A2: [Orange]
> A3: [Banana]
> A4: [Tomato]
> A5: [Syrup  ]
> A6: [          ]
> A7: [          ]
> A8: [          ]
>
> The result should be something like:
> [Apple, Orange, Banana, Tomato, Syrup]
>
>
> -- 
> JemyM
> ------------------------------------------------------------------------
> JemyM's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=26945
> View this thread: http://www.excelforum.com/showthread.php?threadid=518176
> 


0
3/2/2006 1:56:58 PM
Ok, I am greatful for the 2 examples given here. I guess they are both
visual basic scripts and that's above my level. If someone have a
website that describes how I can use this in Excel I would be greatful.
:(


-- 
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?threadid=518176

0
3/3/2006 9:42:05 AM
See http://www.mvps.org/dmcritchie/excel/getstarted.htm

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JemyM" <JemyM.24384z_1141379101.3085@excelforum-nospam.com> wrote in
message news:JemyM.24384z_1141379101.3085@excelforum-nospam.com...
>
> Ok, I am greatful for the 2 examples given here. I guess they are both
> visual basic scripts and that's above my level. If someone have a
> website that describes how I can use this in Excel I would be greatful.
> :(
>
>
> -- 
> JemyM
> ------------------------------------------------------------------------
> JemyM's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26945
> View this thread: http://www.excelforum.com/showthread.php?threadid=518176
>


0
bob.phillips1 (6510)
3/3/2006 10:06:54 AM
JemyM,

Looks like I'm not the only one out there who doesn't use VB.  Here's a
simple solution to your problem.

In cell B1, type the formula =if(and(istext(A1),istext(A2)),",","")

This checks to see if the cell to the left and the one below it both
have text in them (in which case there should be a comma followed by a
space).  Fill this down to the bottom of your list.

In cell C1, type the formula =A1&B1.  This concatenates the two.  Fill
down again.

At the bottom of column C (say C26), type =CONCATENATE(C1,C2,C3...C25).
In full, not abbreviated like this - I'm just being lazy.  This should
give you the list you want.  

Let me know how you go.


-- 
random1970
------------------------------------------------------------------------
random1970's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32112
View this thread: http://www.excelforum.com/showthread.php?threadid=518176

0
3/5/2006 9:43:38 AM
On Thu, 2 Mar 2006 05:19:16 -0600, JemyM
<JemyM.241hva_1141298400.5381@excelforum-nospam.com> wrote:

>
>I am stuck again and would love som help :(
>
>I would like to repeat all words found inside ~25 cells, separated only
>by ", ", ignoring empty cells. 
>
>Data:
>A1: [Apple  ]
>A2: [Orange]
>A3: [Banana]
>A4: [Tomato]
>A5: [Syrup  ]
>A6: [          ]
>A7: [          ]
>A8: [          ]
>
>The result should be something like:
>[Apple, Orange, Banana, Tomato, Syrup]


I note that you have opening and closing brackets around each word.  Are they
really there or is that some kind of metacharacter?

In any event, one way is to download and install Longre's free morefunc.xll
add-in from  http://xcell05.free.fr/

You can then use the formula:

"["&REGEX.SUBSTITUTE(MCONCAT(REGEX.MID(
A1:A25,"\w+"),", "),"(?<=, )(, )+|(, )+$")&"]"

If the brackets are not really part of your cell entries, or the result, then
you can use the simpler formula:

=REGEX.SUBSTITUTE(MCONCAT(I1:J25,", "),"(?<=, )(, )+|(, )+$")



--ron
0
ronrosenfeld (3122)
3/5/2006 2:00:09 PM
Ron Rosenfeld Wrote: 
> I note that you have opening and closing brackets around each word.  Are
> they
> really there or is that some kind of metacharacter?
> 
> In any event, one way is to download and install Longre's free
> morefunc.xll
> add-in from  http://xcell05.free.fr/
> 
> You can then use the formula:
> 
> "["&REGEX.SUBSTITUTE(MCONCAT(REGEX.MID(
> A1:A25,"\w+"),", "),"(?<=, )(, )+|(, )+$")&"]"
> 
> If the brackets are not really part of your cell entries, or the
> result, then
> you can use the simpler formula:
> 
> =REGEX.SUBSTITUTE(MCONCAT(I1:J25,", "),"(?<=, )(, )+|(, )+$")
> --ron

The brackers were there to make it "look" like an excel stylesheet.
Sorry about the confusion.

Thanks for the tip. I will check it out :)


-- 
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?threadid=518176

0
3/6/2006 9:08:32 AM
random1970 Wrote: 
> JemyM,
> 
> Looks like I'm not the only one out there who doesn't use VB.  Here's a
> simple solution to your problem.
> 
> In cell B1, type the formula =if(and(istext(A1),istext(A2)),",","")
> 
> This checks to see if the cell to the left and the one below it both
> have text in them (in which case there should be a comma followed by a
> space).  Fill this down to the bottom of your list.
> 
> In cell C1, type the formula =A1&B1.  This concatenates the two.  Fill
> down again.
> 
> At the bottom of column C (say C26), type =CONCATENATE(C1,C2,C3...C25).
> In full, not abbreviated like this - I'm just being lazy.  This should
> give you the list you want.  
> 
> Let me know how you go.

Lol... that was a bit too easy...

I think I go with that one and spend more time with VB when I have the
time. Thanks!


-- 
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?threadid=518176

0
3/6/2006 9:41:05 AM
If you are trying to list all unique instances of a list of cells
containing words then I would use the advanced filter option under data
-- filters--advanced filters and check the unique records box.  If you
have multiple words per cell divided by commas then you should try to
use text to columns to get one word per cell.  No formulas required and
fast!

0
kletcho (34)
3/8/2006 4:27:25 AM
If you are trying to list all unique instances of a list of cells
containing words then I would use the advanced filter option under data
-- filters--advanced filters and check the unique records box.  If you
have multiple words per cell divided by commas then you should try to
use text to columns to get one word per cell.  No formulas required and
fast!

0
kletcho (34)
3/8/2006 4:27:28 AM
Reply:

Similar Artilces:

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Microsoft CRM 3.0 Installation 11-08-07
Good Day board, Quick and to the point. I'm installing a trial copy of Microsoft CRM 3.0 that I got at a computer conference recently and I've gotten everything to work in the System Requirements dialog except that it's telling me that "The minimum required version is SQL Server 2000 SP4 (8.0.2026)". Strange this is that I do believe that I have SQL Server 2005 installed on the machine. Any idea how to be absolutely sure of what version I have installed? Have you choosed the right name of SQL server during installation set- up?` You have to make it sure if it is ...

Reporting from Project Server
I dont know if i need to ask this question here or in the Access section. I have an ODBC connection to the Project Server database so I can make reports through Access. Access' limit of 255 fields per table is causing me some trouble. for example, the MSP_VIEW_PROJ_PROJECTS_ENT table has well over 255 fields. Access only shows me the first 255 fields. how can I change that so I can see all the fields in that table? thanks, Hadi Hadi, I have not tried this yet it may be a viable option. Have your DBA create a view that pulls the key fields to this table and the specifi...

Count If Formula #4
I need a formula that will figure the following: If column C = 3, count all times < 00:45 If column C = 4, count all times < 00:45 If column C = 3, count all times > 00:46 Thanks. Your description is a bit confused.You have different actions for the same value (C=3). And where are the times, in a different column than C? So, making many assumptions, perhaps =SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1:D200<TIME(0,45,0)))+S UMPRODUCT(--(C1:C200=5),--(D1:D200<TIME(0,46,0))) -- HTH RP (remove nothere from the email address if mailing direct) "Denise"...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

Emails #3
Hi, I was wondering if anyone knew of any web based email provder that I could use that wont be bloked but the I.T Filer at my work. I require use of emails during the day for personal use but work emails are monitored. I have tired various sites i.e Hotmail, Yahoo, Gmail, lycos etc but they are all blocked. Does anyone know of any that may not be picked up buy the web filer. Fiona Fiona, It is difficult to answer because it depends on what your filter is and how it is monitored. For instance, if it is actively monitored and somebody found out you were accessing the site, then it coul...

Contacts in Exchange #2
We are running Exchange 2003 and Outlook 2003. I have tried to give a user rights to her boss's contacts through delegation and setting the rights on the contacts folder, however, while she has the rights she is unable to add or see her boss's contact folder in her Outlook. She can see it if she runs a search, but can not permanently put it in her list of contact folders. IS there a way that I can accomplish this through the back-end? Thanks, Joseph rapoport jrapoport@insurmark.net ...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

learning Excel #3
Hi, I was considering learning Excel as an additional tool for my data analysis work. Is it better to use data sets that I have previously used with SPSS and apply the same analysis tools as in SPSS? For applying the appropriate tools, I was considering using excel's online help. The second option I have is to use some excel book for data analysis and apply the techniques to data sets provided with the book. Any suggestions????? regards Metal ...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Goal Seek #2
Can anyone tell me a little about goal seek? Hi basically it's used to change an input variable so that the result of the formula equals what you want it to ... say i have the following A B C 1 10 20 =A1+B1 now C1 will give me 30, but if i want to know what B1 will need to be if i want C1 to show 50, i can use goal seek Set C1 to value of 50 by changing B1 Hope this helps Cheers JulieD "Jamie Hart" <jhart@emaple.net> wrote in message news:u3OscELdEHA.244@TK2MSFTNGP12.phx.gbl... > Can a...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Over Time Pay rate 1.51?
Its that time of year again, Audit time. The auditors have come across a very strange problem it seems that around October of last year random employees have had there OT pay rates increased to 1.51 (instead of the standard 1.50), I cant for the life of me figure out how this has come to be. Has anyone come across this problem before? And is there something I can do to fix or prevent this from reoccurring? It’s just not feasible to check each employee every week before payroll. Im certain that the employees were setup correctly initially. Thanks I have seen this and have not been ab...

Columns #4
My columns in Excel have disappeared how do I get them back? Hi Me! I'm just guessing what you mean. Use: Tools > Options > General Remove check from "R1C1 reference style" OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "me" <anonymous@discussions.microsoft.com> wrote in message news:12D9050B-079C-476D-B5B6-84445F60EA51@microsoft.com... > My columns in Excel have disappeared how do I get them back? Another gue...

Increment A2 from A1 and A2 Sum #2
I have two cells: A1 - manual value B1 - automatic increment = B1 + A1 How can i make it? (=error: circular reference) Note: I have this formula repeated in some lines: = B2 + A2 = B3 + A3 .. -- Message posted from http://www.ExcelForum.com ...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

form base authentication #2
This is killing me. I'm implementing form base authentication. I have SSL and everything working but for one problem when use won't to get to owa they type mail.domain.com which then defaults to http://mail.domain.com. the problem I'm having is that FBA need it to default to https not http. How can I get it to default to HTTPS with out telling the uses to type https? I am implementing this on a FE/BE environment wayne .. What I do is change the http port on the main site to something like 8080, and then create a new site that listens on 80. For that site, instead of...

Formating text in cells
Hello, does anyone know how to force Excel to keep the hyphen or dash character (-) with the associated numbers when putting multiple dash-#s in a cell and turning "wrap" formating on? This is the same problem I have in Word; when I add dash-#s (ie; -xxx, -yyy, -zzz) Office apps interpret dashes ( - ) as a hypehen to a word and will keep the dash/hyphen on the line above until there are enough characters to force the dash/hyphen to the next line. In my work we use the format; -xxx, -yyy, -zzz to denote multiple versions of the same base part. Work instructions to operators...

New Record, last Date + 1 different records
I have a datefield in a form that I would like to have autofilled when I enter a new record. Right now I have something like this: If Me.NewRecord then Me.DateGauged = DMax("DateGauged","tblGaugesInput") + 1 End if My problem is that it just looks for the max date of the whole table. I would like to narrow it down to a specific group of records in the table and do the last date + 1 on that group: Example of my table. TankNumber DateGauged Measurement 8555 2/1/2008 8" 8555 2/2/2008 9" 8556 2/1/2008 ...

2 different Qty in order details in northwind
Hi, im pretty new with access 2007, i decide to use northwind template. In order detail both form, i have 2 different Qty, the original Qty is the Qty that will link to Inventory and I add another field Qty1 for invoice purpose. For example, I got 1 roll of tape, 1 roll will be cut into 10 pcs. We need to bill according to customer request qty 10, but actual qty sold is only 1. Anyone come across this issue. How to modify it w/o effect the function? table/form as below:- ID ORDER ID PRODUCT ID QUANTITY (FOR INVENTORY) QTY1 (FOR INVOICE) UNIT PRICE AND SO ON Is there a...

Inactive records in the "Look up records" dialogue (CRM 4.0)
Due to "dirty import data" I have a lot of duplicates in my CRM system. These have all been deactivated (but not merged) until they can be evaluated and deleted. Unfortunately, when I use "Advanced find" and use account name as a criteria, *all* accounts show up in the "Look up records" dialogue box - regardless of status. Is there any way to ensure only active accounts show up in this dialogue box? On Dec 4, 2:29=A0am, "maria.elmv...@gmail.com" <maria.elmv...@gmail.com> wrote: > Due to "dirty import data" I have a lot of duplicates...