Separating a comma separated list

Not having a great deal of experience with macros, I thought I might
see if I could pick the brains of the group.

I have to essentially break out a comma separated text string in a
single cell into a vertical list, with a reference number thrown in
for good measure.

Example.

Data I have:
     A                B
1   NUMBER    RESOURCE
2   101           Smith John,Jones Adam,Brown Philip,Greene Thomas
3   102          Smith John,Jackson Arthur

Needs to look like:
    A                B
1   NUMBER   RESOURCE
2   101     Smith John
3   101     Jones Adam
4   101     Brown Philip
5   101     Greene Thomas
6   102     Smith John
7   102     Jackson Arthur

The resources will be copied into Excel from Project on a weekly
basis, so I was after some ideas on how to make a macro that would
separate the text out.

Any ideas?

Chris

0
draco664 (3)
9/27/2007 10:02:46 AM
excel 39879 articles. 2 followers. Follow

3 Replies
701 Views

Similar Articles

[PageSpeed] 16

How about this one? I assumed data start at A2

Sub splitdatatest()
Dim pcell As Range, tcell As Range
Dim n As Long
Dim res

Set pcell = Cells(2, 1) '<=== change here
Set tcell = pcell.Offset(1, 0)

Application.ScreenUpdating = False

Do While (Not IsEmpty(pcell))
    res = Split(pcell.Offset(0, 1), ",")
    n = UBound(res)
    If n > 0 Then
        tcell.Resize(n, 1).EntireRow.Insert
        pcell.Offset(1, 0).Resize(n, 1) = pcell.Value
        pcell.Offset(0, 1).Resize(n + 1, 1) = _
            Application.Transpose(res)
    End If
    Set pcell = tcell
    Set tcell = pcell.Offset(1, 0)
Loop
End Sub

keizi

"draco664" <draco664@hotmail.com> wrote in message 
news:1190887366.248184.137740@57g2000hsv.googlegroups.com...
> Not having a great deal of experience with macros, I thought I might
> see if I could pick the brains of the group.
>
> I have to essentially break out a comma separated text string in a
> single cell into a vertical list, with a reference number thrown in
> for good measure.
>
> Example.
>
> Data I have:
>     A                B
> 1   NUMBER    RESOURCE
> 2   101           Smith John,Jones Adam,Brown Philip,Greene Thomas
> 3   102          Smith John,Jackson Arthur
>
> Needs to look like:
>    A                B
> 1   NUMBER   RESOURCE
> 2   101     Smith John
> 3   101     Jones Adam
> 4   101     Brown Philip
> 5   101     Greene Thomas
> 6   102     Smith John
> 7   102     Jackson Arthur
>
> The resources will be copied into Excel from Project on a weekly
> basis, so I was after some ideas on how to make a macro that would
> separate the text out.
>
> Any ideas?
>
> Chris
> 

0
kounoike1 (60)
9/27/2007 11:40:50 AM
Hi,

I put some code together and by the time I finished I see you already have a 
reply. However I will post my code also and you then have a choice. 

The code parses the comma separated values using Text to Columns and then 
copies the data and transposes to another worksheet. It then copies the 
Number field.

Sub Macro2()
Dim wsSht1 As Worksheet
Dim wsSht2 As Worksheet
Dim rngColB As Range
Dim rngRows As Range
Dim rngDest As Range
Dim c As Range

Set wsSht1 = Sheets("Sheet1")
Set wsSht2 = Sheets("Sheet2")

wsSht1.Select
Columns("B:B").TextToColumns Destination:=Range("B1"), _
    DataType:=xlDelimited, _
    Comma:=True
        
wsSht2.Range("A1") = "NUMBER"
wsSht2.Range("B1") = "RESOURCE"

With wsSht1
    Set rngColB = Range(.Cells(2, 2), _
        .Cells(.Rows.Count, 2).End(xlUp))
End With

With wsSht1
    For Each c In rngColB
        Set rngRows = Range(c, .Cells(c.Row, _
            .Columns.Count).End(xlToLeft))
        Set rngDest = wsSht2.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0)
        rngRows.Copy
        rngDest.PasteSpecial Paste:=xlPasteAll, _
            Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        c.Offset(0, -1).Copy _
        Destination:=rngDest.Offset(0, -1) _
        .Resize(rngRows.Columns.Count, 1)
    Next c
End With
wsSht2.Select
Range("A1").Select
End Sub

