Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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' ('The Code Cage' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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" wrote: 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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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' ('The Code Cage' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
So you want all the data from column A putting in single row? if thats the case why not copy the data then right clickPASTE 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. Makelei;308811 Wrote: 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" wrote: 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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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, Simn Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com/))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'Looping help needed - The Code Cage Forums' ('Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86323)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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" wrote: So you want all the data from column A putting in single row? if thats the case why not copy the data then right clickPASTE 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. Makelei;308811 Wrote: 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" wrote: 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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com/))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'Looping help needed - The Code Cage Forums' ('Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86323)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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. Makelei;308856 Wrote: 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" wrote: Makelei;308811 Wrote: 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" wrote: 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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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) Nam3-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's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'Looping help needed - The Code Cage Forums' ('Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86323)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
Thanks Simon,
I really do appreciate. Shall I send the sample to your email address? Makelei "Simon Lloyd" wrote: 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. Makelei;308856 Wrote: 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" wrote: Makelei;308811 Wrote: 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" wrote: 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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'Looping help needed - The Code Cage Forums' ('Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86323)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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" wrote: Thanks Simon, I really do appreciate. Shall I send the sample to your email address? Makelei "Simon Lloyd" wrote: 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. Makelei;308856 Wrote: 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" wrote: Makelei;308811 Wrote: 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" wrote: 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. :) Makelei;308750 Wrote: 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" wrote: I can't make out what you're query is, how is your data laid out in columns? what is it that you would like to achieve?Makelei;308728 Wrote: 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's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'Looping help needed - The Code Cage Forums' ('Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=86323)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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) Makelei;310407 Wrote: 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" wrote: Thanks Simon, I really do appreciate. Shall I send the sample to your email address? Makelei "Simon Lloyd" wrote: 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. Makelei;308856 Wrote: 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" wrote: Makelei;308811 Wrote: 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 Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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 ------------------------------------------------------------------------ MakeLei's Profile: http://www.thecodecage.com/forumz/member.php?userid=242 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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 "MakeLei" wrote: 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 ------------------------------------------------------------------------ MakeLei's Profile: http://www.thecodecage.com/forumz/member.php?userid=242 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping help needed
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. Makelei;317144 Wrote: 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) 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 "MakeLei" wrote: 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 ------------------------------------------------------------------------ MakeLei's Profile: 'The Code Cage Forums - View Profile: MakeLei' (http://www.thecodecage.com/forumz/member.php?userid=242) View this thread: 'Looping help needed - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=86323) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=86323 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping? | Excel Programming | |||
Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
Looping | Excel Programming | |||
Looping macro needed to find intersections of rows and columns | Excel Programming | |||
Help needed - seriously (looping? macros? formulas?) | Excel Programming |