A challenging task in VBA

Hi everybody

Need some help.

I have a column in Excel which looks something like this:

BUSY15321130|-0.9
FREE15321130|-38546.4
BUSY15347040|-23
BUSY15347040|0.2
BUSY15321136|-0.9
BUSY15347043|-0.9
FREE15321136|-38546.4
FREE15347043|-38546.4
BUSY15347046|-0.9
FREE15347046|-38546.4
BUSY15321179|0.2
FREE15321179|-38553.4

This column could have any number of records, i.e. greater than 8000.

I want to do the following:

- Look through the whole column and detect all the duplicates
based on the numbers after the first four characters and before the
pipe character.
- Look at the first four chars of the duplicates
- If all of them are "BUSY" then change all the cell values to "BUSY"
- If all of them are "FREE" then change all the cell values to "FREE"
- If some are "FREE" and some "BUSY" Then:
- Only on the "BUSY" values, look at the number after the pipe
character:

- If the number is less than or equal to 7 Then
- Change all the cell values to "BUSY"
- Else change all the cell values to "FREE"

I want to code all this in VBA. Any help i.e. pointers or pseudo code
would be very much
appreciated.

Regards
Zu

Pls give an example of what it would look like before/after

--
Don Guillett
SalesAid Software
[email protected]
"Zuzeppeddu" <[email protected]> wrote in message
news:[email protected]...
> Hi everybody
>
> Need some help.
>
> I have a column in Excel which looks something like this:
>
> BUSY15321130|-0.9
> FREE15321130|-38546.4
> BUSY15347040|-23
> BUSY15347040|0.2
> BUSY15321136|-0.9
> BUSY15347043|-0.9
> FREE15321136|-38546.4
> FREE15347043|-38546.4
> BUSY15347046|-0.9
> FREE15347046|-38546.4
> BUSY15321179|0.2
> FREE15321179|-38553.4
>
> This column could have any number of records, i.e. greater than 8000.
>
> I want to do the following:
>
> - Look through the whole column and detect all the duplicates
> based on the numbers after the first four characters and before the
> pipe character.
> - Look at the first four chars of the duplicates
> - If all of them are "BUSY" then change all the cell values to "BUSY"
> - If all of them are "FREE" then change all the cell values to "FREE"
> - If some are "FREE" and some "BUSY" Then:
> - Only on the "BUSY" values, look at the number after the pipe
> character:
>
> - If the number is less than or equal to 7 Then
> - Change all the cell values to "BUSY"
> - Else change all the cell values to "FREE"
>
> I want to code all this in VBA. Any help i.e. pointers or pseudo code
> would be very much
> appreciated.
>
> Regards
> Zu
>

Hi

Before:

BUSY15321130|-0.9
FREE15321130|-38546.4
BUSY15347040|-23
BUSY15347040|0.2
BUSY15321136|-0.9
FREE15321136|-38546.4
BUSY15347043|9.012
FREE15347043|-38546.4
BUSY15347046|-0.9
FREE15347046|-38546.4
BUSY15321179|7.325
BUSY15321179|3.314
FREE15321179|-38553.4
FREE15321179|-38553.4

After:

BUSY
BUSY
BUSY
BUSY
BUSY
BUSY
FREE
FREE
BUSY
BUSY
BUSY
BUSY
BUSY
BUSY


Thanks
Zu


Don Guillett wrote:
> Pls give an example of what it would look like before/after
>
> --