Formula to sort data horizontally

[RAJESH1960]

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
	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	Enter Data Here							Auto Calculated
2	DATE	Type	PARTICULARS	NUMBER	DEBIT	CREDIT		Diff	Helper	Index matchDate	Number	Type	1	Amt	2	Amt	3	Amt	4	Amt	5	Amt	6	Amt	7	Amt	8	Amt
3	01-02-2021	Receipt	Pluto	8100	-28000.00	0.00		84000.00	1	01-02-2021	8100	Receipt	Pluto	-28000.00	October	28000.00	October	28000.00	October	28000.00	October	28000.00		0.00		0.00		0.00
4	01-02-2021	Receipt	October	8100	0.00	640.00		10660.00	2	01-02-2021	8124	Receipt	Pluto	-15000.00	October	10660.00	October	10660.00	Mahesh	1056.00	February	2600.00	September	###		0.00		0.00
5	01-02-2021	Receipt	October	8100	0.00	24093.00		6406.00	3	02-02-2021	8150	Receipt		0.00	Mars	6405.00	Rajesh	1.00		0.00		0.00		0.00		0.00		0.00
6	01-02-2021	Receipt	October	8100	0.00	5177.00		0.00	4	02-02-2021	8159	Receipt	Pluto	-7000.00	October	6645.00	September	355.00		0.00		0.00		0.00		0.00		0.00
7	01-02-2021	Receipt	October	8100	-1910.00	0.00		-4720.00	5	03-02-2021	4400	Payment	Pluto	1180.00	June	-1180.00	June	-1180.00	June	-1180.00	June	-1180.00	June	###		0.00		0.00
8	01-02-2021	Receipt	Pluto	8124	-15000.00	0.00		-472.00	1	03-02-2021	4401	Payment	Pluto	472.00	June	-472.00	June	-472.00		0.00		0.00		0.00		0.00		0.00
9	01-02-2021	Receipt	October	8124	0.00	645.00		0.00	2	04-02-2021	8211	Receipt	Pluto	-7000.00	October	2865.00	Monday	2733.00	September	1402.00		0.00		0.00		0.00		0.00
10	01-02-2021	Receipt	October	8124	0.00	10015.00		0.00	3	05-02-2021	8243	Receipt	Pluto	-6000.00	October	2723.00	November	3007.00	September	270.00		0.00		0.00		0.00		0.00
11	01-02-2021	Receipt	Mahesh	8124	0.00	1056.00		0.00	4		0			0.00		0.00		0.00		0.00		0.00		0.00		0.00		0.00
12	01-02-2021	Receipt	February	8124	0.00	2600.00		0.00	5					0.00		0.00		0.00		0.00		0.00		0.00		0.00		0.00
13	01-02-2021	Receipt	September	8124	0.00	684.00		0.00	6					0.00		0.00		0.00		0.00		0.00		0.00		0.00		0.00
Multiple Entries Only

Cell Formulas
Range	Formula
H3:H13	H3	=SUM(N3+P3+R3+T3+V3+X3+Z3+AB3)
I3:I13	I3	=IF(A3="","",IF(D2=D3,I2+1,1))
J3:J13	J3	= IFERROR(INDEX(A:A,MATCH(K3,D:D,0)),"")
K3:K13	K3	=IFERROR(INDEX($D$3:$D$501,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$501)),0)),"")
L3:L13	L3	= 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:O13	M3	=IFERROR(VLOOKUP($K3&"/"&M$2,CHOOSE({1,2},$D$3:$D$501&"/"&$I$3:$I$501,$C$3:$C$501),2,0),"")
N3:N13	N3	=SUMPRODUCT(--($C$3:$C$501=M3)*($D$3:$D$501=K3)*($E$3:$F$501))
P3:P13	P3	=SUMPRODUCT(--($C$3:$C$501=O3)*($D$3:$D$501=K3)*($E$3:$F$501))
R3:R13	R3	=SUMPRODUCT(--($C$3:$C$501=Q3)*($D$3:$D$501=K3)*($E$3:$F$501))
T3:T13	T3	=SUMPRODUCT(--($C$3:$C$501=S3)*($D$3:$D$501=K3)*($E$3:$F$501))
V3:V13	V3	=SUMPRODUCT(--($C$3:$C$501=U3)*($D$3:$D$501=K3)*($E$3:$F$501))
X3:X13	X3	=SUMPRODUCT(--($C$3:$C$501=W3)*($D$3:$D$501=K3)*($E$3:$F$501))
Z3:Z13	Z3	=SUMPRODUCT(--($C$3:$C$501=Y3)*($D$3:$D$501=K3)*($E$3:$F$501))
AB3:AB13	AB3	=SUMPRODUCT(--($C$3:$C$501=AA3)*($D$3:$D$501=K3)*($E$3:$F$501))
M5	M5	=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
Cell	Condition		Cell Format	Stop If True
H2062:H1048576,H2:H36	Cell Value	<0	text	NO
H2062:H1048576,H2:H36	Cell Value	>0	text	NO

 

