Group By Certain Column and Append Rows in Excel

[amtrak23]

I have a worksheet with several hundred rows of data. It is basically user information and different account information. One user can have up to 7 different pieces of account information.

Instead of having one row with all 7 pieces of acount info, it is made up of 7 rows, with the first 4 columns having the same info repeated.

An example would be something like this:
Bob smith, Chicago, 2/22/06, yes, account1, 43, 100, 57
Bob smith, Chicago, 2/22/06, yes, account2, 22, 202, 92
Bob smith could have as little as 2 accounts or as many as 7.

I would like to get it all rolled into one row, exclduing the first 4 columns which is all duplicate info. So ideally I would like to keep all of row one, then append the next few rows, minus the duplicate data in columns 1-4 to the end of row one.

I can do a Data-> Filter-> Advanced Filter and copy out the duplicate data in columns 1-4, but can't get anywhere with the remainder of the data.

The ultimate goal is to be able to use this sheet in mail merge doc.

 

[BenRowe]

I got it to work using if function. Assuming your data is in Columns A-H, and your first row of data is at least row 2 (if your first row is 1 put in a blank row), copy and paste the following into I2-L2
=IF($A2&$B2&$C2&$D2<>$A1&$B1&$C1&$D1,IF($A2&$B2&$C2&$D2=$A3&$B3&$C3&$D3,$E3,""),"")
in J2-L2 you would then change $E3 to $F3, $G3, $H3, respectively
you would then copy these four cells into the next four
columns, changing $A3&B3&C3&D3 to $A4&$B4&$C4&$D4
you would repeat this four more times, each time increasing the row number by 1
last set would be $A8&$B8&$C8&D8
you would then copy and paste these formulas cells down into all the other rows of the worksheet

 

[amtrak23]

I can get the second row to move up and follw the first, bt when I copy and change the values for the next four columns I'm getting blank cells? Can you write out the if statement in normal terms please? Maybe that will help me figure it out.

 

[BenRowe]

If the first four columns do not match, they will be blank. Is that what's going on?

 

[amtrak23]

Ok, I did it from scratch and got it to work. The problem was I was importing from a csv and when doing the delimiting it added a few spaces in front of some of the text. So when I would type new data in it wouldn't exactly be the same.

After playing with it for a while I figured out another problem. It;s kinda hard to explain, but here goes: The whole purpose of doing this is to get this ina format that can plugged into a mail merge doc. So in the merge doc all of the various account info will need to be displayed in the proper places.

When I run these if's on the remainder of the excel sheet I get the different accounts lining up in different columns since each user doesn't necessarily have their accounts in order.

Example. Instead of bob smithin the example above having accounts 1-3 he could have 1, 3,4, and 5. The next user could have accounts 1-3. So Bob's account 4 info will be lining up with the next users account 3 info.

Maybe I'm just going at this all wrong? Anyway, what you wrote definitely fixed the first problem and will be a huge help to me in the future.

 

[KenWright]

In your example, how would you determine from your data whether the account was 1/2/3/4/5/6/7. Is there actually a field that says 1/2/3/4/5/6/7 etc as per your example, and is that the exact format of the field ie account1 account2, or how do you know what number it is?

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[amtrak23]

Yes, there is actaully a field that is labeled something along the lines of "account1" , "account2", etc. Thanks!