PDA

View Full Version : Changing first name to initial only, adding second initial and combining repeats



krackers
03-01-2020, 06:48 AM
I have a large sheet of names and addresses. Names are in the form of first names and second names and then address details in same row.

Some entries are for married couples but each individual has their own line of data with address details repeating along the line of data for a married couple.

I have a need to produce a mailing list showing only the persons initial of their first name and combining into one line where married couples exist.

There are entries of the same name but not a married couple. A married couple can be assumed if both the surname and address details match.

I attach a sample sheet showing what I am trying to achieve. Sheet 1 shows how the data is stored, sheet 2 shows what I am trying to achieve.

The whole process of producing the list is via a cmdbutton and as data changes each month and the mailing list is needed every other month, I want to achieve this using vba code attached to the cmdbutton.

I have had several attempts at getting started but not getting anywhere fast - can someone please point me in the right direction. I'd be most grateful for your help.

Many thanks,

David

snb
03-01-2020, 08:55 AM
Please consider individuals as individuals and not as part of a 'couple'.

krackers
03-01-2020, 09:00 AM
Please consider individuals as individuals and not as part of a 'couple'.

I do not understand what you are trying to say?

krackers
03-01-2020, 10:21 AM
The code I was trying out in order to get things going is below. This starts by adding the initial only for each first name. (NB. Here I was passing the result to another column in the same sheet and not into a second sheet as my example. The first name being in column 3 and passing it to column 2:



Dim m As String
Dim x As Long
Dim z As Long
Dim y As Long


x = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
z = 2
For y = 2 To x
m = Left(Worksheets("Sheet1").Cells(y, 3).Value, 1)
Worksheets("Sheet1").Cells(y, 2).Value = m
z = z + 1
Next