Regards,

OssieMac

 
0
OssieMac (238)
9/27/2007 12:08:03 PM
On Sep 27, 5:08 am, OssieMac <Ossie...@discussions.microsoft.com>
wrote:
> Hi,
>
> I put some code together and by the time I finished I see you already have a
> reply. However I will post my code also and you then have a choice.
>
> The code parses the comma separated values using Text to Columns and then
> copies the data and transposes to another worksheet. It then copies the
> Number field.
>
> Sub Macro2()
> Dim wsSht1 As Worksheet
> Dim wsSht2 As Worksheet
> Dim rngColB As Range
> Dim rngRows As Range
> Dim rngDest As Range
> Dim c As Range
>
> Set wsSht1 = Sheets("Sheet1")
> Set wsSht2 = Sheets("Sheet2")
>
> wsSht1.Select
> Columns("B:B").TextToColumns Destination:=Range("B1"), _
>     DataType:=xlDelimited, _
>     Comma:=True
>
> wsSht2.Range("A1") = "NUMBER"
> wsSht2.Range("B1") = "RESOURCE"
>
> With wsSht1
>     Set rngColB = Range(.Cells(2, 2), _
>         .Cells(.Rows.Count, 2).End(xlUp))
> End With
>
> With wsSht1
>     For Each c In rngColB
>         Set rngRows = Range(c, .Cells(c.Row, _
>             .Columns.Count).End(xlToLeft))
>         Set rngDest = wsSht2.Cells(Rows.Count, 2) _
>             .End(xlUp).Offset(1, 0)
>         rngRows.Copy
>         rngDest.PasteSpecial Paste:=xlPasteAll, _
>             Operation:=xlNone, SkipBlanks:= _
>         False, Transpose:=True
>         c.Offset(0, -1).Copy _
>         Destination:=rngDest.Offset(0, -1) _
>         .Resize(rngRows.Columns.Count, 1)
>     Next c
> End With
> wsSht2.Select
> Range("A1").Select
> End Sub
>
> Regards,
>
> OssieMac

Wow, thanks to both of you. I really appreciate it. I'll try both and
see which works best.

Thanks again.

Chris

0
draco664 (3)
9/27/2007 9:02:44 PM
Reply:

Similar Artilces:

Junk E-Mail Lists Error in Outlook 2003 with Exchange 2003
I have seen several people post about this error message that comes up in Outlook, but so far I haven't seen anyone respond. I am running an Exchange 2003 server with SP1 and my client is Outlook 2003. I installed IMF a few weeks ago, and it appears that this message started soon afterwards. I have now uninstalled IMF, but am still receiving the message. My end users are getting frustrated, as am I. There also appears to be an error in the IMF deployment guide about adding a registry key to increase the list size in Exchange. It states to "right click System", but there is n...

How to create a single line separated by commas from a matrix?
Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a format compatible with other program - an email program-, which requires the information to be arranged in a different way. The program needs all these data to be ordered in a single row, with each characteristic between quotation marks, and separated by commas, following this pattern: "characteristic 1","characteristic ...

Delete contact from multiple distribution lists
Hi, I manage multiple distribution lists, and often have to remove a contact from myriad distro lists. Is there any way to pull this off without having to delete the contact from each list individually? I'm using Exchange 2003. Not that I know of - by the way, Exchange questions can be asked in = microsoft.public.exchange.admin. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Stockmoose16 asked: | Hi, |=20 | I manage...

