mapping data

[krishkrpal]

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

 

[Peter Bartholomew]

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]

You can try running this macro AFTER selecting the relevant range, which in the case of your sample sheet is Sheet1!F2:F10.

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.