• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

mapping data

krishkrpal

New Member
i need query which can map data of each user who attened different Program(All Programs are in one cell per user) to be mapped to each user attending each Program.
Example:

Name Ph email Program

N1 Ph1 E1 P1-1,p1-2..p1-10
N2 Ph2 E2 P2-1,p2-2..p2-9
. . . .
N5000 Ph5000 E5000 P5000-1,P5000-2..P5000-19

Map the data to other sheet

N1 Ph1 E1 P1-1

N1 Ph1 E1 P1-2
. . . .

N2 Ph2 E2 P2-
.
N5000 Ph5000 E5000 P5000-19

Please let me know how this can be done


Please find the attached sample data
 

Attachments

Peter Bartholomew

Well-Known Member
I believe that far and away the best solution to your problem is to use Power Query. That will allow you to split the column, first to separate rows on 'comma' then to columns on 'hyphen'. That gives the table as you show it on Sheet2.

Personally I would consider taking the process a step further and creating three tables. The first would be the list of attendees, the second a list of courses. The central table would record the training as an event. Each record would show the date, a pointer to the course table and a pointer to the attendee. That means that each set of personal details need be entered once as a master copy.
 

p45cal

Well-Known Member
You can try running this macro AFTER selecting the relevant range, which in the case of your sample sheet is Sheet1!F2:F10.
Code:
Sub blah()
Set myRng = Selection
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set Destn = NewSht.Cells(1)
Destn.Resize(, 6).Value = Array("First Name", "Last Name", "Phone", "email", "Original Program", "Original Date")
For Each cll In myRng.Cells
  x = Split(cll.Value, ",")
  For Each prog In x
    y = Split(prog, "-")
    Set Destn = Destn.Offset(1)
    Destn.Value = cll.Parent.Cells(cll.Row, 1).Value
    Destn.Offset(, 1).Value = cll.Parent.Cells(cll.Row, 2).Value
    Destn.Offset(, 2).Value = cll.Parent.Cells(cll.Row, 3).Value
    Destn.Offset(, 3).Value = cll.Parent.Cells(cll.Row, 4).Value
    Destn.Offset(, 4).Value = y(0)
    Destn.Offset(, 5).Value = y(1)
  Next prog
Next cll
End Sub
It adds a new sheet, puts the headers in and fills the rest in.
 
Top