Converting data Format

[Guest]

I hope I explain this clearly. I have a 45,000 row worksheet that is
organized like an Org Chart. What I need to accomplish is convert the
structure to what you would see in a database. Meaning I would like it to be
formatted like a series of records. Each record list the lowest level and
then the next level and so forth. Here is an example...The data is currently
formatted like this...

Group 1
Team 1
Person 1
Person 2
Team 2
Person 3
Person 4
Person 5
Group 2
Team 3
Person 6
Person 7
Team 4
Person 7
Person 8
Person 9

I want it to look like this...

Person Team Group
1 1 1
2 1 1
3 2 1
4 2 1
5 3 2
6 3 2
7 4 2
8 4 2
9 4 2

Person 1 is part of Team 1 which is a part of Group 1...Still with me?

Any suggestions to accomplish this would be GREATLY appreciated. Thank you
in advance for all replies.

 

[Excelenator]

Is the example data you listed EXACTLY your data? What I mean is Group
1, Team, 1, Person 1, are the actual values or are their various names
for the groups, teams, and peolpe? If it's the actual data, is it
arranged in ONE column as you list it?

 

[VBA Noob]

I sure there's an better way...lot of steps but this might help.

You could try.

Text to Columns. Delimiter by space.
Add two helper columns one for Group and one for team.
In cell C1 enter =IF(A2="Group",B2,"")
In Cell D1 enter =IF(A2="team",B2,"")

Drag Formula's down. Next select columns C & D. Press Ctrl + G specia
blanks. (See link for help on this part)

http://www.contextures.com/xlDataEntry02.html

Finally put in Pivot table and job done

VBA Noo

 

[VBA Noob]

Or some formulas to do the same thing

Assumes list starts in A2
B1 = Group
C1 = Team
D1 = Person

Group
=IF(LEFT(A2,LEN($B$1))=$B$1,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),"")

Team
=IF(LEFT(A2,LEN($C$1))=$C$1,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),"")

Person
=IF(LEFT(A2,LEN($D$1))=$D$1,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),"")

VBA Noo