+ Reply to Thread
Results 1 to 7 of 7

Distribute names to affiliations

  1. #1
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Distribute names to affiliations

    Dear All,
    In rows 1 to 4 and sometimes 1 to 5, I have names each associated with a number(s) in brackets.

    In line 9 and bellow a list of universities and research institutions representing the numbers after the names of authors.

    I need a VBA that can distribute (Copy) the names in rows 1-to 5 into their respected organization (line 9 and bellow).

    Please note that some names are associated with more than on organization (E.g. C1 or D2, B3 shaded in green) in this case, Ideally the same name should appear in front of each organization he/she belongs to.

    Alternatively a formula that I can copy down and across will do.

    Please see the attached file of many that I have to deal with,

    Many thanks and appreciation in advance
    Attached Files Attached Files

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Distribute names to affiliations

    Hi Taisir,
    try this
    Please Login or Register  to view this content.

  3. #3
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Distribute names to affiliations

    Try this:-
    Results start "C9"
    NB:- Somewhere in those first 5 rows there are text strings that do not have a Bracketed number !!!!!
    This code only allocates those that do !!!
    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 12-21-2014 at 02:45 PM.

  4. #4
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Distribute names to affiliations

    Dear Nilem
    Dear MickG

    Many thanks for the codes. Both worked very well. I really appreciate that.

    yes MickG, I need to clean the data a bit before applying the code.
    All the best for both of you.

    I will go a head and mark the thread solved. Taisir

  5. #5
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Distribute names to affiliations

    Dear MickG

    Can the VBA be adjusted to run for the whole workbook with many sheets that are identical in structure?

    Also, I could not figure out the reason why the VBA (re-pasted bellow) worked in Sheet1 and failed in Sheet2 and Sheet3 in the attached, and gives the following error

    Run-time error '9'" subscript out of range.

    All the best

    Sub MG21Dec38
    Dim Rng As Range, Dn As Range, n As Long, Txt As String
    Dim sp As Variant, S As Variant
    Dim oMaxC As Long, oMaxR As Long
    Set Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft)).Resize(5)
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    ReDim ray(1 To 260, 1 To 260)

    For Each Dn In Rng
    If InStr(Dn.Value, "[") > 0 Then
    Txt = Trim(Split(Split(Dn.Value, "[")(1), "]")(0))
    sp = Split(Txt, ",")
    For Each S In sp
    If Not .Exists(S) Then
    .Add S, 1
    ray(Int(S), .Item(S)) = Dn
    Else
    .Item(S) = .Item(S) + 1
    ray(Int(S), .Item(S)) = Dn
    oMaxC = Application.Max(oMaxC, .Item(S))
    oMaxR = Application.Max(oMaxR, Int(S))
    End If
    Next S
    End If
    Next
    Range("C9").Resize(UBound(ray, 1), oMaxC) = ray
    End With
    End Sub
    Attached Files Attached Files
    Last edited by Taisir; 12-24-2014 at 07:23 AM. Reason: Missing Attachment

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Distribute names to affiliations

    Try this:-
    Its assumed that the first 5 rows contain the Basic data and the results will Start in cell "C9".
    NB:- 10 sheets took 6 Secs
    Please Login or Register  to view this content.
    Regards Mick

  7. #7
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Distribute names to affiliations

    Dear MickG
    Many thanks again for the VBA.
    Happy Holidays

    All the best

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Please Help. Its hard for me to distribute names manually and Need this automated.
    By mikaelkian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2014, 01:14 AM
  2. [SOLVED] Distribute VBA Add-In
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2014, 03:13 PM
  3. Replies: 3
    Last Post: 12-09-2013, 04:21 AM
  4. Replies: 1
    Last Post: 05-14-2012, 10:14 AM
  5. distribute names equally in front of numbers
    By rahulbawkar2006 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2011, 06:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1