Help Needed : Re-arranging mixed up First Names and Surnames into the correct columns

[Helen842000]

Hi all,

I'm looking for some help that would probably save weeks of work for me even if a little progress is made. I have a spreadsheet that is as follows (around 7000 rows) Some demo data below

---A--------- B ----------C
USERID ----Name1 -----Name2
BSMITH----- BOB ------SMITH
AJONES ----JONES ------ANN
AAHMED---- AHMED -----ALI
SJOHNS ----SIMON ----JOHNSTON
JLIAAA -------LI ---------JOHN


As you can see the names are in a mixed up order. Sometimes it's (surname, first name) sometimes it's (first name, surname) This is down to people typing names in the wrong format. I was wondering if I could use their user ID to some how arrange the names in some uniform way. It would be very difficult to do this manually as with some users it's very difficult to discern which is their first name or surname until I look at their User ID (as the example shows in row 3)

The User ID is always 6 characters and follows the format FIRST NAME INITIAL + FIRST 5 CHARS OF SURNAME. If someones surname isn't at least 5 chars long, letter A's are appended to the ID to make up 6 letters (as shown in example 5).

Even any suggestions of what combined functions might bring me a little closer would be really appreciated.

Thank you!

 

[Domski]

Hi,

Assuming your sample is in A1:C6 then use this:

For surnames...In D2 copied down: =IF(ISNUMBER(SEARCH(MID(A2,2,MIN(LEN(B2),5)),B2)),B2,C2)
For forenames...In E2 copied down: =IF(ISNUMBER(SEARCH(MID(A2,2,MIN(LEN(C2),5)),C2)),B2,C2)

Dom

 

[Helen842000]

This is how I'm thinking it could possibly be done if I can get some assistance with use of the right functions & with corrected syntax. :D

Just writing it out as a pseudo formula as it springs to mind.

I would be trying to build a new column of surnames that would go in column D.

IF CHARS 2 & 3 of USERID (A2) MATCH EITHER (CHARS 1&2 OF NAME1 (B2) OR CHARS 1&2 OF NAME2 (C2)) THEN PRINT FULL CELL CONTENTS FROM MATCHING CELL INTO D2

 

[patel45]

Sub a()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lastrow
NameFirst = Left(Cells(j, 1).Value, 1)
Surname1 = Right(Cells(j, 1).Value, 5)
Surname2 = Left(Surname1, 2)
If NameFirst = Left(Cells(j, 2).Value, 1) And Surname2 = Left(Cells(j, 3).Value, 2) Then
' MsgBox "OK"
Else
b = Cells(j, 2).Value
Cells(j, 2).Value = Cells(j, 3).Value
Cells(j, 3).Value = b
End If
Next
End Sub

 

[Helen842000]

Thank you SO much Domski.

This works great! There are a handful where Surname is repeated twice but on closer inspection it's because there's something unusual about the first name (eg punctuation marks, hypenated names) but this allows me to pick out the few rows that needed amending anyway!

You have just saved me a LOT of work. Thanks again.

 

[Domski]

Glad it helped

Dom