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

 

[Simon Lloyd]

I can't make out what you're query is, how is your data laid out in

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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Simon Lloyd]

So if i understand you right, you want to break that data out of one
column in to seperate columns but keeping the structure something like
this
Code:
--------------------
A B C D E F..............
Org1 John Smith Tue
Org2 Fred Smith Wed
Org3 Jane Smith Thur
--------------------


*How to get further help with a workbook*
For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will have the
opportunity to add attachmnets to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so.

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


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Simon Lloyd]

So you want all the data from column A putting in single row? if thats
the case why not copy the data then right click>PASTE SPECIAL check
Transpose then ok, all your data should now be in one row. Or does your
organisation change every 15th row, if this is the case i will construct
some code for you later this evening as i don't have time now.

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



Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Simon Lloyd]

No problem bearing with you Makelei, i guess now, as i am still having
trouble visualising what you have, i need to see a sample workbook to
try to devise something that would fit your requirement.

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


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Simon Lloyd]

You can do a couple of things to get the sample to me, you could join
our forums (shown in the link below) it's completely free, if you do
join you will have the opportunity to add attachmnets to your posts so
you can add workbooks to better illustrate your problems and get help
directly with them. Also if you do join please post in this thread (link
found below) so that people who have been following or helping with this
query can continue to do so, or you could upload your workbook to a site
where it can be downloaded from, or you could email it to me but i won't
get it until tomorrow at simonDOTlloydATthecodecageDOTcom (of course
substitute the words in captils for what they should be)

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)
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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[MakeLei]

Hi,
Here a sample list. List is over 20 000 rows as a total. Data is in
file as it is as I get it. As you run the macro it will add organization
info to column K. I have commented rows that are problematic for me
(rows 86 - 88).

Thanks for helping me out. I know this is not very hard, but I just
don't get it...

Makelei


+-------------------------------------------------------------------+
|Filename: Example.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=113|
+-------------------------------------------------------------------+

 

[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

 

[Simon Lloyd]

Hi Makelei, my apologies for not having time to look at your attachment,
glad your sorted!

Thanks very much for posting back your solution as this will help many
others with the same or similar problem.

Hi,
I was able to solve this problem on my own - thanks for your time.
Here is how I solved it: Code:
--------------------
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) 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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)