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:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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
As I was not able to select the whole data,I have sent the image of only2 unmatched numbers.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
As I was not able to select the whole data,I have sent the image of only2 unmatched numbers.
If I change the names in column C5 and C6 I get the correct amounts in columns P and R. How do I rectify the formula to solve this problem.? The formula in column headings 1-8 need to be rectified.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
How would you like to prioritize the sorting operation? It looks as if you want a unique list of "Numbers" in column K (extracted from column D). But then you have a Date column (A) and a Type column (B), which are causing me some confusion. If there are a series of transactions with the same Number (let's say Number = 8100), do all of those transactions occur on the same date, as suggested by your example? Similarly, do the "Types" in column B vary with Number?
 

KRice

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

ADVERTISEMENT

Given a starting example similar to the one you provided (I added a few entries to illustrate a point), we have the original data and a helper column to consolidate the "Amount" of the Debit or Credit.
MrExcel20210308.xlsx
ABCDEFGH
1Enter Data Here
2DATETypePARTICULARSNUMBERDEBITCREDITHelper
31/2/2021ReceiptPluto8100-280000-28000
41/2/2021ReceiptOctober81000640640
51/2/2021ReceiptOctober810002409324093
61/2/2021ReceiptOctober8100051775177
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/2021ReceiptMahesh8200-10-1
153/2/2021ReceiptFebruary8200-20-2
161/2/2021ReceiptSeptember8200033
Sheet2
Cell Formulas
RangeFormula
H3:H16H3=SUMPRODUCT((E3:F3),--(E3:F3<>0))

Then depending on the answers to the questions I asked earlier, this type of approach might be useful for reorganizing the data on a row-by-row basis, where each row has a common "Number" code. My concern is that Dates and Types might still vary for a given common Number code? In other words, the pink highlighted cells have two different dates for Number code 8200, but the formulas I've used are not (yet?) considering that multiple dates might be associated with a Number code, so only one date is extracted. Hence my questions about what a more complete data set might look like. Is it conceivable that one Number code would have multiple dates? Is it conceivable that one Number code might have multiple "Types"...perhaps a Payment and Receipt? Or does a unique Number code ensure that only one date and only one Type will be associated with it?
Cell Formulas
RangeFormula
J3:J8J3=INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS(J3:$M3)+1)/2)),1)
K3:K8K3=LOOKUP(2,1/(COUNTIF($K$2:K2,$D$3:$D$16)=0),$D$3:$D$16)
M3:M8,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,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),"")
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
How would you like to prioritize the sorting operation? It looks as if you want a unique list of "Numbers" in column K (extracted from column D). But then you have a Date column (A) and a Type column (B), which are causing me some confusion. If there are a series of transactions with the same Number (let's say Number = 8100), do all of those transactions occur on the same date, as suggested by your example? Similarly, do the "Types" in column B vary with Number?
I would like to make the formula less complicated and more understandable, but this was the closest I could get to to get the required result. If you have understood my problem, Please do whatever is needful to get the correct answer. In the last queryof reconciliation, you just added +1 in the formula to get the next amount if the amount was the same. I hope you understood. This is the formula of the last query.
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$2500)/((A$3:A$2500=H3)*(C$3:C$2500=J3)*(F$3:F$2500=M3)),COUNTIFS(H$2:H2,H3,J$2:J2,J3,M$2:M2,M3)+1)),"Suspense")
The +1 before the suspense did the trick last time.
GTG. See you tonight. Till then Have a nice day.😄
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Given a starting example similar to the one you provided (I added a few entries to illustrate a point), we have the original data and a helper column to consolidate the "Amount" of the Debit or Credit.
MrExcel20210308.xlsx
ABCDEFGH
1Enter Data Here
2DATETypePARTICULARSNUMBERDEBITCREDITHelper
31/2/2021ReceiptPluto8100-280000-28000
41/2/2021ReceiptOctober81000640640
51/2/2021ReceiptOctober810002409324093
61/2/2021ReceiptOctober8100051775177
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/2021ReceiptMahesh8200-10-1
153/2/2021ReceiptFebruary8200-20-2
161/2/2021ReceiptSeptember8200033
Sheet2
Cell Formulas
RangeFormula
H3:H16H3=SUMPRODUCT((E3:F3),--(E3:F3<>0))

