Justify data in right columns

P

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
890
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>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Are the cells empty that offset the columns?
 
Upvote 0
Are the cells with "JV" treated as blank?
 
Upvote 0
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.
 
Last edited:
Upvote 0
How will the debit and credit values be determined if all the blank cells are removed?
 
Upvote 0
Is there always a "JV" if its a debit?
 
Upvote 0
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]
 
Last edited:
Upvote 0
Just need to find a way to determine how many rows

Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
pulling data from one chart to another
Replies
1
Views
814
jb00976
J
C
  • Question Question
Sum
Replies
3
Views
1K
pbornemeier
pbornemeier
M
  • Question Question
Finding value in a row
Replies
4
Views
1K
MRN227
M
P
  • Question Question
Enter description next to a number
2
Replies
10
Views
1K
Panoos64
P
P
  • Question Question
Clear data if specific columns are blank
Replies
2
Views
590
Panoos64
P

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top