Creat a VBA or formula for (get person name next to the activity)

[captain321]

This is my original data, this data has more than thousand names and hundred activity. Maybe a VBA function will really help.

Name/Activity:	Prod %	Date
Smith (12345)	Attn:
walk	76	4-Mar
clean	45	3-Mar
Name/Activity:
John (23456)
Name/Activity:
Rose (1314)	Attn:
camp	43	8-Aug
walk	65	7-Jul
run	78	6-Jun
clean	76	8-Feb
drive	45	4-Apr
Name/Activity:
Lucy (34567)	Attn:
food	23	2-Feb
clean	45	8-Aug

<tbody>
</tbody>

This is the data formatted I need, In this way, I can pivot and compare them.

Name	Name/Activity:	Prod %	Date
Smith (12345)	Smith (12345)	Attn:
Smith (12345)	walk	76%	4-Mar
Smith (12345)	clean	45%	3-Mar
	Name/Activity:
John (23456)	John (23456)
	Name/Activity:
Rose (1314)	Rose (1314)	Attn:
Rose (1314)	camp	43%	8-Aug
Rose (1314)	walk	65%	7-Jul
Rose (1314)	run	78%	6-Jun
Rose (1314)	clean	76%	8-Feb
Rose (1314)	drive	45%	4-Apr
	Name/Activity:
Lucy (34567)	Lucy (34567)	Attn:
Lucy (34567)	food	23%	2-Feb
Lucy (34567)	clean	45%	8-Aug

<tbody>
</tbody>

Thank you so much for help!

 

[hiker95]

captain321,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C
1	Name/Activity:	Prod %	Date
2	Smith (12345)	Attn:
3	walk	76	4-Mar
4	clean	45	3-Mar
5	Name/Activity:
6	John (23456)
7	Name/Activity:
8	Rose (1314)	Attn:
9	camp	43	8-Aug
10	walk	65	7-Jul
11	run	78	6-Jun
12	clean	76	8-Feb
13	drive	45	4-Apr
14	Name/Activity:
15	Lucy (34567)	Attn:
16	food	23	2-Feb
17	clean	45	8-Aug
18
Sheet1

After the macro (using two arrays in memory) in a new worksheet Results:

Excel 2007
	A	B	C	D
1	Name	Name/Activity:	Prod %	Date
2	Smith (12345)	Smith (12345)	Attn:
3	Smith (12345)	walk	76%	4-Mar
4	Smith (12345)	clean	45%	3-Mar
5		Name/Activity:
6	John (23456)	John (23456)	Attn:
7		Name/Activity:
8	Rose (1314)	Rose (1314)	Attn:
9	Rose (1314)	camp	43%	8-Aug
10	Rose (1314)	walk	65%	7-Jul
11	Rose (1314)	run	78%	6-Jun
12	Rose (1314)	clean	76%	8-Feb
13	Rose (1314)	drive	45%	4-Apr
14		Name/Activity:
15	Lucy (34567)	Lucy (34567)	Attn:
16	Lucy (34567)	food	23%	2-Feb
17	Lucy (34567)	clean	45%	8-Aug
18
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 09/25/2014, ME807964
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, na As String
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  a = .Range(.Cells(1, 1), .Cells(lr, 3))
  ReDim o(1 To lr, 1 To 4)
End With
j = j + 1
o(j, 1) = "Name"
o(j, 2) = "Name/Activity:"
o(j, 3) = "Prod %"
o(j, 4) = "Date"
For i = 2 To lr
  If a(i, 1) = "Name/Activity:" Then
    j = j + 1
    o(j, 2) = "Name/Activity:"
  ElseIf InStr(a(i, 1), " (") Then
    j = j + 1
    na = a(i, 1)
    o(j, 1) = na
    o(j, 2) = na
    o(j, 3) = "Attn:"
  Else
    j = j + 1
    o(j, 1) = na
    o(j, 2) = a(i, 1)
    o(j, 3) = a(i, 2) & "%"
    o(j, 4) = Format(a(i, 3), "d-mmm")
  End If
Next i
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Worksheets("Results")
With wr
  .UsedRange.Clear
  .Cells(1, 1).Resize(lr, 4) = o
  .Columns(1).Resize(, 4).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[captain321]

I use Windows 7 and Excel 2010. Thank you so much for you help. It works like a charm.

 

[hiker95]

captain321,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.