Formula to sort data horizontally

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
Hello Experts,
I am trying to sort the entered data horizantally. The problem is in the amounts in columns N, P, R, T are the same. It has to show the amount as per the entered data. The columns are taking the amount to all the cells as the name of the particulars is the same. How do I rectify this problem.?
Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Enter Data HereAuto Calculated
2DATETypePARTICULARSNUMBERDEBITCREDITDiffHelperIndex matchDateNumberType1Amt2Amt3Amt4Amt5Amt6Amt7Amt8Amt
301-02-2021ReceiptPluto8100-28000.000.0084000.00101-02-20218100ReceiptPluto-28000.00October28000.00October28000.00October28000.00October28000.00 0.00 0.00 0.00
401-02-2021ReceiptOctober81000.00640.0010660.00201-02-20218124ReceiptPluto-15000.00October10660.00October10660.00Mahesh1056.00February2600.00September### 0.00 0.00
501-02-2021ReceiptOctober81000.0024093.006406.00302-02-20218150Receipt 0.00Mars6405.00Rajesh1.00 0.00 0.00 0.00 0.00 0.00
601-02-2021ReceiptOctober81000.005177.000.00402-02-20218159ReceiptPluto-7000.00October6645.00September355.00 0.00 0.00 0.00 0.00 0.00
701-02-2021ReceiptOctober8100-1910.000.00-4720.00503-02-20214400PaymentPluto1180.00June-1180.00June-1180.00June-1180.00June-1180.00June### 0.00 0.00
801-02-2021ReceiptPluto8124-15000.000.00-472.00103-02-20214401PaymentPluto472.00June-472.00June-472.00 0.00 0.00 0.00 0.00 0.00
901-02-2021ReceiptOctober81240.00645.000.00204-02-20218211ReceiptPluto-7000.00October2865.00Monday2733.00September1402.00 0.00 0.00 0.00 0.00
1001-02-2021ReceiptOctober81240.0010015.000.00305-02-20218243ReceiptPluto-6000.00October2723.00November3007.00September270.00 0.00 0.00 0.00 0.00
1101-02-2021ReceiptMahesh81240.001056.000.004 0  0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
1201-02-2021ReceiptFebruary81240.002600.000.005    0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
1301-02-2021ReceiptSeptember81240.00684.000.006    0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Multiple Entries Only
Cell Formulas
RangeFormula
H3:H13H3=SUM(N3+P3+R3+T3+V3+X3+Z3+AB3)
I3:I13I3=IF(A3="","",IF(D2=D3,I2+1,1))
J3:J13J3= IFERROR(INDEX(A:A,MATCH(K3,D:D,0)),"")
K3:K13K3=IFERROR(INDEX($D$3:$D$501,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$501)),0)),"")
L3:L13L3= IFERROR(INDEX(B:B,MATCH(K3,D:D,0)),"")
M6:M13,M3:M4,AA3:AA13,Y3:Y13,W3:W13,U3:U13,S3:S13,Q3:Q13,O3:O13M3=IFERROR(VLOOKUP($K3&"/"&M$2,CHOOSE({1,2},$D$3:$D$501&"/"&$I$3:$I$501,$C$3:$C$501),2,0),"")
N3:N13N3=SUMPRODUCT(--($C$3:$C$501=M3)*($D$3:$D$501=K3)*($E$3:$F$501))
P3:P13P3=SUMPRODUCT(--($C$3:$C$501=O3)*($D$3:$D$501=K3)*($E$3:$F$501))
R3:R13R3=SUMPRODUCT(--($C$3:$C$501=Q3)*($D$3:$D$501=K3)*($E$3:$F$501))
T3:T13T3=SUMPRODUCT(--($C$3:$C$501=S3)*($D$3:$D$501=K3)*($E$3:$F$501))
V3:V13V3=SUMPRODUCT(--($C$3:$C$501=U3)*($D$3:$D$501=K3)*($E$3:$F$501))
X3:X13X3=SUMPRODUCT(--($C$3:$C$501=W3)*($D$3:$D$501=K3)*($E$3:$F$501))
Z3:Z13Z3=SUMPRODUCT(--($C$3:$C$501=Y3)*($D$3:$D$501=K3)*($E$3:$F$501))
AB3:AB13AB3=SUMPRODUCT(--($C$3:$C$501=AA3)*($D$3:$D$501=K3)*($E$3:$F$501))
M5M5=IFERROR(VLOOKUP($K5&"/"&M$2,CHOOSE({1,2},$D$3:$D$501&"/"&$I$3:$I$501,$C$3:$C$501),2,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2062:H1048576,H2:H36Cell Value<0textNO
H2062:H1048576,H2:H36Cell Value>0textNO
 
Last edited:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
So the answers to both of my questions appear to be "yes", as I don't see any exceptions. Given a specific Number (column D in your first post, or column E in Post #9), there is only one date and one transaction type associated with the number. The solution for that case is shown in my Post #5 with a few modifications...perhaps something like this:
MrExcel20210308.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Enter Data Here123456
2DATETypePARTICULARSNUMBERDEBITCREDITHelperDateNumberTypeParticsAmtParticsAmtParticsAmtParticsAmtParticsAmtParticsAmt
31/2/2021ReceiptPluto8100-280000-280001/2/20218100ReceiptPluto-28000October640October24093October5177October-1910  
41/2/2021ReceiptOctober810006406401/2/20218124ReceiptPluto-15000October645October10015Mahesh1056February2600September684
51/2/2021ReceiptOctober8100024093240933/2/20218200otherMahesh-1February-2September3      
61/2/2021ReceiptOctober8100051775177 0             
71/2/2021ReceiptOctober8100-19100-1910               
81/2/2021ReceiptPluto8124-150000-15000               
91/2/2021ReceiptOctober81240645645
101/2/2021ReceiptOctober812401001510015
111/2/2021ReceiptMahesh8124010561056
121/2/2021ReceiptFebruary8124026002600
131/2/2021ReceiptSeptember81240684684
143/2/2021otherMahesh8200-10-1
153/2/2021otherFebruary8200-20-2
163/2/2021otherSeptember8200033
Sheet2
Cell Formulas
RangeFormula
J3:J8J3=IFERROR(INDEX($A$3:$H$16,MATCH($K3,$D$3:$D$16,0),1),"")
K3:K8K3=IFERROR(INDEX($D$3:$D$20,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$20)),0)),"")
L3:L8L3=IFERROR(INDEX($A$3:$H$16,MATCH($K3,$D$3:$D$16,0),2),"")
M3:M8,W3:W8,U3:U8,S3:S8,Q3:Q8,O3:O8M3=IFERROR(INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS($M3:M3)+1)/2)),3),"")
N3:N8,X3:X8,V3:V8,T3:T8,R3:R8,P3:P8N3=IFERROR(INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS($M3:N3)+1)/2)),8),"")
H3:H16H3=SUM(E3:F3)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Solution

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
So the answers to both of my questions appear to be "yes", as I don't see any exceptions. Given a specific Number (column D in your first post, or column E in Post #9), there is only one date and one transaction type associated with the number. The solution for that case is shown in my Post #5 with a few modifications...perhaps something like this:
MrExcel20210308.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Enter Data Here123456
2DATETypePARTICULARSNUMBERDEBITCREDITHelperDateNumberTypeParticsAmtParticsAmtParticsAmtParticsAmtParticsAmtParticsAmt
31/2/2021ReceiptPluto8100-280000-280001/2/20218100ReceiptPluto-28000October640October24093October5177October-1910  
41/2/2021ReceiptOctober810006406401/2/20218124ReceiptPluto-15000October645October10015Mahesh1056February2600September684
51/2/2021ReceiptOctober8100024093240933/2/20218200otherMahesh-1February-2September3      
61/2/2021ReceiptOctober8100051775177 0             
71/2/2021ReceiptOctober8100-19100-1910               
81/2/2021ReceiptPluto8124-150000-15000               
91/2/2021ReceiptOctober81240645645
101/2/2021ReceiptOctober812401001510015
111/2/2021ReceiptMahesh8124010561056
121/2/2021ReceiptFebruary8124026002600
131/2/2021ReceiptSeptember81240684684
143/2/2021otherMahesh8200-10-1
153/2/2021otherFebruary8200-20-2
163/2/2021otherSeptember8200033
Sheet2
Cell Formulas
RangeFormula
J3:J8J3=IFERROR(INDEX($A$3:$H$16,MATCH($K3,$D$3:$D$16,0),1),"")
K3:K8K3=IFERROR(INDEX($D$3:$D$20,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$20)),0)),"")
L3:L8L3=IFERROR(INDEX($A$3:$H$16,MATCH($K3,$D$3:$D$16,0),2),"")
M3:M8,W3:W8,U3:U8,S3:S8,Q3:Q8,O3:O8M3=IFERROR(INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS($M3:M3)+1)/2)),3),"")
N3:N8,X3:X8,V3:V8,T3:T8,R3:R8,P3:P8N3=IFERROR(INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS($M3:N3)+1)/2)),8),"")
H3:H16H3=SUM(E3:F3)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
If the above image is with your formulas, It looks perfect. I will copy the formula to my original sheet with the required range and let you know. Please give me sometime. I will confirm as soon as it is working in my sheet too.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
Sounds good...just let me know what you find out.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
If the above image is with your formulas, It looks perfect. I will copy the formula to my original sheet with the required range and let you know. Please give me sometime. I will confirm as soon as it is working in my sheet too.
Sounds good...just let me know what you find out.
I didn't notice that you changed the formulas in helper. I was wondering what was wrong. Then when I made a new sheet with your formulas I noticed it. It worked perfectly in the query sheet. Now to try it and check in the master base. Thanks KRice. You are too good man. Thanks once again.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Yes, I was overthinking the helper column. A simple sum of debits and credits should suffice since each row includes only one of them, either positive or negative, and it is more convenient to refer to a single helper column for the value.

I'm happy to help.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
Yes, I was overthinking the helper column. A simple sum of debits and credits should suffice since each row includes only one of them, either positive or negative, and it is more convenient to refer to a single helper column for the value.

I'm happy to help.
It is just awesome KRice. 4 Hours of work in just 5 minutes. It worked in the data base perfectly. Thank you So So much....
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
You're welcome, and thanks for the update.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,490
Messages
5,636,629
Members
416,932
Latest member
mm07

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