Formula to display data of multiple columns in a horizontal order

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. 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,
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Original DataTo Display by a Formula or VBA code in the next sheet
2DateParticularsNumberDebitCreditDateABCLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/CrLedger NameAmount (Dr)/Cr
301-02-2021January1000110001-02-202110001January-100February3600March-200April-300May-400June-500July-600August-700September-800
4February360002-02-202110002January-500February500
5March20003-02-202110003January-100March-200April-300Bank650CC-50
6April300
7May400
8June500
9July600
10August700
11September800
1202-02-2021January10002500
13February500
1403-02-2021January10003100
15March200
16April300
17Bank650
18CC50
19
Sheet1
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows
Good Morning,
I knew this was a tough one. So I hit the sack early.
I have cleaned the original data to make it simpler to apply a formula. I hope someone clearly understands what I am trying to get. Please note there are 2 sheets in this workbook and I am sending both the sheets.
Query to sort multiple data horizontally.xlsx
ABCDE
1DATEPARTICULARSNUMBERDEBITCREDIT
201-02-2021JANUARY1001-100
301-02-2021FEBRUARY10014300
401-02-2021MARCH1001-300
501-02-2021APRIL1001-400
601-02-2021MAY1001-500
701-02-2021JUNE1001-600
801-02-2021JULY1001-700
901-02-2021AUGUST1001-800
1001-02-2021SEPTEMBER1001-900
1101-02-2021JANUARY1002-500
1201-02-2021FEBRUARY1002500
1302-02-2021JANUARY1003-100
1402-02-2021MARCH1003-200
1502-02-2021APRIL1003-300
1602-02-2021BANK1003650
1702-02-2021CC1003-50
18Total-54505450
OriginalData
Cell Formulas
RangeFormula
D18:E18D18=SUM(D2:D17)



the other sheet where I want the data displayed after applying the formula
Query to sort multiple data horizontally.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1DATEANUMBERCPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNTPARTICULARSDR/CR AMOUNT
201-02-20211001JANUARY-100FEBRUARY4300MARCH-300APRIL-400MAY-500JUNE-600JULY-700AUGUST-800SEPTEMBER-900
302-02-20211002JANUARY-50FEBRUARY500
403-02-20211003JANUARY-100MARCH-200APRIL-300BANK650CC-50
Sorted Horizontally
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows
Anybody home....Yu.Hu...
I just watched a video about xlookup function. I think it may be possible to use that function for my problem, but the problem is, I have excel 2019 version and xlookup works in only 365.
 

Forum statistics

Threads
1,147,820
Messages
5,743,387
Members
423,792
Latest member
travisds

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
Top