I am looking for a formula for an "if / then" scenario. I have a
database where each record is duplicated based on a change in
the data in one field. For example, if the person has 3 different phone
numbers, then the subject's record would appear 3 times, with the only
difference between each record appearing in the phone number column.

I am trying to write a formula stating if the person's name is the same,
then merge the phone number fields. I have tried several different things
(including pivot tables, which don't work because there are too many
columns/rows of data) and can't seem to get it to work. Any help would be
appreciated.

I would think a macro that eliminates those other records and puts the phone
numbers on the first (and only) record to keep would be easier than a formlua.

If that sounds interesting, you'll have to describe your data (key field is
column ??). Phone numbers are in column ??.

And what does merge mean? Use one cell that contains all the phone numbers
(could be a problem later on) or dedicate columns (say) M:P for phone
numbers--limit 4 per person???



CCharley wrote:

I am looking for a formula for an "if / then" scenario. I have a
database where each record is duplicated based on a change in
the data in one field. For example, if the person has 3 different phone
numbers, then the subject's record would appear 3 times, with the only
difference between each record appearing in the phone number column.

I am trying to write a formula stating if the person's name is the same,
then merge the phone number fields. I have tried several different things
(including pivot tables, which don't work because there are too many
columns/rows of data) and can't seem to get it to work. Any help would be
appreciated.

--

Dave Peterson

My keyed field is a File Number Field (column 6). There may be multiple file
#'s per person.

The 1st column is Last name, 2nd is First Name, 3rd is Middle Name, 4th is
DOB, etc. etc. - until the 15th column is Phone number. I want one cell that
contains all of the phone numbers listed for that file number, with a comma
and a space in between each phone number, and only one record per file
number. Right now it is duplicating the record for each phone number given
(this is the format it came in).

If it isn't possible to merge the phone numbers, then I would at least like
to have them on the same row in separate columns.

Therefore, what I am hoping to accomplish is the statement that if the file
number is the same in column 6, merge the cells in column 16 and delete any
duplicate records with the same file number. Does that make sense?

I am not too familiar with macros, but I am good at figuring things out
(although this one has baffled me). If you think a macro is the best idea,
or a combination of the two...I would appreciate any advice here.





"Dave Peterson" wrote:

I would think a macro that eliminates those other records and puts the phone
numbers on the first (and only) record to keep would be easier than a formlua.

If that sounds interesting, you'll have to describe your data (key field is
column ??). Phone numbers are in column ??.

And what does merge mean? Use one cell that contains all the phone numbers
(could be a problem later on) or dedicate columns (say) M:P for phone
numbers--limit 4 per person???



CCharley wrote:

I am looking for a formula for an "if / then" scenario. I have a
database where each record is duplicated based on a change in
the data in one field. For example, if the person has 3 different phone
numbers, then the subject's record would appear 3 times, with the only
difference between each record appearing in the phone number column.

I am trying to write a formula stating if the person's name is the same,
then merge the phone number fields. I have tried several different things
(including pivot tables, which don't work because there are too many
columns/rows of data) and can't seem to get it to work. Any help would be
appreciated.

--

Dave Peterson

Does Column 15 or column 16 contain the phone number. I'm gonna assume column
15, but you can change it.

First, sort your data by the key column. The macro assumes your data is in the
correct sequence.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim KeyCol As Long
Dim PhoneCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

KeyCol = 6
PhoneCol = 15

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, KeyCol).End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, KeyCol).Value = .Cells(iRow - 1, KeyCol).Value Then
'found a match in column 6
.Cells(iRow - 1, PhoneCol).Value _
= .Cells(iRow - 1, PhoneCol).Value _
& ", " & .Cells(iRow, PhoneCol).Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Be careful--this macro destroys the original data when it does its work. So
either try this against a copy of your data--or close without saving if it
doesn't work ok.

All it's doing is starting at the bottom of the data (based on the keycol).
Then it works its way up the data. If that keycol matches the previous, then it
merges the data in the phonecol (15 or 16???).





CCharley wrote:

My keyed field is a File Number Field (column 6). There may be multiple file
#'s per person.

The 1st column is Last name, 2nd is First Name, 3rd is Middle Name, 4th is
DOB, etc. etc. - until the 15th column is Phone number. I want one cell that
contains all of the phone numbers listed for that file number, with a comma
and a space in between each phone number, and only one record per file
number. Right now it is duplicating the record for each phone number given
(this is the format it came in).

If it isn't possible to merge the phone numbers, then I would at least like
to have them on the same row in separate columns.

Therefore, what I am hoping to accomplish is the statement that if the file
number is the same in column 6, merge the cells in column 16 and delete any
duplicate records with the same file number. Does that make sense?

I am not too familiar with macros, but I am good at figuring things out
(although this one has baffled me). If you think a macro is the best idea,
or a combination of the two...I would appreciate any advice here.

"Dave Peterson" wrote:

I would think a macro that eliminates those other records and puts the phone
numbers on the first (and only) record to keep would be easier than a formlua.

If that sounds interesting, you'll have to describe your data (key field is
column ??). Phone numbers are in column ??.

And what does merge mean? Use one cell that contains all the phone numbers
(could be a problem later on) or dedicate columns (say) M:P for phone
numbers--limit 4 per person???



CCharley wrote:

I am looking for a formula for an "if / then" scenario. I have a
database where each record is duplicated based on a change in
the data in one field. For example, if the person has 3 different phone
numbers, then the subject's record would appear 3 times, with the only
difference between each record appearing in the phone number column.

I am trying to write a formula stating if the person's name is the same,
then merge the phone number fields. I have tried several different things
(including pivot tables, which don't work because there are too many
columns/rows of data) and can't seem to get it to work. Any help would be
appreciated.

--

Dave Peterson


--

Dave Peterson