exporting/saving recent files list
I'm getting a new computer. Is there a way to save the "recent files" list that is available in each of my Office programs? (Does anyone know where Office saves these shortcuts?) http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/bd58b46fe9c11ef9/853766524964c427?lnk=gst&q=recently+opened#853766524964c427 -- Gary''s Student - gsnu200909 "vabki" wrote: > I'm getting a new computer. Is there a way to save the "recent files" list > that is available in each of my Office programs? (Do...

Selecting Members for Distribution List
Am running Outlook 2007. I have a Contacts folder and other smaller folders. I have a Group distribution List named NWH in a folder named NWH contacts that I set up in 2003. I can delete from this list but not add to it, as the select members function only looks in the Contacts folder, even though I am looking at contacts in the NWH contacts folder when I select the NWH list to edit. ??? Thank you for any help. Claudette Too little information to permit an answer. What is a "Group distribution list?" Outlook does not use that term. How and where did you create ...

Drop down list 04-04-10
I have a drop down list with names in it. I need the names to disappear as they are being selected when you go down to the next row, so when you get to the last row there should only be one name left. Thanks. Take a look at this article from Debra Dalgleish's site: http://www.contextures.com/xlDataVal03.html Hope this helps. Pete On Apr 4, 1:50=A0am, Lost Cluster <LostClus...@discussions.microsoft.com> wrote: > I have a drop down list with names in it. I need the names to disappear a= s > they are being selected when you go down to the next row, so wh...

Filter List
I need to filter a list of data to be able to view each part and the customer for each part. The example below shows part 10P2345 appearing 4 times, with a different customer for each order, I need to see the part with each customer and other data also. How do I do this without using an Auto Filter? Part Order No. Serial No. Customer Ship date 10P2345 3553232 7992323 H. Potter 16/09/2005 10P2346 3553233 7992324 R. Malcolm 23/09/2005 10P2347 3553234 7992325 J. Peters 23/09/2005 10P2348 3553235 7992326 M. Henderson 24/09/2005 10P2349 3553236 7992327 M. Watters 23/09/2005 1...

Drop down list #3
Is there a command whereby I can create a drop down list in a cell? Data|Validation is pretty easy to use. Debra Dalgleish has lots of notes at: http://www.contextures.com/xlDataVal01.html Hrider wrote: > > Is there a command whereby I can create a drop down list in a cell? -- Dave Peterson ...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

Junk Sender's List
Is there a way to print the junk sender's list? This is Outlook 2002 and 1997. Thank you. Dana I don't think there is a way to do it. >-----Original Message----- >Is there a way to print the junk sender's list? This is >Outlook 2002 and 1997. > >Thank you. > >Dana >. > Yes, It is called "Junk Senders.txt" >-----Original Message----- >Is there a way to print the junk sender's list? This is >Outlook 2002 and 1997. > >Thank you. > >Dana >. > I am searching for that text file and so far have not found it. ...

Separate inboxes for separate accounts
How can I separate incoming e-mail from separate e-mail accounts into folders so I can tell where incoming mail came from? Using Outlook 2000. >-----Original Message----- >How can I separate incoming e-mail from separate e-mail >accounts into folders so I can tell where incoming mail >came from? Using Outlook 2000. >. > I'm a novice but I just ask that question and dl@spoofmail.com gave me the following advice: It is done with rules. 1st create your folders (sounds like you've done that.) Then select Tools / rules & alerts / New Rule... This brings up...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

Create list of worksheets
Can anyone help me with this problem. I have a workbook based on th attached workbook. It uses a macro to find data in worksheets with a 'x' in a column. What I need to do is , that when the the macro i clicked, it puts which sheet the data came from in column E in th special order worksheet. Can anyone help +------------------------------------------------------------------- |Filename: CheckSheetsOrders.zip |Download: http://www.excelforum.com/attachment.php?postid=3650 +------------------------------------------------------------------- --...

Separating strings in a field to separate fields
Hi, I have a field that consists of a string value that looks like Item1, Item2, Item3, each item is separated by commas. I want to separate that string value so that each item has its own field. Is there code that will easily do this? Thanks, Jaime On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jseger22@yahoo.com wrote: >Hi, > >I have a field that consists of a string value that looks like Item1, >Item2, Item3, each item is separated by commas. I want to separate >that string value so that each item has its own field. Is there code >that will easily do this? > >Thank...

How to change Exchange 2000 Global Address List / Distribution List
Does anyone know how to change the Global Address List / Distribution List in Exchange Server 2000 and/or 2003 We have user accounts that are no longer valid, but want to retain their mailboxes for a while before removing them. In the interim, we don't want their names to show on the distribution list Any help would be greatly appreciated, thank you You can hide them from the GAIL. Use Active Directory Users & Computers (make sure set to advanced view), and you'll find the setting on the Exchange Advanced tab of the user object. Be warned that doing this will prevent the use...

Default address list exchange 2003
I am organizing our address list and was wondering If I could remove the default lists that were created when we installed exchange e.g.... all user, all groups...... TIA Josh It should be no problem to remove the unwanted default lists, but why would you want to remove them? They create very little overhead (except perhaps during OAB generation) and the users may find them helpful. Tony www.activedir.org "Josh" <jsawyer@insightbb.com> wrote in message news:e7keQQ$OFHA.2700@TK2MSFTNGP10.phx.gbl... >I am organizing our address list and was wondering If I could ...

