Matching Data from One Column to Another and Deleting Extra Rows

[FrenchCelt]

Hello,

I'm trying to finish up a macro by taking data (system username) from Column A and aligning it with the first entry in another column to contain that data to make a report easier to read.

This is what it looks like now:

And this is how I want it to look:

Basically I want the username from Column A to match up with the first entry for that name in Column B and then delete the subsequent entries, because the only one that matters is the earliest date in the sequence for each person.

Can anyone help me out?

 

[Peter_SSs]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Using XL2BB will generally get you faster and better answers as many helpers will just bypass your question rather than manually type out data to test with.

Try this with a copy of your data.

Sub MatchFirstRow()
  Dim Emp As Variant, EmpName As Variant
  Dim ErrRng As Range, NameFound As Range
  
  Emp = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
  Range("A2", Range("A" & Rows.Count).End(xlUp)).ClearContents
  Set ErrRng = Range("B1", Range("B" & Rows.Count).End(xlUp))
  For Each EmpName In Emp
    Set NameFound = ErrRng.Find(What:="*:" & EmpName & " *")
    If Not NameFound Is Nothing Then NameFound.Offset(, -1).Value = EmpName
  Next EmpName
  ErrRng.Offset(, -1).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[FrenchCelt]

Brilliant, that worked perfectly.

I would use that XL2BB add-in, but I post these at work and I never received permission from my IT Analyst to install it. I'll send another request.

 

[Peter_SSs]

Brilliant, that worked perfectly.

You're welcome. Glad to help.

I would use that XL2BB add-in, but I post these at work and I never received permission from my IT Analyst to install it. I'll send another request.

If your request is denied, second best option because we can still manage to copy is a direct Copy/Paste like this

Emp	Error
Emp1	Stuff :Emp1 and more 1
Emp2	Stuff :Emp1 and more 2
Emp3	Stuff :Emp1 and more 3
	Stuff :Emp1 and more 4
	Stuff :Emp1 and more 5
	Stuff :Emp1 and more 6
	Other Stuff :Emp2 and more 1
	Yet more Stuff :Emp3 and more 1
	Yet more Stuff :Emp3 and more 2