Justify data in right columns

[Panoos64]

Hi all, i would like to create a VBA so that to adjust the below data in right columns and entering headings too as my below extract. In sch.1. are the data which the columns are mixed and in sch.2. is the expected result. I would be greatly appreciated any help. Thanks in advance

Sch.1.

<colgroup><col style="mso-width-source:userset;mso-width-alt:1060;width:22pt" width="29"> <col style="width:48pt" width="64" span="4"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:1097;width:23pt" width="30"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="width:48pt" width="64" span="6"> <col style="mso-width-source:userset;mso-width-alt:1718;width:35pt" width="47"> </colgroup><tbody>
[TD="class: xl69, width: 29, align: center"]
[/TD]
[TD="class: xl69, width: 64, align: center"]A[/TD]
[TD="class: xl69, width: 64, align: center"]B[/TD]
[TD="class: xl69, width: 64, align: center"]C[/TD]
[TD="class: xl69, width: 64, align: center"]D[/TD]
[TD="class: xl69, width: 87, align: center"]E[/TD]
[TD="class: xl69, width: 46, align: center"]F[/TD]
[TD="class: xl69, width: 30, align: center"]G[/TD]
[TD="class: xl69, width: 46, align: center"]H[/TD]
[TD="class: xl69, width: 64, align: center"]I[/TD]
[TD="class: xl69, width: 64, align: center"]J[/TD]
[TD="class: xl69, width: 64, align: center"]K[/TD]
[TD="class: xl69, width: 64, align: center"]L[/TD]
[TD="class: xl69, width: 64, align: center"]M[/TD]
[TD="class: xl69, width: 64, align: center"]N[/TD]
[TD="class: xl69, width: 47, align: center"]O[/TD]

[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl66, align: center"]10-02-18[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"]JV[/TD]
[TD="class: xl67, align: center"]1005623[/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl71, align: center"]5.62[/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"]DISCOUNT[/TD]
[TD="class: xl68, align: center"] [/TD]
[TD="class: xl67, align: center"]9456[/TD]

