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

A challenging task in VBA
 

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

--
Don Guillett
SalesAid Software

"Zuzeppeddu" wrote in message
ups.com...
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

A challenging task in VBA
 

Hi

Befo

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

--