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 |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | Enter Data Here | | |
|---|
| 2 | DATE | Type | PARTICULARS | NUMBER | DEBIT | CREDIT | | Helper |
|---|
| 3 | 1/2/2021 | Receipt | Pluto | 8100 | -28000 | 0 | | -28000 |
|---|
| 4 | 1/2/2021 | Receipt | October | 8100 | 0 | 640 | | 640 |
|---|
| 5 | 1/2/2021 | Receipt | October | 8100 | 0 | 24093 | | 24093 |
|---|
| 6 | 1/2/2021 | Receipt | October | 8100 | 0 | 5177 | | 5177 |
|---|
| 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 | Receipt | Mahesh | 8200 | -1 | 0 | | -1 |
|---|
| 15 | 3/2/2021 | Receipt | February | 8200 | -2 | 0 | | -2 |
|---|
| 16 | 1/2/2021 | Receipt | September | 8200 | 0 | 3 | | 3 |
|---|
|
|---|
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?
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.
