Looping help needed

[Makelei]

Hi,
I need some help with looping....
Using Excel 2003.

I have a list of some 30 000 rows. It has organization and person
information in following way:
Org1
Org2 (starting - just name of it)
Name2-1
Name2-2
...
Name2-N
Org2 (ending - just the name of it)
Org3 (starting - just name of it)
Name3-1
...
Name3-N
Org3 (ending - just the name of it)
Name1-1 (belongin to Org1)
....
Name1-N (belongin to Org1)
Org1 (ending - just the name of it)

I need to get the Org-name next to name column. I am not quite sure how to
make it in a way that it will check starting and ending and when ending there
might be person belongin to previous organization...

I have been able to get so far that it is filling up allrigtm, but when
ending and earlier organization should be returned, I cannot figure out how.
Oranization hierarchy is 5 levels.
Any help would be appreciated
Makelei

 

[Makelei]

Sorry, that it is not clear...

All data is in column A.
When new organization starts there is name of the organization. Same when
organization ends. Organization hierarcy is 5 levels. This means that the
first organization level starts at row 1 and ends to the last used row.
Between these there is a list of organizations as level 2 to level 5.

In the middle of these two organization (start and end) rows there is a list
of people belonging to this organization.

Now I should get the organization information to same row to column I. The
problem is that when one organization ends there might be names that are
belonging to mother/higher organization hiearchy, which starting row was
before this ending organization started (and also ended).

Hopefully this clarifies a bit ;o)
Makelei

 

[Makelei]

Hi,
Actually I want just to copy the organization info to I-column.
The structure is now
A-column I-column
Org1
Org2 (starts)
Fred -> Org2
Smith -> Org2
Wed
Org2 (ends)
Org3 (starts)
Jane
Smith -> Org3
Thur -> Org3
Org3 (ends)
Mike -> Org1
Tom -> Org1
Org1 (ends)


Thanks
Makelei

 

[Makelei]

Hi,
Nope, I want to ONLY add organization (from previous rows) information to
I-column. It is not available at the moment with the name. There is other
information in columns B to H for each name and I need that organization
information along with the name.

The challenge for me is that after one organization ends, there can be names
for higher level organization. In example Mike and Tom belongs to Org1.

Amount of people varies per organization.

Thanks Simon bearing with me!
Makelei

 

[Makelei]

Thanks Simon,
I really do appreciate. Shall I send the sample to your email address?
Makelei

 

[Makelei]

Hi,
This works OK until there shoud be organization that is earlier in the list
-> see example of Mike -> Org1 and Tom -> Org1. Org1 has started earlier and
names are comin later on in the list.

Sub A08OrgsWithName()
Dim LRw, i, Org, Org1, Org2, Org3, Org4, Org5
Columns("I:M").ClearContents
LRw = Cells(Rows.Count, "E").End(xlUp).Row

Org1 = Range("A4").Value

For i = 8 To LRw
If (Left(Range("A" & i).Value, 2) = 50 And Len(Range("A" & i).Value)

7) Or (Left(Range("B" & i).Value, 2) = 50 And Len(Range("B" & i).Value) >

7) Then
Org1 = Range("A" & i).Value & Range("B" & i).Value
Org = Org1
Else
End If

Range("K" & i).Value = Org
' Range("M" & i).Value = Range("A" & i).Value & Left(Org1, 8)
Next i

End Sub

Makelei

 

[Makelei]

Hi,
I was able to solve this problem on my own - thanks for your time.
Here is how I solved it:
Sub OrgsWithNames()
Dim LRw, i, Org, OrgF
Application.ScreenUpdating = False

Columns("I:M").ClearContents
LRw = Cells(Rows.Count, "E").End(xlUp).Row

For i = 8 To LRw
If (Left(Range("A" & i).Value, 2) = 50 And Len(Range("A" & i).Value)

7) Or (Left(Range("B" & i).Value, 2) = 50 And Len(Range("B" & i).Value) >

7) Then

Range("A" & i + 1).Select
Org = Range("A" & i).Value & Range("B" & i).Value

OrgF = Range("A" & i & ":B20000").Find(What:=Org,
After:=ActiveCell _
, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Row

If OrgF > i Then
Range("K" & i & ":K" & OrgF + 3).Value = Org
Range("L" & i).Value = OrgF
Else
End If

Else

End If

Next i
Application.ScreenUpdating = True

End Sub


Makelei