Solved: Rearranging data

[Anomandaris]

Hi guys,

I need a bit of help with a spreadsheet. I've attached a file - Sheet 1 shows what the input looks like, and sheet 2 is what I need it to look like.
Sheet 1 will have some empty rows and columns, and the number of rows with data may vary - i need the macro to stop when it reaches a row with the word 'ACTION' in Column A.

Columns F & G are the main bits. Sometimes both F and G will be empty, in that case ignore the row. There will be data in either F or G, never both.
F and G show transactions on different files/accounts. positive means inflow, negative is outflow.
As you can see in the 'output' sheet - Column A should say 'in' or 'out' based on the +/- sign from Columns F,G in Sheet 1.

And the value from F & G should appear in Output sheet -Column C (AMount), but this has to be shown as a positive number.

Columns D & E are static will always show the values 'colour' and 'annual', Columns F and G should stay empty in 'output'.

Column H (File) -
In 'input' if there is a value in Column F then in Sheet 'output' Column H we should have value '100'
otherwise if there was a value in column G, then here the value will be '200'.

I hope I was able to explain that properly


Any advice would be great
Thanks

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

i = 1
Do

If .Cells(i, "A").Value = "" Or ( _
.Cells(i, "F").Value = "" And .Cells(i, "G").Value = "") Then

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If

.Cells(i, "C").Value = .Cells(i, "F").Value + .Cells(i, "G").Value
.Cells(i, "A").Value = IIf(.Cells(i, "C").Value < 0, "out", "in")
.Cells(i, "C").Value = Abs(.Cells(i, "C").Value)
If .Cells(i, "F").Value Then .Cells(i, "H").Value = 100
If .Cells(i, "G").Value Then .Cells(i, "H").Value = 200
.Cells(i, "D").Resize(, 4).Value = Array("colour", "annual", "", "")
i = i + 1
Loop Until .Cells(i, "A").Value = "ACTION"

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If

rng.Delete

.Rows(1).Insert
.Range("A1:H1").Value = Array("Cashflow", "ID", "Qty", "Category", "Duration", "", "", "File#")
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[Anomandaris]

thanks Xld

but the code is not running. When I clicked on Debug i highlighted this line..not sure whats wrong.

thanks for the quick response though

.Cells(i, "C").Value = Abs(.Cells(i, "C").Value)

[Bob Phillips]

Try this slight amendment

[vba]

Public Sub ProcessData()
Dim i As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

i = 2
Do

If .Cells(i, "A").Value = "" Or ( _
.Cells(i, "F").Value = "" And .Cells(i, "G").Value = "") Then

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If

.Cells(i, "C").Value = .Cells(i, "F").Value + .Cells(i, "G").Value
.Cells(i, "A").Value = IIf(.Cells(i, "C").Value < 0, "out", "in")
.Cells(i, "C").Value = Abs(.Cells(i, "C").Value)
If .Cells(i, "F").Value Then .Cells(i, "H").Value = 100
If .Cells(i, "G").Value Then .Cells(i, "H").Value = 200
.Cells(i, "D").Resize(, 4).Value = Array("colour", "annual", "", "")
i = i + 1
Loop Until .Cells(i, "A").Value = "ACTION"

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If

rng.Delete

.Range("A1:H1").Value = Array("Cashflow", "ID", "Qty", "Category", "Duration", "", "", "File#")
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[Anomandaris]

Thanks Xld,, it works but it does it on Sheet1(deleting existing data) where i have the inputs, how do i make it appear on Sheet2 without changing any of the values on Sheet1?

thnks

[mdmackillop]

Your question does not ask for the data to be copied/rearranged on sheet 2.
Have you tried copying the data to sheet 2 then running the code?

[GTO]

Thanks Xld,, it works but it does it on Sheet1(deleting existing data) where i have the inputs, how do i make it appear on Sheet2 without changing any of the values on Sheet1?

thnks

Greetings Anomandaris,

I happen to have have followed along while 'practicing' arrays. XLD's code uses the Active Sheet to run on. Thus - from whence you have called the sub, the data will be 're-arranged'.

Are you saying that the wrong sheet was active, or that you want to "read" the data one plpace, and put the new data somewhere else?

Mark

[Anomandaris]

GTO - when sheet 2 is active, the code doesnt work because it doesnt refer to data in sheet 1. But you are right - I want to read data on Sheet 1, but put the new data on another sheet or wkbook.

Mdmac - yes if i copied the data to sheet 2 and ran the code it would work

[Bob Phillips]

[vba]


Public Sub ProcessData()
Dim i As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Worksheets("input").Cells.Copy Worksheets("output").Range("A1")

With Worksheets("output")

i = 2
Do

If .Cells(i, "A").value = "" Or ( _
.Cells(i, "F").value = "" And .Cells(i, "G").value = "") Then

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If

.Cells(i, "C").value = .Cells(i, "F").value + .Cells(i, "G").value
.Cells(i, "A").value = IIf(.Cells(i, "C").value < 0, "out", "in")
.Cells(i, "C").value = Abs(.Cells(i, "C").value)
If .Cells(i, "F").value Then .Cells(i, "H").value = 100
If .Cells(i, "G").value Then .Cells(i, "H").value = 200
.Cells(i, "D").Resize(, 4).value = Array("colour", "annual", "", "")
i = i + 1
Loop Until .Cells(i, "A").value = "ACTION"

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If

rng.Delete

.Range("A1:H1").value = Array("Cashflow", "ID", "Qty", "Category", "Duration", "", "", "File#")
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[Anomandaris]

Thanks a lot for your help Xld, it works well!
i have to work on understanding all this coding stuff more, so i dont bother you guys too much haha.
and thanks mdmac and gto for your advice as well