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
746 Views

Similar Articles

[PageSpeed] 33

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:

Exchange server on MAPS RSS list
My Exchange 2000 server is on blacklisted on RSS. Whenever I run a open relay test from any website other than RSS it shows my relay is closed. My question is why is it that RSS shows my server as an open relay and how do I correct this? You could be on a BL for a number of reasons. Some RBL owners are stricter than others. It does not mean they are right, but it is their database/server and they wield the power to decide what they will block you for and for how long. If you are willing to provide your MX info, we can help you find out if you are truly open relay or not. -- Sincerely, D�j...

i cant display field list in pivot table
i cant display the field chooser in pivot table What version of Excel are you using? Do you see the field list for other pivot tables? Cesar wrote: > i cant display the field chooser in pivot table -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

File Error: Using Sharepoint WSS lists in Excel 2003
Hi, I have a Windows Sharepoint Services 2007 list I export to Excel 2003 (SP3 11.8206.8202). Sharepoint creates an .iqy file and I open it in Excel. This creates a List range in the spreadsheet that is tied to the Sharepoint List (I make batch updates to the list, right-click and synch the list with the server, works fine). I save the workbook to my PC as .xls. When I open it later I get the error "File Error: Data May have been Lost", the list data is still there but the range is not i.e. the list range no longer has a blue border around it and I can no longer right-click a...

Make a formula
Is it possible with Excel 2000 to automatically make a list of all the formulas you are using in your spreadsheet to print them. Thanks...Steve -- Steven E ----------------------------------------------------------------------- Steven E.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1512 View this thread: http://www.excelforum.com/showthread.php?threadid=26758 This subroutine makes a new sheet (Formulas in Sheen N) in which all formulas are listed together with cell reference and value. Not mine-own but I have lost the reference to the author but I thank h...

Removing in column B when listed in Column A
Hi I have a list of text in column A aaaa bbbb ccccc dddd eeee ffff etc.... I also have a list in column B zzzz ccccc xxxx aaaa ffff gggg etc What I need to do is remove all the entries in colum a from column B In the example above I would have in column B left zzzz xxxx gggg Hope this makes sence and someone can help. Thanks Iai -- Message posted from http://www.ExcelForum.com Iain, I assume your colums A en B are filled with values A1:A10 and B1:B10 Put in C1 the formula : If(ISNA(VLOOKUP(B1,$A$1:$A$10,1,0)),0,1) and copy down to C10. Copy and paste special / values column...

sharing sub directory contact lists
I have a problem where if a make a new contact list under the default conacts and share the list and give permissions for a user to access it, when the user tries to open the list using outlook, open another users data, I get a message saying the oject cannot be found. If I share the default contact list and try and access it it works fine. This is the same for inboxs as well does anyone know why this is and can I fix it? Jake In news:3D1F7F26-CD1A-4BAB-B9D1-501C9394E307@microsoft.com, Jake <Jake@discussions.microsoft.com> typed: > I have a problem where if a make a new cont...

Selected items from List Box sent to Form Field
When items are selected from a list box (frm!List52), they currently populate another list box (frm!List56) but I would like the selections made to also update a field (T_assigned) on the form. Currently frm! List56 is populated with first name and last name data - Columns 1 and 2 - I am trying to get the selected ID_NUMs from Column 0 into the T_assigned field. Any help or guidance would be greatly appreciated, thanks. ______________________________________________ Public Sub CopySelected(ByRef frm As Form) Dim ctlSource As Control Dim ctlDest As Control Dim strItems As String...

