Macro to combine variable multiple rows

[Guest]

I am still looking for a solution to this issue?

I have an excel file that has several people listed several times in
different rows. The only common information that shows these individuals as
the same person is their SSN. The same individual is listed several times in
different rows due to each row showing the same individual with different
products that they have purchased. Is their a macro that will read the SSN
column - looking for the same SSN, and if the same SSN exists, combine the
information into one row?


The data looks like:

Amy burden 111-11-1111 LTD
John Burden 111-11-1111 STD
sam burden 111-11-1111 CHILD
kim swafford 111-11-1112 PS 1000
ale swafford 111-11-1112 PS PLUS

The reason their are repetive SSNs, but different names are due to family
members will sign up for different products but they will be listed under
owners SSN, that is at the beginning of each repetitive SSN. I would like the
data to look like:

Amy burden 111-11-1111 LTD STD CHILD
kim swafford 111-11-1112 PS
1000 PS PLUS

 

[Guest]

Hello,

Have a look at the VBA forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=74&SiteID=1

I did something similar for someone on there.
Thread name is "Moving Data"

ChasAA

 

[Guest]

ChasAA,
I tried looking for the thread you have given me, but no success. Are there
anymore suggestions

 

[Guest]

Okay, I'll have a look at this tomorrow.
Is the initial data in three columns?
Is there only one product per row?
Where do you want the new data placed?

After the data is manipulated, do you want only one name per row?
How is the "owner" of the SSN identified , or is it always the first name in
the column.

Will SSN 111-11-1111 ever appear again after 111-11-112 for example?

Leave me this info and I'll have a look tommorow

Chas

 

[Guest]

ChasAA,
Quick response time! I actually found the thread for Moving Data. However,
it will not work. The data is in columns AA through BE. I would like the data
to combine listing the information after the last column-in different
columns. There is only one product per row, but the same individual. The
product will be different per row, but only one. Also, There will never be a
common number of times a person will be listed. Some will be listed 16 and
some 4, depends on the number of products they have purchased.

 

[Guest]

I dont understand what you mean "I would like the data to combine listing the
info after the last column-in different columns"

Also will SSN 1111 ever appear AFTER 1112? or AFTER 1113?

Will the first name of the block of SSN's be the "owner"?

anyway until tomorrow....

Chas

 

[Guest]

I am looking for the individual with the same SSN to become one row, with
each prodcut becoming a separate column at the end of the last column of the
file. The file will be sorted first by SSN so the SSN 1111 will not appear
again after 1112 or 1113. Yes, the first name of the block will be the owner
of that sequence of identical SSNs.

 

[Guest]

Hello,
Sorry for the late reply but work has been quite busy.

Anyway:

Option Base 1
Sub combine()
Dim thisSSN As String
Dim masterSSN As String
Dim thisName As String
Dim off As Integer
Dim nbrProducts As Integer
Dim dataArray()
Dim masterRow As Integer

Range("B1").Select
' This is where your first SSN appears
Selection.CurrentRegion.Select
' Select all the surrounding cells
Range(Selection, Selection.Offset(1, 0)).Select
' add one more row to prevent error in loop
dataArray = Selection.Value
' put all cell data into array


Range("E1").Select
' This is where you would like the combined data to start
counter = 1

Do While counter <= UBound(dataArray, 1) - 1
mastername = dataArray(counter, 1)
masterRow = counter
thisSSN = dataArray(counter, 2)
masterSSN = thisSSN
thisproduct = dataArray(counter, 3)
nbrProducts = 0
Selection.Offset(masterRow - 1, 0) = mastername
Selection.Offset(masterRow - 1, 1) = masterSSN
Do While thisSSN = masterSSN
thisproduct = dataArray(counter, 3)
nbrProducts = nbrProducts + 1
Selection.Offset(masterRow - 1, 1 + nbrProducts) = thisproduct
n = dataArray(counter, 1)
counter = counter + 1
thisSSN = dataArray(counter, 2)
Loop
Loop
End Sub

Chas

 

[Guest]

ChasAA,
With a little modifications, it worked like a charm!
Thanks