Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert long list into shorter list
have a three column range holding the following data: Col1 employee name,
Col2 a date the name was entered into the table, Col3 the employee's assignment As the employee's assignment changes, the table is updated by adding their name to the end of the range in Col1, entering the date the assignment became effective in Col2, and the new assignment in Col3. When (if) any new assignments are entered, the entire range is sorted by Col1 (employee name) in ascending order. Thus, from day to day, the range will grow longer as new or duplicate names are entered. I have to keep the entire range for historical tracking, but I need an additional table that holds only the most recent or current assignments. The following "long" list should produce a resulting "short" list: Columns separated with semicolon for clarity here. LongList Doe, John; 1/1/07; Line 1 Doe, John; 2/5/07; Line 3 Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 1/1/07; Line 4 Jun, Mary; 1/23/07; Line 7 Jun, Mary; 2/17/07; Line 3 ShortList Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 2/17/07; Line 3 I'm thinking about looping (or moving) down the LongList row by row looking at each entry in Col1 comparing to the next to determine the final entry of the matches. Using that value, begin populating the new table. However, as I begin writing the looping, I don't know if I should use Next For, For each Next, or something else. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert long list into shorter list
If you have 5 staff with 1000 entries, looping is wasteful of resources.
Would you consider Find previous method from bottom up (Since the list has been sorted)? Cheers! -- Regards PY & Associates (GMT+8) "gifer" wrote in message ... have a three column range holding the following data: Col1 employee name, Col2 a date the name was entered into the table, Col3 the employee's assignment As the employee's assignment changes, the table is updated by adding their name to the end of the range in Col1, entering the date the assignment became effective in Col2, and the new assignment in Col3. When (if) any new assignments are entered, the entire range is sorted by Col1 (employee name) in ascending order. Thus, from day to day, the range will grow longer as new or duplicate names are entered. I have to keep the entire range for historical tracking, but I need an additional table that holds only the most recent or current assignments. The following "long" list should produce a resulting "short" list: Columns separated with semicolon for clarity here. LongList Doe, John; 1/1/07; Line 1 Doe, John; 2/5/07; Line 3 Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 1/1/07; Line 4 Jun, Mary; 1/23/07; Line 7 Jun, Mary; 2/17/07; Line 3 ShortList Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 2/17/07; Line 3 I'm thinking about looping (or moving) down the LongList row by row looking at each entry in Col1 comparing to the next to determine the final entry of the matches. Using that value, begin populating the new table. However, as I begin writing the looping, I don't know if I should use Next For, For each Next, or something else. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert long list into shorter list
Sure. I forgot to mention, I don't know the various macro functions,
commands, etc., very well. So, no doubt, something rather simple probably will work. I will kick this around a bit. Thanks! "PY & Associates" wrote in message ... If you have 5 staff with 1000 entries, looping is wasteful of resources. Would you consider Find previous method from bottom up (Since the list has been sorted)? Cheers! -- Regards PY & Associates (GMT+8) "gifer" wrote in message ... have a three column range holding the following data: Col1 employee name, Col2 a date the name was entered into the table, Col3 the employee's assignment As the employee's assignment changes, the table is updated by adding their name to the end of the range in Col1, entering the date the assignment became effective in Col2, and the new assignment in Col3. When (if) any new assignments are entered, the entire range is sorted by Col1 (employee name) in ascending order. Thus, from day to day, the range will grow longer as new or duplicate names are entered. I have to keep the entire range for historical tracking, but I need an additional table that holds only the most recent or current assignments. The following "long" list should produce a resulting "short" list: Columns separated with semicolon for clarity here. LongList Doe, John; 1/1/07; Line 1 Doe, John; 2/5/07; Line 3 Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 1/1/07; Line 4 Jun, Mary; 1/23/07; Line 7 Jun, Mary; 2/17/07; Line 3 ShortList Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 2/17/07; Line 3 I'm thinking about looping (or moving) down the LongList row by row looking at each entry in Col1 comparing to the next to determine the final entry of the matches. Using that value, begin populating the new table. However, as I begin writing the looping, I don't know if I should use Next For, For each Next, or something else. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert long list into shorter list
gifer,
Copy the data to a new sheet, then use this macro Sub test() Dim lRow As Long lRow = 1 While Range("A" & lRow).Text < "" While Range("A" & lRow).Text = Range("A" & lRow + 1).Text Range("A" & lRow).EntireRow.Delete Wend lRow = lRow + 1 Wend End Sub "gifer" wrote: have a three column range holding the following data: Col1 employee name, Col2 a date the name was entered into the table, Col3 the employee's assignment As the employee's assignment changes, the table is updated by adding their name to the end of the range in Col1, entering the date the assignment became effective in Col2, and the new assignment in Col3. When (if) any new assignments are entered, the entire range is sorted by Col1 (employee name) in ascending order. Thus, from day to day, the range will grow longer as new or duplicate names are entered. I have to keep the entire range for historical tracking, but I need an additional table that holds only the most recent or current assignments. The following "long" list should produce a resulting "short" list: Columns separated with semicolon for clarity here. LongList Doe, John; 1/1/07; Line 1 Doe, John; 2/5/07; Line 3 Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 1/1/07; Line 4 Jun, Mary; 1/23/07; Line 7 Jun, Mary; 2/17/07; Line 3 ShortList Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 2/17/07; Line 3 I'm thinking about looping (or moving) down the LongList row by row looking at each entry in Col1 comparing to the next to determine the final entry of the matches. Using that value, begin populating the new table. However, as I begin writing the looping, I don't know if I should use Next For, For each Next, or something else. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert long list into shorter list
Thank you it works perfect!
"Vergel Adriano" wrote in message ... gifer, Copy the data to a new sheet, then use this macro Sub test() Dim lRow As Long lRow = 1 While Range("A" & lRow).Text < "" While Range("A" & lRow).Text = Range("A" & lRow + 1).Text Range("A" & lRow).EntireRow.Delete Wend lRow = lRow + 1 Wend End Sub "gifer" wrote: have a three column range holding the following data: Col1 employee name, Col2 a date the name was entered into the table, Col3 the employee's assignment As the employee's assignment changes, the table is updated by adding their name to the end of the range in Col1, entering the date the assignment became effective in Col2, and the new assignment in Col3. When (if) any new assignments are entered, the entire range is sorted by Col1 (employee name) in ascending order. Thus, from day to day, the range will grow longer as new or duplicate names are entered. I have to keep the entire range for historical tracking, but I need an additional table that holds only the most recent or current assignments. The following "long" list should produce a resulting "short" list: Columns separated with semicolon for clarity here. LongList Doe, John; 1/1/07; Line 1 Doe, John; 2/5/07; Line 3 Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 1/1/07; Line 4 Jun, Mary; 1/23/07; Line 7 Jun, Mary; 2/17/07; Line 3 ShortList Doe, John; 3/2/07; Line 4 Doe, Terry; 1/1/07; Line 2 Jun, Mary; 2/17/07; Line 3 I'm thinking about looping (or moving) down the LongList row by row looking at each entry in Col1 comparing to the next to determine the final entry of the matches. Using that value, begin populating the new table. However, as I begin writing the looping, I don't know if I should use Next For, For each Next, or something else. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert vertical blocked list to horizontal list? | Excel Worksheet Functions | |||
Create a shorter list of dates | Excel Discussion (Misc queries) | |||
Summarised list from long list | Excel Discussion (Misc queries) | |||
Formula too long - Shorter version? | Excel Worksheet Functions | |||
Setting up a random list from long list of names ? | Excel Discussion (Misc queries) |