Data from Vertical Order from Horizontal Order for Each Ledger account with the Conditions

Hi,

Share the link from where i can download your MS Excel file.

Hi there

Your worksheet is too complex to give you a solution without having access to your file.

To help us to give you a prompt and right solution.

Kindly suggest preparing and uploading a sample file to Onedrive, Dropbox, etc ... and share the link here.

Please first,

a)  Remove any personal/sensitive data.

b) Keep the headers, table structure, and formulas as they actually are.

c) ZIP the file if contains macros, pictures, shapes, or other objects.

d) Provide more details of your scenario and goals.  Also, post the results in the way you expect.

To share the sample file please, follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany

Hi Anna, Thanks for your Email, But little confused, I thought, I have attached a screen shot with wrong Orders.

"Working_data" sheet (where JV booking details are available) and JV is new sheet (here the entries should come as "A" Column as Ledger account & "H" Column as Amount & "I" Column as DR/CR from Working Data sheet).

Example: Rows 3 Says,

Start with "4", 450100, So From "F3:O3" data transpose in JV sheet, same 10 Times ledger account need to File Vertical Order from Horizontal Order. refer the "JV Screen Shot, ( refer the below Screen Shot )

like that, Entry will Flow, But if "1" & "2" Series Ledger account, then update only one time, Entry Amount From Working_data "B" Column value in "J" Column in JV Sheet. ( Line 22 Highlighed )

Hi Gunasekaran

I'm Anna and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Here is an example of VBA code that could be used to transpose data in a range of cells and create journal entries based on that data

Sub TransposeData()

' Declare variables Dim ws As Worksheet Dim rngData As Range Dim rngTransposed As Range Dim rngDR As Range Dim rngCR As Range Dim intRow As Integer Dim intCol As Integer

' Set the range of data to be transposed Set rngData = ThisWorkbook.Sheets("Sheet1"). Range("A1:D10")

' Transpose the data Set rngTransposed = rngData.Cells(1, 1). Resize(rngData.Columns.Count, rngData.Rows.Count)

' Copy the transposed data to a new location rngTransposed.Copy Destination:=ThisWorkbook.Sheets("Sheet2"). Range("A1")

' Loop through all worksheets in the workbook For Each ws In ThisWorkbook.Sheets If ws. Name <> "Sheet1" And ws. Name <> "Sheet2" Then ' Set the range of DR and CR ledger accounts Set rngDR = ws. Range("A1:A10") Set rngCR = ws. Range("B1:B10")

' Loop through the rows of data in the DR and CR ranges For intRow = 1 To rngDR.Rows.Count ' Check the series of the ledger account If Left(rngDR.Cells(intRow, 1), 1) = "1" Or Left(rngDR.Cells(intRow, 1), 1) = "2" Then ' Series 1 or 2, create one line item with the entry amount ws. Cells(intRow, 3) = rngDR.Cells(intRow, 1) ws. Cells(intRow, 4) = rngCR.Cells(intRow, 1) Else ' Series 3 or higher, allocate factors for the entry amount For intCol = 1 To rngTransposed.Columns.Count ws. Cells(intRow, intCol + 2) = rngTransposed.Cells(1, intCol) * rngDR.Cells(intRow, 1) Next intCol End If Next intRow End If Next ws

End Sub

I assume that the data you want to transpose is located in Sheet1, range A1:D10, and the transposed data will be copied to Sheet2, range A1. Additionally, it loops through all sheets in the workbook (excluding sheet1 and sheet2) and checks the first character of the ledger account to see if it is a 1 or 2 and if true it creates a journal entry with the entry amount, otherwise, it creates a journal entry by allocating factors for the entry amount.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

Anna

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.