PDA

View Full Version : Output From and to document numbers and missing number for each branch in given range



satputenandk
05-12-2014, 06:10 AM
Dear Members,

I generate dispatch report daily or alternate day. I have raw data with branch code and document numbers. I am looking for vba code which output From and To document number for each branch & missing document number/numbers. (each document set have 50 leafs)

Please find attached file for more clarification. (I have showed output in separate sheet.)

Any help may appreciated.

Bob Phillips
05-12-2014, 08:07 AM
public Sub CreateOuput()
Const FORMULA_BATCH_50 As String = "=SUMPRODUCT(--(RIGHT('Raw Data'!B<start>:B<end>,2)=""50""))"
Const FORMULA_BATCH_100 As String = "=SUMPRODUCT(--(RIGHT('Raw Data'!B<start>:B<end>,2)=""00""))"
Const FORMULA_MIDDLE_50 As String = "=MATCH(--(LEFT(B<start>,LEN(B<start>)-2)&""50""),B1:B<end>,0)"
Const FORMULA_MIDDLE_100 As String = "=MATCH(--(LEFT(B<start>,LEN(B<start>)-2)&""00""),B1:B<end>,0)"
Const FORMULA_MISSING As String = "=MIN(IF(NOT(ISNUMBER(MATCH(ROW(INDIRECT(RIGHT(B<start>,4)&"":""&RIGHT(B<end>,4))),--(RIGHT(B<start>:B<end>,4)),0)))," & _
"--(LEFT(B<start>,LEN(B<start>)-4)&TEXT(ROW(INDIRECT(RIGHT(B<start>,4)&"":""&RIGHT(B<end>,4))),""0000""))))"
Dim ws As Worksheet
Dim branch As String
Dim firstTHC As Long
Dim middleTHC As Long
Dim lastTHC As Long
Dim missingTHC As Long
Dim nextrow As Long
Dim lastrow As Long
Dim i As Long

Application.DisplayAlerts = False
Worksheets("Output").Delete
Application.DisplayAlerts = True
With ActiveSheet

Set ws = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbo ok.Worksheets.Count))
ws.Name = "Output"
ws.Range("A1:D1").Value = Array("Bkg Branch", "From", "To", "Missing")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextrow = 2
branch = .Range("A2").Value
For i = 2 To lastrow + 1

If .Cells(i, "A").Value <> "branch" Then

End If

firstTHC = i
Do
i = i + 1
Loop Until .Cells(i, "A").Value <> branch Or i > lastrow + 1
lastTHC = i - 1
If .Evaluate(Replace(Replace(FORMULA_BATCH_50, "<start>", firstTHC), "<end>", lastTHC)) Then

middleTHC = .Evaluate(Replace(Replace(FORMULA_MIDDLE_50, "<start>", firstTHC), "<end>", lastTHC))
ElseIf .Evaluate(Replace(Replace(FORMULA_BATCH_100, "<start>", firstTHC), "<end>", lastTHC)) Then

middleTHC = .Evaluate(Replace(Replace(FORMULA_MIDDLE_100, "<start>", firstTHC), "<end>", lastTHC))
Else

middleTHC = 0
End If
If middleTHC > 0 Then

.Cells(firstTHC, "A").Resize(, 2).Copy ws.Cells(nextrow, "A")
ws.Cells(nextrow, "C").Value = .Cells(middleTHC, "B").Value
.Range("F1").FormulaArray = Replace(Replace(FORMULA_MISSING, "<start>", firstTHC), "<end>", middleTHC)
If .Range("F1").Value > 0 Then ws.Cells(nextrow, "D").Value = .Range("F1").Value
nextrow = nextrow + 1

.Cells(middleTHC + 1, "A").Resize(, 2).Copy ws.Cells(nextrow, "A")
ws.Cells(nextrow, "C").Value = .Cells(lastTHC, "B").Value
.Range("F1").FormulaArray = Replace(Replace(FORMULA_MISSING, "<start>", middleTHC), "<end>", lastTHC)
If .Range("F1").Value > 0 Then ws.Cells(nextrow, "D").Value = .Range("F1").Value
nextrow = nextrow + 1

branch = .Cells(i, "A").Value
Else

.Cells(firstTHC, "A").Resize(, 2).Copy ws.Cells(nextrow, "A")
ws.Cells(nextrow, "C").Value = .Cells(lastTHC, "B").Value
.Range("F1").FormulaArray = Replace(Replace(FORMULA_MISSING, "<start>", firstTHC), "<end>", lastTHC)
If .Range("F1").Value > 0 Then ws.Cells(nextrow, "D").Value = .Range("F1").Value
nextrow = nextrow + 1

branch = .Cells(i, "A").Value
End If
If i < lastrow Then i = i - 1
Next i

.Range("F1").ClearContents
End With
End Sub

satputenandk
05-13-2014, 11:16 PM
Thank You sir,

You are genius. you code working perfect. :clap2:

Thanks again for giving your valuable time.

satputenandk
05-13-2014, 11:57 PM
Dear sir,

Here I am reopening thread because, When I use large data the code gives Run-time error '13': Type mismatch error on below line


middleTHC = .Evaluate(Replace(Replace(FORMULA_MIDDLE_100, "<start>", firstTHC), "<end>", lastTHC))

I have attached file with large data

Bob Phillips
05-14-2014, 12:46 AM
I can correct that first issue, but there is a more serious problem later on. Line #53 THC is 1677049, line #54 THC is 9155499. I thought you had said that gaps would just be an increment of 1, and I coded such. I am not catering for a series 7,478,450 missing THC numbers.

satputenandk
05-15-2014, 12:58 AM
sorry sir,:( I forgot one point that any branch might be contain more that one series that means Line #53 and Line # 54 is different series. (If gap is more that 50 then consider it new series).

My English is not well.:sad2:

thanks

satputenandk
05-16-2014, 09:49 PM
Any help may appreciated.

satputenandk
05-20-2014, 01:37 AM
Any Master have solution for this ??

satputenandk
05-21-2014, 03:44 AM
bump post....

satputenandk
05-26-2014, 05:46 AM
bump post...

satputenandk
06-05-2014, 05:33 AM
bump no response yet

satputenandk
06-10-2014, 04:48 AM
bump post.......

satputenandk
06-24-2014, 01:38 AM
Please help me out .....