I have an enormous list sent to me of subcontractors who work for different companies and each employee is listed alphabetically within their own company. Each employee's details are in several columns across 1 row.
Column A is the company name but is listed ONLY once for the first employee. The remaining employees have a blank cell in A. The next row with text in Column A is the first line of the next company and the pattern repeats.
My problem is that I need to create ONE alphabetical list of all people with every person's company listed in the A column.
I was hoping to create a macro that would copy A2 down however many rows that are blank until it reaches the next cell with text, then proceed to copy the next company text down the column for however many employees it has etc.
Bob's Bikes Tom
Sara's Kayaks Mary
Bob's Drones Todd
Kevin's Canoes Marti
Even though this is not a spreadsheet - hopefully it will give you an idea of what I'm referring to.
Click on the Name column, and SORT by ALPHA! No need for a macro. If it says that there is other data not selected, say YES select that data too! If you say no, the name only will be moved, and the the rest of the columns will not be sorted with the original record!
Thanks for the response but unfortunately it does not produce the result I need. Also, the format of my original note did not stay the same when I hit enter. Imagine this: Column A has the company names, Column B the employee's last names. Only the first employee in the list of 15 people indicates the name of the company and the remaining employees have a blank cell for their company name because it is easy to see who they work for since they're all together. My problem is that my list has over 50 companies and I would like to have an alphabetized list of all employees based on last name. If I sort the list as is - I have tons of people with no company name and I don't know who they work for. I need to be able to copy down the company names throughout my list so when I sort it by last name each individual has their company listed. Does this make more sense?
Dim x, lRow As Long
lRow = Range("B1").End(xlDown).Row
For x = 2 To lRow
If Cells(x, 1) <> vbNullString Then
Application.CutCopyMode = False