get list of used namespaces
Hi all, I load an xml file into XmlDocument object and would like to get a list of namespaces used in the file. How can I get a list of namespaces used in xml file? Thanks. helpful sql wrote: > I load an xml file into XmlDocument object and would like to get a list > of namespaces used in the file. How can I get a list of namespaces used in > xml file? XPath has a namespace axis for namespace nodes which are in scope for a given element node, here is an example using an XPath expression with SelectNodes to output all namespace nodes (those explictly given and th...

Offline Address List Fun
Hi All, Here's hoping some of you exchange gurus can help me troubleshoot a rather interesting problem. We have a couple of Outlook 2003 users who are having trouble with their Offline Address List in cache mode. Things are all good and dandy when they are not in cache mode but once they turn cache mode on they would not download the latest offline address list even if they are forced to. The server has the latest OAL since I am also running in Cache mode and I am able to get everybody's user info ( and I made sure I am using the local OAB files ). I deleted his local OABs but wh...

What's the deal with List View ignoring LVIF_STATE in OnLvnGetdispinfo Microsoft, please comment !
Hello all. I have whis wierd problem with list view ignoring LVIF_STATE in OnLvnGetdispinfo message handler. It is a custom drawn virtual list. When the list is initially displayed, I'm not getting any LVIF_STATE requests.for the items displayed, only when I scroll the list I start getting it, but list ignores it flat-out. Also, how come I'm not getting any OnLvnSetdispinfo messages? I've run into so many incosistencies when dealig with the virtual custom drawn list that's quickly becoming the nightmare. Here's the current bug list: 1. Keyboard selection bug: when ...

Removing Senders from the Blocked List 07-12-10
For months I have been unable to remove blocked senders. I have tried from Windows Live Mail and from the Windows Live Hotmail website. After a short while they reappear. If someone can tell me what I must do, or if this can be resolved remotely, I will be most appreciative. It is most frustrating and I have been unable to receive any assistance. PLEASE HELP! ...

print list of all styles in document
I want to view/print a single list showing ALL of the styles being used in a Publisher document, so I can see the font names, sizes and other characteristics in order to help keep a consistent look and feel in the document. I can look at only one at a time and would have to either print the screen image or write it down to get the full list; one way wastes a lot of paper/toner, the other seems terribly inefficient. Is there an easy way? ...

Excel VBA
Hi Excel VBA advisers! I have a userform where there are two text boxes (drugdesc1 & drugdesc2) that I want to force 4 commas to be keyed into both fields. 4 commas are MANDATORY in each text box & are used to separate the type of input within each. ie; brand name, ingredients, strength, form, pack size. (FYI: Later on, I split these fields into 4 separate columns based on the comma delimiter.) When "cmdAdd" command button is clicked, I need to display a msgbox to advise user and disallow the add. I imagine that the code (repeated for each text box) that I would put in th...

View blocked senders list
I blocked a sender (don't recall the name) and would like to view the blocked sender list. How do I do that? When I click on ? in WLM for Help and click on "get help with mail" I get "We can't find that page" notification. Tools | Safety Options | Blocked Senders. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Panic" <wrong@cox.net> wrote in message news:i3q110$8jm$1@speranza.aioe.org... >I blocked a sender (don't recall the name) and would like to view the >blocked sender list. Ho...

Update Several Records with separate table data
I have two tables. One has all fields complete. The other has some complete records, but most only have one field completed. I want to input the complete records to the incomplete table, merging any records that would be duplicated. I forgot to mention this in my first post. Why are you duplicating data in your database? The goal of a well designed database is to only store the same piece of information once. Dale -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: > In Access (am unsure whether A2007 supports this), you can do this as a ...

Adding to a list of codes
Hi In Excel 2003 I have a spreadsheet listing a large number of cost centres. Each month I am given a list of the cost centres and expenditure against them, which I want to use a look up to populate in to a master spreadsheet. However, during the month new cost centres could have been created. What I would like to be able to do is run a macro against the monthly return and compare the codes contained in it to the codes already listed in the master sheet. If there are any new codes not already in the master sheet I would want them added to the existing list so that the mast...

removing store from store list from HQ
i need to remove a store from the list of stores in HQ - the stores have been closed for 2 years, and i would like to clean up the check stores feature at other store locations, have less to sort in reports, etc. i have already removed them from the store list in HQ Manager under stores, but they still show in reports and in check stores. is there a way to remove them from either or both? ...

OL2000 disabling inplace editing for contact lists and task lists, etc #3
hi Can someone please help me with this one: I manage a small SBS2000 instalation (20 users) running outlook 2000 with exchange 2000. Outlook 2000 has the annoying feature that whenever you click on an item in a task list or contact list and start typing, you modify the contens of the item (without noticing). This happens often in the company and has recently resulted in inadvertedly destructing critical information contained in public contacts and tasks folders. I would like to know if there is a way to disable this in place editing feature, making changes to items only possible if you doub...

RECENTLY USED FILE LIST #3
The RECENTLY USED FILE LIST is greyed out How to activate? Go to TOOLS / OPTIONS... If the 'Recently Used File List:' checkbox is uncheck, check it. HTH, Gary Brown "bvanepps" wrote: > The RECENTLY USED FILE LIST is greyed out How to activate? The box is not available, that is the problem. Thanks "Gary Brown" wrote: > Go to TOOLS / OPTIONS... > If the 'Recently Used File List:' checkbox is uncheck, check it. > HTH, > Gary Brown > > "bvanepps" wrote: > > > The RECENTLY USED FILE LIST is greyed out How to activ...

incorrect details in mailing list
hello, a user has phone number and other details in aduc. When i go to GAL and select this user, all his details show. As do mine. Then when i select a mailing list from the GAL. (in a parent domain) My name and the users names are there. All my details show up. The others users phone number, and everything else are missing. The only details that are there for him is his name. Now everyone will firstly say that this is a Syncing issue. This cannot be. This user has been at the company for many years. I havn't, and my details are correct and his arent. Any ideas what causes this?...

Can I have the right formula to list all my agencies under country
A B C A1 COUNTRY AGENCY RESULT FRANCE A2 FRANCE MAURITOURS W.S.TOURS A3 FRANCE MAURITOURS CONCORDE A4 FRANCE MAURITOURS MAURITOURS A5 FRANCE W.S.TOURS ETC A6 FRANCE W.S. TOURS A7 FRANCE CONCORDE A8 GERMANY KUONI Experiment with Data | Filter When you filer on say France, you can copy and paste the agencies to an...

Why Excel process is still there on Task Manager list
Hi all, My project is about pick up info from individual workbook, and merge the info into a new workbook. But in the end of code, even I set nothing to workbook and application object, but the Excel process is still there, but the way the open and close procedure is very slow, is there any way to expedite it? Dim SrcWrk As Workbook Dim DstWrk As Workbook Dim app As New Application DstWrk = app.Workbooks.Add() For Each File As String In Directory.GetFiles(Me.SrcDir, "*.xls", System.IO.SearchOption.AllDirectories) ...

Alert when consecutive numbers in a list exceed 5
Good morning I have thousands of records, and need to be alerted when, consecutive numbers (in a particular column) exceed 5. It has to be consecutive numbers (not sorted). eg data 1 data 1 data 8 data 8 data 8 data 8 data 8 data 8 In this case the number 8 exceeds 5. Would really appreciate help on this, as at the moment it is really time consuming. Thanking you in anticipation. Have you thought about using conditional formatting for this? Select the cells with the numbers you want to compare. Format -> Conditional Formatting Condition 1 -> For...

SPAM List
Hello, Looks like were placed on a block list as a result of a message that was sent out between 4:30pm and 5:00pm yesterday. I have asked around and all staff say that they have not sent out anything questionalble. We don't seem to have any virus' or risks detected by our corprate Antivirus solution. My question is, is there any way to use the tracking features in exchange to try and pinpoint who/what was sent out that triggered adding our IP address to the block list. Thanks For Exchange 2003, in case you have the Message Tracking feature enabled on the server that send...

Address labels list
Where do i start to look for making an address label list Will this list be used for Mail Merges? If so: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm These are good places to get information. tj "sally" wrote: > Where do i start to look for making an address label list Good place to start AND finish! http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ===============================...