Newbie Here. Help Converting data to multiple columns.

[steveblaine]

I'm having trouble figuring this out. I need to capture the Name and Company on a separate sheet.

A new record seems to start at the row 'New'.
The 'Name' seems to be always the 5th row below the row 'New'.
The 'Company' seems to be always after the word 'at'.

Here is the raw data.. What is looks like BEFOREHAND.

New

4 hours ago

Send InMail

Ask for review

Update me about Doug Wilbur

Doug Wilbur

CEO, co-founder at Stix

Jordan

New

4 hours ago

Send InMail

Ask for review

Select Charlie JonesidUpdate me about Charlie Jones

Charlie Jones

3rd

Owner at Compuwear

Orange County, California Area

New

4 hours ago

Send InMail

Ask for review

Select Louis RichardsUpdate me about Louis Richards

Louis Richards

3rd

CEO/Founder at Beckman Sounds

Orange County, California Area

New

4 hours ago

Send InMail

Ask for review

Select Emma WonsonUpdate me about Emma Wonson

Emma Wonson

CEO - President at Go Health Solutions, Inc

Greater Philadelphia Area

<tbody>
</tbody>

Here is What I Want it to look like.

Name	Company
Doug Wilbur	Stix
Charlie Jones	Compuwear
Louis Richards	Beckman Sounds
Emma Wonson	Go Health Solutions, Inc

<tbody>
</tbody>

VBA code would be appreciated.
Thank you.

 

[steveblaine]

I am using Excel 2013 on Windows 7 Home.

 

[hiker95]

steveblaine,

Thanks for the Private Message.

Here is a macro solution for you to consider that is based on your two flat text displays, and, that uses two arrays in memory, and, should be fast.

You can change the raw data worksheet name in the macro.

The macro will create a new worksheet, Results.

Sample raw data:

Excel 2007
	A
1	New
2	4 hours ago
3	Send InMail
4	Ask for review
5	Update me about Doug Wilbur
6	Doug Wilbur
7	CEO, co-founder at Stix
8	Jordan
9	New
10	4 hours ago
11	Send InMail
12	Ask for review
13	Select Charlie JonesidUpdate me about Charlie Jones
14	Charlie Jones
15	3rd
16	Owner at Compuwear
17	Orange County, California Area
18	New
19	4 hours ago
20	Send InMail
21	Ask for review
22	Select Louis RichardsUpdate me about Louis Richards
23	Louis Richards
24	3rd
25	CEO/Founder at Beckman Sounds
26	Orange County, California Area
27	New
28	4 hours ago
29	Send InMail
30	Ask for review
31	Select Emma WonsonUpdate me about Emma Wonson
32	Emma Wonson
33	CEO - President at Go Health Solutions, Inc
34	Greater Philadelphia Area
35
Sheet1

And, after the macro in a new worksheet Results:

Excel 2007
	A	B
1	Name	Company
2	Doug Wilbur	Stix
3	Charlie Jones	Compuwear
4	Louis Richards	Beckman Sounds
5	Emma Wonson	Go Health Solutions, Inc
6
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 Extract_Name_Company()
' hiker95, 04/13/2017, ME10000506
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, i As Long, ii As Long, n As Long, s, ss As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Worksheets("Results")
wr.UsedRange.Clear
With w1
  a = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
  n = Application.CountIf(.Columns(1), "New")
  ReDim o(1 To n + 1, 1 To 2)
End With
j = j + 1: o(j, 1) = "Name": o(j, 2) = "Company"
For i = 1 To UBound(a, 1)
  If a(i, 1) = "New" Then
    j = j + 1: o(j, 1) = a(i + 5, 1)
    For ii = i + 5 To i + 10
      If InStr(a(ii, 1), " at ") Then
        s = Split(a(ii, 1), " at ")
        o(j, 2) = s(1)
        i = ii
        Exit For
      End If
    Next ii
  End If
Next i
With wr
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, 2).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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Extract_Name_Company macro.

 

[steveblaine]

Thanks Hiker95! That was Genius! The Macro worked as you described and its also fast!

 

[hiker95]

steveblaine,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.