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.

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" <(E-Mail Removed)> wrote in message
news:ltidnVKnx_L_UXXYnZ2dnUVZ_r-(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:ltidnVKnx_L_UXXYnZ2dnUVZ_r-(E-Mail Removed)...
>> 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.
>>
>>
>
>

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

Thank you it works perfect!
"Vergel Adriano" <(E-Mail Removed)> wrote in message
news:1239E069-885C-4275-9E71-(E-Mail Removed)...
> 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.
>>
>>
>>