Then depending on the answers to the questions I asked earlier, this type of approach might be useful for reorganizing the data on a row-by-row basis, where each row has a common "Number" code. My concern is that Dates and Types might still vary for a given common Number code? In other words, the pink highlighted cells have two different dates for Number code 8200, but the formulas I've used are not (yet?) considering that multiple dates might be associated with a Number code, so only one date is extracted. Hence my questions about what a more complete data set might look like. Is it conceivable that one Number code would have multiple dates? Is it conceivable that one Number code might have multiple "Types"...perhaps a Payment and Receipt? Or does a unique Number code ensure that only one date and only one Type will be associated with it?
Cell Formulas
RangeFormula
J3:J8J3=INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS(J3:$M3)+1)/2)),1)
K3:K8K3=LOOKUP(2,1/(COUNTIF($K$2:K2,$D$3:$D$16)=0),$D$3:$D$16)
M3:M8,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,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),"")
Good Evening KRice

First thing, Column H is nowhere connected to the data. It is just for my reference if the amounts have been posted correctly. The number is of a single voucher which contains multiple entries on a particular date. If the number changes and the date is same then it is the next voucher on the same date. I want each voucher entry in columns A to F to sort horizontally with their values in columns J to AB depending upon how many particulars are there in a single voucher, i.e., the voucher number. In a single voucher if there are 2 or more same names then it should display them horizontally with their values accordingly.

As the names are same in some vouchers, the horizontal display is taking the first name and amount repeatedly. If I change the name in the original data with a different name it displays correctly.

=INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS(J3:$M3)+1)/2)),1)

In this formula I have changed the ($A$3:$H$16 to ($A$3:$F$16 and also the range to ($A$3:$F$2000 as it is a huge data.

In K3.= =LOOKUP(2,1/(COUNTIF($K$2:K2,$D$3:$D$16)=0),$D$3:$D$16), if I enter the formula the whole data changes. So I would prefer to go with the formula which is =IFERROR(INDEX($D$3:$D$2000,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$2000)),0)),"")

This formula after changing the range is also working right. =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),"")

The formula for N3 is displaying blank. If I get this right, then maybe I will be able to enter the formula for all the other columns.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
I still need clarification on the questions I asked. I'm trying to understand some details about your data in columns A:F. Let me try a different way. In your example, where column D (the "Number" column) displays 8100, will all of those rows also show the same "Date" in column A? In your example, every Number = 8100 shows Date = 01-02-2021. So my first question is attempting to understand whether the number codes displayed in column D are each tied to only one date in column A.

A related question pertains to the column B (the "Type" column). Everywhere I see 8100 in the Number column, I also see Receipt in the Type column. Is it always true that a specific Number Code is always tied to only one "Type"?

The answers to these two questions are critical.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
Given a starting example similar to the one you provided (I added a few entries to illustrate a point), we have the original data and a helper column to consolidate the "Amount" of the Debit or Credit.
MrExcel20210308.xlsx
ABCDEFGH
1Enter Data Here
2DATETypePARTICULARSNUMBERDEBITCREDITHelper
31/2/2021ReceiptPluto8100-280000-28000
41/2/2021ReceiptOctober81000640640
51/2/2021ReceiptOctober810002409324093
61/2/2021ReceiptOctober8100051775177
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/2021ReceiptMahesh8200-10-1
153/2/2021ReceiptFebruary8200-20-2
161/2/2021ReceiptSeptember8200033
Sheet2
Cell Formulas
RangeFormula
H3:H16H3=SUMPRODUCT((E3:F3),--(E3:F3<>0))