[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"]15-02-18[/TD]
[TD="class: xl67, align: center"]1005648[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl71, align: center"]562.40[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, colspan: 2, align: center"]PURCHASES[/TD]
[TD="class: xl68, align: center"]9458[/TD]
[TD="class: xl67, align: center"] [/TD]

[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl66, align: center"]28-02-18[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"]1005655[/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl71, align: center"]309.70[/TD]
[TD="class: xl67, colspan: 2, align: center"]PURCHASES[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl68, align: center"] [/TD]
[TD="class: xl67, align: center"]9465[/TD]

[TD="class: xl65, align: center"]4[/TD]
[TD="class: xl66, align: center"]02-03-18[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"]JV[/TD]
[TD="class: xl67, align: center"]1005701[/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"]3.00[/TD]
[TD="class: xl71, align: center"] [/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"]DISCOUNT[/TD]
[TD="class: xl67, align: center"]9518[/TD]
[TD="class: xl68, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]

[TD="class: xl65, align: center"]5[/TD]
[TD="class: xl66, align: center"]03-03-18[/TD]
[TD="class: xl67, align: center"]1005718[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl70, align: center"] [/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl71, align: center"]83.60[/TD]
[TD="class: xl67, colspan: 2, align: center"]PURCHASES[/TD]
[TD="class: xl67, align: center"] [/TD]
[TD="class: xl68, align: center"] [/TD]
[TD="class: xl67, align: center"]9524[/TD]

</tbody>

Sch.2.

<colgroup><col style="mso-width-source:userset;mso-width-alt:1060;width:22pt" width="29"> <col style="width:48pt" width="64" span="4"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> </colgroup><tbody>
[TD="class: xl69, width: 29, align: center"]
[/TD]
[TD="class: xl69, width: 64, align: center"]A[/TD]
[TD="class: xl69, width: 64, align: center"]B[/TD]
[TD="class: xl69, width: 64, align: center"]C[/TD]
[TD="class: xl69, width: 64, align: center"]D[/TD]
[TD="class: xl69, width: 87, align: center"]E[/TD]
[TD="class: xl69, width: 46, align: center"]F[/TD]

[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl70, align: center"]DATE[/TD]
[TD="class: xl71, align: center"]INV.#[/TD]
[TD="class: xl71, align: center"]DEBIT[/TD]
[TD="class: xl71, align: center"]CREDIT[/TD]
[TD="class: xl71, align: center"]DESCRIPTION[/TD]
[TD="class: xl71, align: center"]TR.#[/TD]

[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl66, align: center"]10-02-18[/TD]
[TD="class: xl67, align: center"]1005623[/TD]
[TD="class: xl72, align: center"]5.62[/TD]
[TD="class: xl73, align: center"] [/TD]
[TD="class: xl67, align: center"]DISCOUNT[/TD]
[TD="class: xl68, align: center"]9456[/TD]

[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl66, align: center"]15-02-18[/TD]
[TD="class: xl67, align: center"]1005648[/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl72, align: center"]562.40[/TD]
[TD="class: xl67, align: center"]PURCHASES[/TD]
[TD="class: xl69, align: center"]9458[/TD]

[TD="class: xl65, align: center"]4[/TD]
[TD="class: xl66, align: center"]28-02-18[/TD]
[TD="class: xl67, align: center"]1005655[/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl73, align: center"]309.70[/TD]
[TD="class: xl67, align: center"]PURCHASES[/TD]
[TD="class: xl68, align: center"]9465[/TD]

[TD="class: xl65, align: center"]5[/TD]
[TD="class: xl66, align: center"]02-03-18[/TD]
[TD="class: xl67, align: center"]1005701[/TD]
[TD="class: xl72, align: center"]3.00[/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl67, align: center"]DISCOUNT[/TD]
[TD="class: xl68, align: center"]9518[/TD]

[TD="class: xl65, align: center"]6[/TD]
[TD="class: xl66, align: center"]03-03-18[/TD]
[TD="class: xl67, align: center"]1005718[/TD]
[TD="class: xl72, align: center"] [/TD]
[TD="class: xl72, align: center"]83.60[/TD]
[TD="class: xl67, align: center"]PURCHASES[/TD]
[TD="class: xl68, align: center"]9524[/TD]

</tbody>

 

[nemmi69]

Are the cells empty that offset the columns?

 

[nemmi69]

Are the cells with "JV" treated as blank?

 

[Panoos64]

Hi nemmi, yes the cells offset the columns are empty, and therefore that i don't need the JV cells, so we can clear or delete them.
Therefore that the above is just an extract of data and the columns and rows, change each time when i export the data from accounting system. The maximum of rows arises to 5000 and columns' range A-O, Basically, the code should run through the whole spreadsheet and should delete by horizontal empty/blank cells, including "JV", so that to justify the columns and data, but this complicate to determine the numbers which are below to DEBIT and CREDIT side.
I would like to thank for your time and your interesting to resolve my project.
Hv a nice day.

 

[nemmi69]

How will the debit and credit values be determined if all the blank cells are removed?

 

[Panoos64]

This was my query and my questioning. Do you have any other suggestion?
Thanks once again

 

[nemmi69]

Is there always a "JV" if its a debit?

 

[Panoos64]

Now i think you found the key to solution. I done a mistake above and is not "JV" but "JV2". The debit side marked always by transaction type "JV2" but is not stably in col. "D". Sometimes it appears in "D" but most of the times is mixed with date column in the same cell. please see below. If this help you to create the code, it doesn't matter for my project to appear a mixed date with additional text JV2, so do not try to split such cells.

Many thanks for your advice and i express my apologies for my fault.




[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl69, align: center"][/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]A
[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]B
[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]C[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]D[/TD]
[TD="class: cms_table_xl69, width: 87, align: center"]E[/TD]
[TD="class: cms_table_xl69, width: 46, align: center"]F[/TD]
[TD="class: cms_table_xl69, width: 30, align: center"]G[/TD]
[TD="class: cms_table_xl69, width: 46, align: center"]H[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]I[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]J[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]K[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]L[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]M[/TD]
[TD="class: cms_table_xl69, width: 64, align: center"]N[/TD]
[TD="class: cms_table_xl69, width: 47, align: center"]O[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, align: center"]1[/TD]
[TD="class: cms_table_xl66, align: center"]10-02-18
[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]JV2[/TD]
[TD="class: cms_table_xl67, align: center"]1005623[/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl71, align: center"]5.62[/TD]
[TD="class: cms_table_xl72, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]DISCOUNT[/TD]
[TD="class: cms_table_xl68, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]9456[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, align: center"]2[/TD]
[TD="class: cms_table_xl66, align: center"][/TD]
[TD="class: cms_table_xl66, align: center"]15-02-18[/TD]
[TD="class: cms_table_xl67, align: center"]1005648[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl72, align: center"][/TD]
[TD="class: cms_table_xl71, align: center"]562.40[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, colspan: 2, align: center"]PURCHASES[/TD]
[TD="class: cms_table_xl68, align: center"]9458[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, align: center"]3[/TD]
[TD="class: cms_table_xl66, align: center"]28-02-18[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]1005655[/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl72, align: center"][/TD]
[TD="class: cms_table_xl71, align: center"]309.70[/TD]
[TD="class: cms_table_xl67, colspan: 2, align: center"]PURCHASES[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl68, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]9465[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, align: center"]4[/TD]
[TD="class: cms_table_xl66, align: center"]02-03-18 JV2
[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]1005701[/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"]3.00[/TD]
[TD="class: cms_table_xl71, align: center"][/TD]
[TD="class: cms_table_xl72, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]DISCOUNT[/TD]
[TD="class: cms_table_xl67, align: center"]9518[/TD]
[TD="class: cms_table_xl68, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, align: center"]5[/TD]
[TD="class: cms_table_xl66, align: center"]03-03-18[/TD]
[TD="class: cms_table_xl67, align: center"]1005718[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl70, align: center"][/TD]
[TD="class: cms_table_xl72, align: center"][/TD]
[TD="class: cms_table_xl71, align: center"]83.60[/TD]
[TD="class: cms_table_xl67, colspan: 2, align: center"]PURCHASES[/TD]
[TD="class: cms_table_xl67, align: center"][/TD]
[TD="class: cms_table_xl68, align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]9524[/TD]
[/TR]
</tbody>[/TABLE]

 

[nemmi69]

Just need to find a way to determine how many rows

Option Explicit


Dim RowCount As Long
Dim ColCount As Long
Dim CellOK As Boolean
Dim CellDelCount As Integer
Dim IsDebit As Boolean


Sub JustifyDataInRightColumns()


For RowCount = 1 To 6
   IsDebit = False
   For ColCount = 1 To 5 'A to E
        CellOK = False
        CellDelCount = 0
        Do While CellOK = False
            If IsEmpty(Cells(RowCount, ColCount).Value) Or Trim(Cells(RowCount, ColCount).Value) = "" Then
                Cells(RowCount, ColCount).Delete Shift:=xlToLeft
                CellDelCount = CellDelCount + 1
                If CellDelCount >= 15 Then 'has deleted up to column O
                    CellOK = True
                End If
            ElseIf Cells(RowCount, ColCount).Value = "JV" Then
                IsDebit = True
                Cells(RowCount, ColCount).Delete Shift:=xlToLeft
                CellDelCount = CellDelCount + 1
                If CellDelCount >= 15 Then 'has deleted up to column O
                    CellOK = True
                End If
            Else
                CellOK = True
            End If
        Loop
    Next ColCount
    If IsDebit = True Then
        Cells(RowCount, 4).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Else
        Cells(RowCount, 3).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    End If


Next RowCount


'Set headers
Range("A1:F5").Cut Destination:=Range("A2:F6")
Range("A1").Value = "DATE"
Range("B1").Value = "INV.#"
Range("C1").Value = "DEBIT"
Range("D1").Value = "CREDIT"
Range("E1").Value = "DESCRIPTION"
Range("F1").Value = "TR.#"
End Sub

 

[Panoos64]

Well done nemmi! Based on my data it works perfect. I express my thanks for your time spent for my project and your prompt respond. It was so kind from you.
Hv a great lovely day