Merge or Combine Rows of Data into One Row, but keeping specific data

[gexcel15]

Hello, I have a spreadsheet with multiple lines and an employee may have multiple lines. I would I go about combining those employees that have multiple lines, keeping data from a specific cell?

For example, in the table below, employee Mickey Mouse has two "Benefit Plan" elections. Looking for a way to combine his elections in one column, but keeping the data in columns, E & J

Hope I'm explaining myself with some understanding. Any help is greatly appreciated!

 

[Joe4]

I am not quite sure I understand.
Can you post an image of your expected results, based on the example you have provided?

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

There is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[gexcel15]

I am not quite sure I understand.
Can you post an image of your expected results, based on the example you have provided?

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

There is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello Joe4,

The expected result is to only have "one" line per employee with both totals in separate columns.

Let's take Mickey Mouse in the example above:
I just need one line with Mickey's name, but Column D and Column J data should be in separate columns for the employee, but on the same line, not 2 lines of data.

I'm thinking a pivot table would be best, just not sure.

Thanks for any help you can provide.
Gwen

Employee ID	Legal Name - Last Name	Legal Name - First Name	Benefit Plan	Coverage Begin Date for Benefit Plan	Current Election Coverage Begin Date	Coverage End Date	Date Last Changed	Benefit Elections - Currently Enrolled as of Report Effective Date	Coverage
1234	Doe	Jane	401(k) Pre-Tax	1/5/2020	12/17/2020		12/17/2020 6:03	Yes	6%
1234	Doe	Jane	401(k) Pre-Tax	1/5/2020	12/10/2020		12/10/2020 6:04	Yes	10%
9876	Doe	John	401(k) Roth	6/11/2020	12/10/2020		12/10/2020 6:04	Yes	2%
6789	Dover	Ben	401(k) Pre-Tax	1/5/2020	1/5/2020		1/2/2020 15:58	Yes	10%
6789	Dover	Ben	401(k) Pre-Tax	1/5/2020	1/5/2020		1/2/2020 15:58	Yes	5%
4444	Mouse	Mickey	401(k) After-Tax	1/5/2020	1/5/2020		1/2/2020 15:58	Yes	7%
4444	Mouse	Mickey	401(k) Transition Credit	1/5/2020	1/5/2020		1/2/2020 15:58	Yes	3%
2424	Duck	Daffy	401(k) Pre-Tax	1/5/2020	4/2/2020		4/2/2020 6:15	Yes	9%
2424	Duck	Daffy	401(k) Roth	1/5/2020	4/2/2020		4/2/2020 6:15	Yes	9%
3657	White	Snow	401(k) Roth	1/12/2020	4/2/2020		4/2/2020 6:09	Yes	21%

 

[Joe4]

Are columns A, B, C,D, and J the only columns that you need in this output?
Are you looking for an automated (VBA) solution, or a formulaic solution that you will set up manually?

I am not sure Pivot Tables can be used, because though they cannot summarize data, I don't think they can turn one column into two. You would at least need to do something like make another copy of the column.

 

[gexcel15]

Are columns A, B, C,D, and J the only columns that you need in this output?
Are you looking for an automated (VBA) solution, or a formulaic solution that you will set up manually?

I am not sure Pivot Tables can be used, because though they cannot summarize data, I don't think they can turn one column into two. You would at least need to do something like make another copy of the column.

Joe4,

Yes, those are the only columns needed and any output will work, VBA or formulaic. I can read VBA (lol), just can't write it... Go figure!

Thanks,

 

[Joe4]

In looking at the "Benefit Plan" column, it looks like you have 4 different values.
So doesn't that mean that you would need 4 different columns for each person, one for each possibility?

 

[gexcel15]

In looking at the "Benefit Plan" column, it looks like you have 4 different values.
So doesn't that mean that you would need 4 different columns for each person, one for each possibility?

Yes - that is correct!

 

[Joe4]

OK, here is one way you can do it.

First, I making the following assumptions:
1. The data you posted is on "Sheet1", starting in cell A1
2. The output will be on "Sheet2"

Now, perform the following steps:
1. Copy columns A-C from Sheet1 to Sheet2 (to the same columns)
2. Select columns A-C on Sheet2, and from the Data menu, select the "Remove Duplicates" functionality to remove all duplicate records.
3. In cells D1:G1 on sheet 2, enter the name of your 4 benefit plans, exactly as they appear in your data.
4. Enter the following formula in cell D2:

=SUMIFS(Sheet1!$J:$J,Sheet1!$A:$A,Sheet2!$A2,Sheet1!$D:$D,Sheet2!D$1)

5. Copy that formula to cells E2:G2, and then down all rows

I believe this should give you what you want.