RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello Everyone
The columns A to E is the original display of the data exported from a software.
I have to fill the whole data horizontally as shown in the image. But, the amount in column D, which are debit should be displayed in a negative figure and the amount in column E which are credit, should be displayed in a positive figure. Each date has a different data debit and credit wise. Hence the data should be compact without any blanks.
As this is a sample file with 3 entries only, I have manually posted the data just to show the result. (columns G to AB). I am trying to find a solution to display the data horizontally with the help of a formula, but I am not able to do so. I hope with the help of a formula, it is possible. My last option will be a VBA code,
The columns A to E is the original display of the data exported from a software.
I have to fill the whole data horizontally as shown in the image. But, the amount in column D, which are debit should be displayed in a negative figure and the amount in column E which are credit, should be displayed in a positive figure. Each date has a different data debit and credit wise. Hence the data should be compact without any blanks.
As this is a sample file with 3 entries only, I have manually posted the data just to show the result. (columns G to AB). I am trying to find a solution to display the data horizontally with the help of a formula, but I am not able to do so. I hope with the help of a formula, it is possible. My last option will be a VBA code,
Book1 | ||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |||
1 | Original Data | To Display by a Formula or VBA code in the next sheet | ||||||||||||||||||||||||||||
2 | Date | Particulars | Number | Debit | Credit | Date | A | B | C | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | Ledger Name | Amount (Dr)/Cr | |||
3 | 01-02-2021 | January | 10001 | 100 | 01-02-2021 | 10001 | January | -100 | February | 3600 | March | -200 | April | -300 | May | -400 | June | -500 | July | -600 | August | -700 | September | -800 | ||||||
4 | February | 3600 | 02-02-2021 | 10002 | January | -500 | February | 500 | ||||||||||||||||||||||
5 | March | 200 | 03-02-2021 | 10003 | January | -100 | March | -200 | April | -300 | Bank | 650 | CC | -50 | ||||||||||||||||
6 | April | 300 | ||||||||||||||||||||||||||||
7 | May | 400 | ||||||||||||||||||||||||||||
8 | June | 500 | ||||||||||||||||||||||||||||
9 | July | 600 | ||||||||||||||||||||||||||||
10 | August | 700 | ||||||||||||||||||||||||||||
11 | September | 800 | ||||||||||||||||||||||||||||
12 | 02-02-2021 | January | 10002 | 500 | ||||||||||||||||||||||||||
13 | February | 500 | ||||||||||||||||||||||||||||
14 | 03-02-2021 | January | 10003 | 100 | ||||||||||||||||||||||||||
15 | March | 200 | ||||||||||||||||||||||||||||
16 | April | 300 | ||||||||||||||||||||||||||||
17 | Bank | 650 | ||||||||||||||||||||||||||||
18 | CC | 50 | ||||||||||||||||||||||||||||
19 | ||||||||||||||||||||||||||||||
Sheet1 |