modifying global address list template
I am trying to add username to the global address list template so that when a search on a user it gives me there user list in the template. I try modyfying it in the exchange administrator/details template/english/user and nothing happens. The change appears, i press test, but when I look at it from my machine, nothing. I also need to add a field to the search box "username" so I can search on it. Any clue, any body ou there? ...

Sorting a list of elements
How do I sort a list of elements by their field values? E.g. Mylist contains 10 customer structures. How do I sort the list by customer's age. Note that the customer structure contains a age field. Any ideas? Hi, "Charles Tam" <CharlesTam@discussions.microsoft.com> schrieb im Newsbeitrag news:5C133BE2-D87F-4BB6-B36B-43199D30E771@microsoft.com... > How do I sort a list of elements by their field values? > > E.g. Mylist contains 10 customer structures. How do I sort the list by > customer's age. Note that the customer structure contains a age field....

Launch Oulook Express Newsreader separately
Hi I use Outlook Express as my newsreader. My problem is that in order to access it, I need to first launch Outlook. Is there some way I can launch the newsreader portion of OE without launching Outlook? A command line switch or something I imagine. I'm using Outlook XP with OE 6 on Windows XP pro. Thanks I believe the command-line switch is /newsonly. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "aJax&...

convert a list to count by county
>From what I'm hearing, they say Excel is limited only by one's imagination. I can't imagine how to do this, so perhaps someone would be willing to help me! I've got a list of 700 county names. How hard would it be to convert that list to a count by county. In other words, when all is said and done, I will know how many entries I have for each county. In the past, I've used SAS - I'm trying to reduce my dependence on this software and learn Excel! Mike Take a look at Pivot Tables. See http://peltiertech.com/Excel/Pivots/pivotstart.htm for a good starting...

Returning a value if data is in a large list
I think you use a IF function but I am not sure. I have two large lists of numbers. The lists are in columns A and C with columns B and D blank. In B, I want to insert an "X" if the number in column A next to it is somewhere in column C. I tried =IF(A2=C:C,"X","") and =IF(A2=C2;C1000,"X","") but they do not work. Is there a way I can do this? One way: =IF(COUNTIF(C:C,A1)>0,"x","") Another way: =IF(ISNUMBER(MATCH(A1,C:C,0)),"x","") I like the second way. It'll be faster for larger lists ...

Exchange 2003 GAL address list new user not showing up
I have somewhat of a difficult problem to troubleshoot here... Heres the scenario: 1) I have a existing user that I've tried to setup with an email account. The email account was created no problem and the user has a proper SMTP address. (This indicates that the recipient update service is working) 2) I can send the user email using his full email address, however the user is not in the GAL. Once I sent him an email, his mailbox populated into the private information store. 3) This is *not* an cached exchange mode issue where I need to download the latest gal. I have done this seve...

Multilevel lists 05-12-10
I have never had so much trouble creating a multi-level list and table of contents than with Word 2007. I can not control the numbering in outline and get a table of contents. The numbering does not seem to continue from the previous number no matter how many times I tell it to. It jumps back and start at 1 even though it has multiple sub levels attached. Wow. It totally unituitive. TheWizEd;468945 Wrote: > I have never had so much trouble creating a multi-level list and table > of contents than with Word 2007. I can not control the numbering in > outline and g...

Inventory Item List Not Populating, Generating SQL Errors
When any user tries to view the Items list under the Inventory category in the sidebar, the following warning appears: [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'ATYALLOC', table 'tempdb.dbo.##2411735'; column does not allow nulls. INSERT fails. After clicking ok, the list never populates. All of our other lists are working correctly, except for this one. What can be done to enable this list's functionality? What version of GP? What Service Pack or build number? Did this feature ever worked before? Did you just noticed thi...

Removing Senders from the Blocked List 06-16-10
In Widows Live Mail I go to Tools, Safety Options, Blocked Senders and highlight senders I want to unblock, remove from the list. They do disappear from the list and seem to be removed, but after a short while they are back again. This has happened in the time that it took me to type this. Does anyone know what may be malfunctioning? The same think happens if I try online at the website. Any assistance will be much appreciated. That list is saved in the registry. Any overzealous security program that prevents writes to the registry can cause changes to not be saved. Gary ...