[KRice]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Enter Data Here												1		2		3		4		5		6
2	DATE	Type	PARTICULARS	NUMBER	DEBIT	CREDIT		Helper		Date	Number	Type	Partics	Amt	Partics	Amt	Partics	Amt	Partics	Amt	Partics	Amt	Partics	Amt
3	1/2/2021	Receipt	Pluto	8100	-28000	0		-28000		1/2/2021	8100	Receipt	Pluto	-28000	October	640	October	24093	October	5177	October	-1910
4	1/2/2021	Receipt	October	8100	0	640		640		1/2/2021	8124	Receipt	Pluto	-15000	October	645	October	10015	Mahesh	1056	February	2600	September	684
5	1/2/2021	Receipt	October	8100	0	24093		24093		3/2/2021	8200	other	Mahesh	-1	February	-2	September	3
6	1/2/2021	Receipt	October	8100	0	5177		5177			0
7	1/2/2021	Receipt	October	8100	-1910	0		-1910
8	1/2/2021	Receipt	Pluto	8124	-15000	0		-15000
9	1/2/2021	Receipt	October	8124	0	645		645
10	1/2/2021	Receipt	October	8124	0	10015		10015
11	1/2/2021	Receipt	Mahesh	8124	0	1056		1056
12	1/2/2021	Receipt	February	8124	0	2600		2600
13	1/2/2021	Receipt	September	8124	0	684		684
14	3/2/2021	other	Mahesh	8200	-1	0		-1
15	3/2/2021	other	February	8200	-2	0		-2
16	3/2/2021	other	September	8200	0	3		3
Sheet2

Cell Formulas
Range	Formula
J3:J8	J3	=IFERROR(INDEX($A$3:$H$16,MATCH($K3,$D$3:$D$16,0),1),"")
K3:K8	K3	=IFERROR(INDEX($D$3:$D$20,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$20)),0)),"")
L3:L8	L3	=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:O8	M3	=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:P8	N3	=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:H16	H3	=SUM(E3:F3)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[RAJESH1960]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Enter Data Here												1		2		3		4		5		6
2	DATE	Type	PARTICULARS	NUMBER	DEBIT	CREDIT		Helper		Date	Number	Type	Partics	Amt	Partics	Amt	Partics	Amt	Partics	Amt	Partics	Amt	Partics	Amt
3	1/2/2021	Receipt	Pluto	8100	-28000	0		-28000		1/2/2021	8100	Receipt	Pluto	-28000	October	640	October	24093	October	5177	October	-1910
4	1/2/2021	Receipt	October	8100	0	640		640		1/2/2021	8124	Receipt	Pluto	-15000	October	645	October	10015	Mahesh	1056	February	2600	September	684
5	1/2/2021	Receipt	October	8100	0	24093		24093		3/2/2021	8200	other	Mahesh	-1	February	-2	September	3
6	1/2/2021	Receipt	October	8100	0	5177		5177			0
7	1/2/2021	Receipt	October	8100	-1910	0		-1910
8	1/2/2021	Receipt	Pluto	8124	-15000	0		-15000
9	1/2/2021	Receipt	October	8124	0	645		645
10	1/2/2021	Receipt	October	8124	0	10015		10015
11	1/2/2021	Receipt	Mahesh	8124	0	1056		1056
12	1/2/2021	Receipt	February	8124	0	2600		2600
13	1/2/2021	Receipt	September	8124	0	684		684
14	3/2/2021	other	Mahesh	8200	-1	0		-1
15	3/2/2021	other	February	8200	-2	0		-2
16	3/2/2021	other	September	8200	0	3		3
Sheet2

Cell Formulas
Range	Formula
J3:J8	J3	=IFERROR(INDEX($A$3:$H$16,MATCH($K3,$D$3:$D$16,0),1),"")
K3:K8	K3	=IFERROR(INDEX($D$3:$D$20,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$20)),0)),"")
L3:L8	L3	=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:O8	M3	=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:P8	N3	=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:H16	H3	=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]

Sounds good...just let me know what you find out.

 

[RAJESH1960]

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]

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]

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]

You're welcome, and thanks for the update.