Re: VBA question


<yousaf.hassan@xxxxxxxxx> wrote in message
news:1151409407.198385.319070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


Now, How do you want your final output to look?
Do you want all items with same leftnumber grouped together
or do you want to maintain the order of original list?

The final output should look like this:

BUSY123564
BUSY123564
BUSY123564
BUSY123564
FREE234098
FREE234098
FREE234098
FREE234098

etc.


Also, the following seems a tautology to me..
- 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"
These states would already exist and thus require no action. Or am I
miss reading the situation?


Yes, these states would require no action. Sorry! This was my poor
atempt at writing pseudo code.


You will also need to clarify..
- Only on the "BUSY" values, look at the number after the pipe
What if some of these right numbers are >7 and others are <7
What would be the status(Busy/Free) of the group in this case?

It requires only one number after the pipe to be less than or equal to
7 for the whole group to be BUSY.

Thanks again for you help.

Yousaf

Yousaf, test this to see if it is grouping by leftnumber
correctly and also that it runs at acceptable speed
for your 8,000 records. If it is too slow you may have
to do a "binary sort" on your records, which is
outside my knowledge. If everthing is satisfactory then
it means we only have the Busy/Free status to worry
about.

Private Sub Command1_Click()
Dim arrMaster(1 To 12) As String
Dim Busy(1 To 12) As Boolean, Checked(1 To 12) As Boolean
Dim leftnumber(1 To 12) As String, rightnumber(1 To 12) As Double
Dim i As Long, numstring As String, arrFinal(1 To 12) As String
Dim idx As Long, busyfound As Boolean, freefound As Boolean
Dim pos As Integer, arrBusyFinal(1 To 12) As Boolean
Dim rightFinal(1 To 12) As Double

arrMaster(1) = "BUSY15321130|-0.9"
arrMaster(2) = "FREE15321130|-38546.4"
arrMaster(3) = "BUSY15347040|-23"
arrMaster(4) = "BUSY15347040|0.2"
arrMaster(5) = "BUSY15321136|-0.9"
arrMaster(6) = "BUSY15347043|-0.9"
arrMaster(7) = "FREE15321136|-38546.4"
arrMaster(8) = "FREE15347043|-38546.4"
arrMaster(9) = "BUSY15347046|-0.9"
arrMaster(10) = "FREE15347046|-38546.4"
arrMaster(11) = "BUSY15321179|0.2"
arrMaster(12) = "FREE15321179|-38553.4"

For i = 1 To UBound(arrMaster)
If Left$(arrMaster(i), 1) = "B" Then Busy(i) = True
pos = InStr(arrMaster(i), "|")

leftnumber(i) = Mid$(arrMaster(i), 5, pos - 5)

'Print leftnumber(i)
numstring = Mid$(arrMaster(i), 14, Len(arrMaster(i)) - 13)
rightnumber(i) = CDbl(numstring)
'Print rightnumber(i)
Next i

'Group the list by leftnumber
idx = 1: pos = 1
Do Until idx = UBound(arrMaster)
If Checked(idx) = False Then numstring = leftnumber(idx)

For i = idx To UBound(arrMaster)
If leftnumber(i) = numstring And Checked(i) = False Then
arrFinal(pos) = arrMaster(i)
arrBusyFinal(pos) = Busy(i)
rightFinal(pos) = rightnumber(i)
pos = pos + 1
Checked(i) = True 'so can't be used again

End If

Next i

idx = idx + 1
Loop

For i = 1 To 12
Print arrFinal(i)
Next i

Print "fin"
End Sub


.

Relevant Pages