Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
Hello,
Have a look at the VBA forum http://forums.microsoft.com/MSDN/Sho...ID=74&SiteID=1 I did something similar for someone on there. Thread name is "Moving Data" ChasAA "Help?" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
ChasAA,
I tried looking for the thread you have given me, but no success. Are there anymore suggestions "ChasAA" wrote: Hello, Have a look at the VBA forum http://forums.microsoft.com/MSDN/Sho...ID=74&SiteID=1 I did something similar for someone on there. Thread name is "Moving Data" ChasAA "Help?" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
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 "ChasAA" wrote: Hello, Have a look at the VBA forum http://forums.microsoft.com/MSDN/Sho...ID=74&SiteID=1 I did something similar for someone on there. Thread name is "Moving Data" ChasAA "Help?" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
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. "ChasAA" wrote: 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 "Help?" wrote: 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. "ChasAA" wrote: 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 "ChasAA" wrote: Hello, Have a look at the VBA forum http://forums.microsoft.com/MSDN/Sho...ID=74&SiteID=1 I did something similar for someone on there. Thread name is "Moving Data" ChasAA "Help?" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
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 "Help?" wrote: 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. "ChasAA" wrote: 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 "Help?" wrote: 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. "ChasAA" wrote: 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 "ChasAA" wrote: Hello, Have a look at the VBA forum http://forums.microsoft.com/MSDN/Sho...ID=74&SiteID=1 I did something similar for someone on there. Thread name is "Moving Data" ChasAA "Help?" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to combine variable multiple rows
ChasAA,
With a little modifications, it worked like a charm! Thanks "ChasAA" wrote: 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 "Help?" wrote: 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. "ChasAA" wrote: 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 "Help?" wrote: 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. "ChasAA" wrote: 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 "ChasAA" wrote: Hello, Have a look at the VBA forum http://forums.microsoft.com/MSDN/Sho...ID=74&SiteID=1 I did something similar for someone on there. Thread name is "Moving Data" ChasAA "Help?" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple rows into one. | Excel Discussion (Misc queries) | |||
How to combine information from multiple rows into one | Excel Discussion (Misc queries) | |||
Combine Data from Multiple Rows | Excel Discussion (Misc queries) | |||
Macro to combine variable rows | Excel Programming | |||
Combine multiple rows to 1 row.... help | Excel Programming |