Excel 2007 : How to take data in one column and make 2 columns out of it based on certain criteria

[sskinner]

I have data from a csv file. It looks like this...

Group: Accounting Members
User: Accounting
User: Doe, John
User: Smith, Jane
User: Chan, Jackie
Group: Administration Members
User: Neal, Willie
User: Bullock, Sandra
Group: Administration Owners
Group: Conversion Members
User: Wise, Lindsey
User: Tower, Ryan

I need the "User" data in column A to be moved to column B of another sheet. And I need the group name to be listed in column A on the new sheet corresponding to each user. So I'd like my new sheet to look like this... (Note, if there are no users for a certain group, I'd still like for it to list that group.)

Group: Accounting Members
Group: Accounting Members User: Accounting
Group: Accounting Members User: Doe, John
Group: Accounting Members User: Smith, Jane
Group: Accounting Members User: Chan, Jackie
Group: Administration Members
Group: Administration Members User: Neal, Willie
Group: Administration Members User: Bullock, Sandra
Group: Administration Owners
Group: Conversion Members
Group: Conversion Members User: Wise, Lindsey
Group: Conversion Members User: Tower, Ryan

Any help is much appreciated!

Thanks!

**** I tried to edit my post and attach the spreadsheet, but don't see how to do that in the "Edit Post".

[Domski]

Hi,

Try this:

Please Login or Register  to view this content.

Dom

[sskinner]

Hi Dom!

Thanks for your help! I am a total novice at using the developer tool. But this is what I did... I added the developer tab. Inserted a Macro***, named it, clicked New, pasted what you provided above, saved the Workbook, ran the macro. And this is what I got on the sheet that was added...

*** I should have said I inserted a "button" and pasted the code.

Group: Accounting Members
Group: Administration Members
Group: Administration Owners
Group: Conversion Members

Can you tell what I did wrong? Sorry!

Thanks so much!
Stephanie

[sskinner]

One more thing I should add but don't know if it matters... In the cells that have a User, there is a space before the word User. That is how the data is exported and ends up in the excel spreadsheet.

[sskinner]

Anybody have any ideas? Thx!

[Andrew-R]

If your data is in column A, starting at row 1 then in row 1 of another column paste this formula and copy down as far as needed:

=IF(LEFT(A1,5)="Group",A1,INDEX(A:A,MAX(INDEX(ROW($A$1:A1)*(LEFT($A$1:A1,5)="Group"),0))) & A1)

[sskinner]

If your data is in column A, starting at row 1 then in row 1 of another column paste this formula and copy down as far as needed:

=IF(LEFT(A1,5)="Group",A1,INDEX(A:A,MAX(INDEX(ROW($A$1:A1)*(LEFT($A$1:A1,5)="Group"),0))) & A1)

Thank you Andrew! I can use this! I just took out the & A1 in the new column. When I copied in my example above, it looks like I needed the Group and the User in one column together. Bottom line, what I need is there to be a column titled Groups and a Column titled Users. And what you provided gives me that! Now I just need to figure out how to make this automatically happen. I think I can do it with a Macro. I'll play with the Macro and see if I can do it.

Thanks so much for your help!
Stephanie

[Andrew-R]

Sorry I misunderstood. Doing it into two columns is actually much easier.

Again assuming your data is in column A, starting at row 1, and also assuming that it always starts with a group and that we're using columns B & C for the output.

In B1 put the formula:

=A1

In B2 the formula:

=IF(LEFT(A2,5)="Group",A2,B1)

And copy down as far as needed.

In C2 use the formula:

=IF(LEFT(A2,5)="Group","",A2)

And, again, copy down.

Job's done.

[Domski]

This should get the code working:

Please Login or Register  to view this content.

Dom

[sskinner]

This should get the code working:

Please Login or Register  to view this content.

Dom

Dom,

Thanks so much!!! I really appreciate it! It worked perfectly! I am new to this site and don't know what I'm doing... but I clicked the star for you!

Thanks!
Stephanie

[Domski]

No problem. Happy to help.

Don't quote whole posts in replies or you'll incur the wrath of the mods (what might be left of them (in joke)).

Dom