Hi,
i am trying to copy data from Input sheet and paste it in Output sheet in the format given. Please help with the relevant VBA codes to get the data displayed in Output Sheet. (File attached)
Regards.
Hi,
i am trying to copy data from Input sheet and paste it in Output sheet in the format given. Please help with the relevant VBA codes to get the data displayed in Output Sheet. (File attached)
Regards.
Use Power Query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Interest of EMI", type number}, {"Interest & charges", type number}, {"Repayment", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Value] <> 0)) in #"Filtered Rows"
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks for such a prompt reply and taking effort to write down the query. I am not so conversant with Power Query. Is it possible for code (module) which can be used in Excel in VBA?
Thanks
I can do that for you, but Power Query is worth learning, MS are investing far more in it than they ever will again in VBA.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Option Explicit Sub Blah() Dim LastRow As Long Dim lRow As Long Dim lCol As Long Dim lRowCount As Long Dim WS As Worksheet Dim WSOut As Worksheet Set WS = ThisWorkbook.Worksheets("Input") Set WSOut = ThisWorkbook.Worksheets("Output") LastRow = FindLastRow(WS, "A") lRowCount = 2 With WSOut For lRow = 2 To LastRow For lCol = 2 To 4 Select Case WS.Cells(lRow, lCol) Case Is > 0 .Cells(lRowCount, "A") = WS.Cells(lRow, "A") .Cells(lRowCount, "B") = WS.Cells(1, lCol) .Cells(lRowCount, "C") = WS.Cells(lRow, lCol) lRowCount = lRowCount + 1 End Select Next lCol Next lRow End With Set WS = Nothing Set WSOut = Nothing End Sub Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row End Function
Thanks a lot. It worked like a charm.
Thanks. I will make it a point to learn Power Query, seems to be the way of future.
Just a query. Is it possible to paste the values of "Repayment" in Column D, instead of Column C (currently)?
Hi, is it possible to paste the "values" of "Repayment" in Column D, instead of Column C (currently)?
Option Explicit Sub Blah() Dim LastRow As Long Dim lRow As Long Dim lCol As Long Dim lRowCount As Long Dim WS As Worksheet Dim WSOut As Worksheet Set WS = ThisWorkbook.Worksheets("Input") Set WSOut = ThisWorkbook.Worksheets("Output") LastRow = FindLastRow(WS, "A") lRowCount = 2 With WSOut For lRow = 2 To LastRow For lCol = 2 To 4 Select Case WS.Cells(lRow, lCol) Case Is > 0 .Cells(lRowCount, "A") = WS.Cells(lRow, "A") .Cells(lRowCount, "B") = WS.Cells(1, lCol) If InStr(WS.Cells(1, lCol), "Repayment") Then .Cells(lRowCount, "D") = WS.Cells(lRow, lCol) Else .Cells(lRowCount, "C") = WS.Cells(lRow, lCol) End If lRowCount = lRowCount + 1 End Select Next lCol Next lRow End With Set WS = Nothing Set WSOut = Nothing End Sub Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row End Function
Thank you very much.