Then depending on the answers to the questions I asked earlier, this type of approach might be useful for reorganizing the data on a row-by-row basis, where each row has a common "Number" code. My concern is that Dates and Types might still vary for a given common Number code? In other words, the pink highlighted cells have two different dates for Number code 8200, but the formulas I've used are not (yet?) considering that multiple dates might be associated with a Number code, so only one date is extracted. Hence my questions about what a more complete data set might look like. Is it conceivable that one Number code would have multiple dates? Is it conceivable that one Number code might have multiple "Types"...perhaps a Payment and Receipt? Or does a unique Number code ensure that only one date and only one Type will be associated with it?
Cell Formulas
RangeFormula
J3:J8J3=INDEX($A$3:$H$16,AGGREGATE(15,6,(ROW($D$3:$D$16)-ROW($D$2))/($D$3:$D$16=$K3),INT((COLUMNS(J3:$M3)+1)/2)),1)
K3:K8K3=LOOKUP(2,1/(COUNTIF($K$2:K2,$D$3:$D$16)=0),$D$3:$D$16)
M3:M8,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,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),"")
Query - Corrections in formula for Multiple ledgers matching.xlsx
BCDEFG
1DateVch TypeParticularsVch No.DebitCredit
201-02-2021ReceiptPluto8124-15000.000.00
301-02-2021ReceiptOctober81240.00645.00
401-02-2021ReceiptOctober81240.0010015.00
501-02-2021ReceiptMahesh81240.001056.00
601-02-2021ReceiptFebruary81240.002600.00
701-02-2021ReceiptSeptember81240.00684.00
802-02-2021ReceiptPluto8150-6406.000.00
902-02-2021ReceiptMars81500.006405.00
1002-02-2021ReceiptRajesh81500.001.00
1102-02-2021ReceiptPluto8159-7000.000.00
1202-02-2021ReceiptOctober81590.006645.00
1302-02-2021ReceiptSeptember81590.00355.00
1403-02-2021PaymentPluto44000.001180.00
1503-02-2021PaymentJune4400-236.000.00
1603-02-2021PaymentJune4400-236.000.00
1703-02-2021PaymentJune4400-236.000.00
1803-02-2021PaymentJune4400-236.000.00
1903-02-2021PaymentJune4400-236.000.00
2003-02-2021PaymentPluto44010.00472.00
2103-02-2021PaymentJune4401-236.000.00
2203-02-2021PaymentJune4401-236.000.00
2303-02-2021ReceiptPluto8171-28000.000.00
2403-02-2021ReceiptOctober81710.00640.00
2503-02-2021ReceiptNovember81710.0024093.00
2603-02-2021ReceiptFebruary81710.005177.00
2703-02-2021ReceiptSeptember8171-1910.000.00
2804-02-2021ReceiptPluto8211-7000.000.00
2904-02-2021ReceiptOctober82110.002865.00
3004-02-2021ReceiptMonday82110.002733.00
3104-02-2021ReceiptSeptember82110.001402.00
3205-02-2021ReceiptPluto8243-6000.000.00
3305-02-2021ReceiptOctober82430.002723.00
3405-02-2021ReceiptNovember82430.003007.00
3505-02-2021ReceiptSeptember82430.00270.00
3608-02-2021ReceiptPluto8322-10000.000.00
3708-02-2021ReceiptOctober83220.002540.00
3808-02-2021ReceiptOctober83220.002040.00
3908-02-2021ReceiptFebruary83220.003000.00
4008-02-2021ReceiptOctober83220.003280.00
4108-02-2021ReceiptFebruary83220.001000.00
4208-02-2021ReceiptSeptember8322-1860.000.00
4309-02-2021ReceiptPluto8343-23000.000.00
4409-02-2021ReceiptOctober83430.0020856.00
4509-02-2021ReceiptFebruary83430.001960.00
4609-02-2021ReceiptSeptember83430.00184.00
4710-02-2021PaymentPluto45370.0036590.00
4810-02-2021PaymentSneha4537-36590.400.00
4910-02-2021PaymentRajesh45370.000.40
5010-02-2021ReceiptPluto8364-272250.000.00
5110-02-2021ReceiptVinod83640.00300000.00
5210-02-2021ReceiptMercury8364-30000.000.00
5310-02-2021ReceiptTDS83640.002250.00
5410-02-2021ReceiptPluto8365-181500.000.00
5510-02-2021ReceiptJupiter83650.00200000.00
5610-02-2021ReceiptMercury8365-20000.000.00
5710-02-2021ReceiptTDS83650.001500.00
5810-02-2021ReceiptPluto8374-20000.000.00
5910-02-2021ReceiptFebruary83740.00388.00
6010-02-2021ReceiptTuesday83740.009573.00
6110-02-2021ReceiptOctober83740.001480.00
6210-02-2021ReceiptEarth83740.008922.00
6310-02-2021ReceiptSeptember8374-363.000.00
6411-02-2021PaymentPluto45780.001888.00
6511-02-2021PaymentJune4578-236.000.00
6611-02-2021PaymentJune4578-236.000.00
6711-02-2021PaymentJune4578-236.000.00
6811-02-2021PaymentJune4578-236.000.00
6911-02-2021PaymentJune4578-236.000.00
7011-02-2021PaymentJune4578-236.000.00
7111-02-2021PaymentJune4578-236.000.00
7211-02-2021PaymentJune4578-236.000.00
7311-02-2021ReceiptPluto8389-14543.000.00
7411-02-2021ReceiptAjay83890.0014542.99
7511-02-2021ReceiptRajesh83890.000.01
7611-02-2021ReceiptPluto8391-1918.000.00
7711-02-2021ReceiptJanuary83910.001917.00
7811-02-2021ReceiptRajesh83910.001.00
7912-02-2021PaymentPluto45860.0018902.00
8012-02-2021PaymentFriday4586-18902.310.00
8112-02-2021PaymentRajesh45860.000.31
8212-02-2021PaymentPluto46030.0018500.00
8312-02-2021PaymentMercury4603-20000.000.00
8412-02-2021PaymentTDS46030.001500.00
8512-02-2021PaymentPluto46050.0017196.00
8612-02-2021PaymentDeepak4605-17547.000.00
8712-02-2021PaymentTDS46050.00351.00
8812-02-2021ReceiptPluto8439-7000.000.00
8912-02-2021ReceiptFebruary84390.002000.00
9012-02-2021ReceiptWednesday84390.002338.00
9112-02-2021ReceiptAugust84390.00672.00
9212-02-2021ReceiptSeptember84390.001990.00
9313-02-2021PaymentPluto46200.009250.00
9413-02-2021PaymentMercury4620-10000.000.00
9513-02-2021PaymentTDS46200.00750.00
9613-02-2021ReceiptPluto8467-1742.000.00
9713-02-2021ReceiptRaja84670.001741.00
9813-02-2021ReceiptRajesh84670.001.00
9914-02-2021ContraPluto1523-55000.000.00
10014-02-2021ContraJuly15230.00500.00
10114-02-2021ContraJuly15230.0044500.00
10214-02-2021ContraJuly15230.0010000.00
10315-02-2021ReceiptPluto8503-35304.000.00
10415-02-2021ReceiptMay85030.0035855.00
10515-02-2021ReceiptDecember8503-551.000.00
10616-02-2021ReceiptPluto8518-74000.000.00
10716-02-2021ReceiptFebruary85180.0033359.00
10816-02-2021ReceiptFebruary85180.0010000.00
10916-02-2021ReceiptFebruary85180.003400.00
11016-02-2021ReceiptFebruary85180.00800.00
11116-02-2021ReceiptOctober85180.0010790.00
11216-02-2021ReceiptFebruary85180.0014200.00
11316-02-2021ReceiptOctober85180.00360.00
11416-02-2021ReceiptOctober85180.00520.00
11516-02-2021ReceiptSeptember85180.00571.00
11616-02-2021ReceiptPluto8520-22340.000.00
11716-02-2021ReceiptNeptune85200.0022356.00
11816-02-2021ReceiptDecember8520-16.000.00
11917-02-2021ReceiptPluto8552-40000.000.00
12017-02-2021ReceiptMoon85520.0010000.00
12117-02-2021ReceiptMoon85520.0030000.00
12217-02-2021PaymentPluto46950.001652.00
12317-02-2021PaymentJune4695-236.000.00
12417-02-2021PaymentJune4695-236.000.00
12517-02-2021PaymentJune4695-236.000.00
12617-02-2021PaymentJune4695-236.000.00
12717-02-2021PaymentJune4695-236.000.00
12817-02-2021PaymentJune4695-236.000.00
12917-02-2021PaymentJune4695-236.000.00
13018-02-2021ContraPluto1536-16100.000.00
13118-02-2021ContraStar15360.00100.00
13218-02-2021ContraStar15360.0010000.00
13318-02-2021ContraStar15360.006000.00
13418-02-2021ReceiptPluto8587-10000.000.00
13518-02-2021ReceiptOctober85870.001290.00
13618-02-2021ReceiptFebruary85870.00400.00
13718-02-2021ReceiptSaturday85870.005130.00
13818-02-2021ReceiptFebruary85870.00660.00
13918-02-2021ReceiptSun85870.002980.00
14018-02-2021ReceiptSeptember8587-460.000.00
14119-02-2021PaymentPluto47340.00708.00
14219-02-2021PaymentJune4734-236.000.00
14319-02-2021PaymentJune4734-236.000.00
14419-02-2021PaymentJune4734-236.000.00
14519-02-2021ReceiptPluto8611-35000.000.00
14619-02-2021ReceiptMoon86110.0010000.00
14719-02-2021ReceiptMoon86110.0025000.00
14819-02-2021ReceiptPluto8613-9000.000.00
14919-02-2021ReceiptNovember86130.001295.00
15019-02-2021ReceiptThursday86130.004004.00
15119-02-2021ReceiptSaturn86130.003800.00
15219-02-2021ReceiptSeptember8613-99.000.00
15320-02-2021ReceiptPluto8636-6000.000.00
15420-02-2021ReceiptTuesday86360.00323.00
15520-02-2021ReceiptApril86360.005946.00
15620-02-2021ReceiptSeptember8636-269.000.00
15720-02-2021PaymentPluto47580.00650.00
15820-02-2021PaymentUranus4758-500.000.00
15920-02-2021PaymentUranus4758-150.000.00
16020-02-2021ReceiptPluto8640-16820.000.00
16120-02-2021ReceiptSanjay86400.0016819.00
16220-02-2021ReceiptRajesh86400.001.00
16321-02-2021ReceiptPluto8655-50000.000.00
16421-02-2021ReceiptMoon86550.0025000.00
16521-02-2021ReceiptMoon86550.0025000.00
16622-02-2021ReceiptPluto8698-23000.000.00
16722-02-2021ReceiptOctober86980.0014270.00
16822-02-2021ReceiptSaturday86980.006160.00
16922-02-2021ReceiptOctober86980.002760.00
17022-02-2021ReceiptSeptember8698-190.000.00
17122-02-2021ReceiptPluto8706-7345.000.00
17222-02-2021ReceiptNaveen87060.007335.00
17322-02-2021ReceiptSunday87060.0010.00
17423-02-2021PaymentPluto48160.007604.00
17523-02-2021PaymentMarch4816-14354.000.00
17623-02-2021PaymentTDS48160.006750.00
17723-02-2021ReceiptPluto8731-10000.000.00
17823-02-2021ReceiptSaturday87310.002883.00
17923-02-2021ReceiptOctober87310.001880.00
18023-02-2021ReceiptRamesh87310.005766.00
18123-02-2021ReceiptSeptember8731-529.000.00
18224-02-2021PaymentPluto48230.0029953.00
18324-02-2021PaymentVenus4823-29963.000.00
18424-02-2021PaymentDecember48230.0010.00
18524-02-2021ReceiptPluto8754-4000.000.00
18624-02-2021ReceiptOctober87540.00350.00
18724-02-2021ReceiptRamesh87540.003505.00
18824-02-2021ReceiptSeptember87540.00145.00
18925-02-2021PaymentPluto48490.0012000.00
19025-02-2021PaymentMercury4849-15000.000.00
19125-02-2021PaymentTDS48490.003000.00
19225-02-2021ReceiptPluto8776-15000.000.00
19325-02-2021ReceiptOctober87760.0014380.00
19425-02-2021ReceiptSeptember87760.00620.00
19526-02-2021ReceiptPluto8802-5000.000.00
19626-02-2021ReceiptSaturday88020.00700.00
19726-02-2021ReceiptRani88020.002990.00
19826-02-2021ReceiptPeter88020.001700.00
19926-02-2021ReceiptSeptember8802-390.000.00
Multiple Ledgers

This is the original data cleaned after I received it. This data I copy and paste in the above sheet, it should automatically sort the data by Vch. Np. Every voucher number is different but the dates may be same in some cases or different also. I have to post each voucher number with the data in the horizontal format as shown above. The helper column is to check the number of rows in each voucher.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
464
Office Version
  1. 2019
Platform
  1. Windows
Each voucher number has more than 2 particulars(names) which have to be sorted horizontally in the exact cell. If I had any idea about multiple vlookup, I would use vlookup in cell M3, lookup values as M2 and K3 , and table as A:F and get the result.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

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