Segregate Data based on Date along with required fields

[harinsh]

I am looking for some help in one formula to get the desired output...I have data table something like below...which includes Name, Date, Amt, Type and Place...
I have only Type1, Type2 and South, North...these will not change.

[TABLE="width: 359"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Amt[/TD]
[TD]Type[/TD]
[TD]Place[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] $ 2,082[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] $ 2,110[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] $ 1,723[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 4,528[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 4,554[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 2,943[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]GG[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 4,638[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]HH[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 4,212[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]II[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 2,308[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]JJ[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 2,337[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]KK[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 4,881[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]LL[/TD]
[TD="align: right"]23-Feb-19[/TD]
[TD] $ 3,663[/TD]
[TD]Type2[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]MM[/TD]
[TD="align: right"]23-Feb-19[/TD]
[TD] $ 1,004[/TD]
[TD]Type2[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]NN[/TD]
[TD="align: right"]23-Feb-19[/TD]
[TD] $ 4,559[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]OO[/TD]
[TD="align: right"]24-Feb-19[/TD]
[TD] $ 3,779[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]PP[/TD]
[TD="align: right"]24-Feb-19[/TD]
[TD] $ 1,513[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]QQ[/TD]
[TD="align: right"]24-Feb-19[/TD]
[TD] $ 3,585[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[/TR]
</tbody>[/TABLE]


I need to segregate based on "Date" field...each date should give one set of table..for example given below for 20th Feb...it should segregate whatever available for both North and south data sets separately

[TABLE="width: 397"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]20-Feb-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Place[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Type1 -Amt[/TD]
[TD]Typ2 - Amt[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD="align: right"]20-Feb-19 [/TD]
[TD]AA[/TD]
[TD] $ 2,082[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2082[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Place[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Type1 -Amt[/TD]
[TD]Typ2 - Amt[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD="align: right"]20-Feb-19 [/TD]
[TD]BB[/TD]
[TD] $ 2,110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2110[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] CC[/TD]
[TD] $ 1,723[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1723[/TD]
[/TR]
</tbody>[/TABLE]

 

[jondavis1987]

Is it a pivot table? If so you should just be able to move date into a filter field and have it separate that way. If you're not using a pivot table then i would suggest going that route.

 

[harinsh]

Is it a pivot table? If so you should just be able to move date into a filter field and have it separate that way. If you're not using a pivot table then i would suggest going that route.

No, its not pivot table and normal data set...I forgot to add another criterion called "Open" and "Close" here you have the updated data set and out table also should give like one data for "Open" and one for "Close"

[TABLE="width: 404"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data Set[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Amt[/TD]
[TD]Type[/TD]
[TD]Place[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] $ 2,082[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] $ 2,110[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD] $ 1,723[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 4,528[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 4,554[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 2,943[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]GG[/TD]
[TD="align: right"]21-Feb-19[/TD]
[TD] $ 4,638[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]HH[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 4,212[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]II[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 2,308[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]JJ[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 2,337[/TD]
[TD]Type1[/TD]
[TD]South[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]KK[/TD]
[TD="align: right"]22-Feb-19[/TD]
[TD] $ 4,881[/TD]
[TD]Type1[/TD]
[TD]North[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]LL[/TD]
[TD="align: right"]23-Feb-19[/TD]
[TD] $ 3,663[/TD]
[TD]Type2[/TD]
[TD]North[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]MM[/TD]
[TD="align: right"]23-Feb-19[/TD]
[TD] $ 1,004[/TD]
[TD]Type2[/TD]
[TD]North[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]NN[/TD]
[TD="align: right"]23-Feb-19[/TD]
[TD] $ 4,559[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]OO[/TD]
[TD="align: right"]24-Feb-19[/TD]
[TD] $ 3,779[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]PP[/TD]
[TD="align: right"]24-Feb-19[/TD]
[TD] $ 1,513[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD]QQ[/TD]
[TD="align: right"]24-Feb-19[/TD]
[TD] $ 3,585[/TD]
[TD]Type2[/TD]
[TD]South[/TD]
[TD]Close[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Output require[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-Feb-19[/TD]
[TD]Closed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Place[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Type1 -Amt[/TD]
[TD]Typ2 - Amt[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD]AA[/TD]
[TD] $ 2,082[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2082[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-Feb-19[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Place[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Type1 -Amt[/TD]
[TD]Typ2 - Amt[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD]BB[/TD]
[TD] $ 2,110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2110[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD]CC[/TD]
[TD] $ 1,723[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1723[/TD]
[/TR]
</tbody>[/TABLE]

 

[harinsh]

Hi jondavis1987,

I hope answered your question ...

 

[jondavis1987]

Honestly the only way I know how to do that is to turn it into a pivot table. Move those headings into the filter. Then you can have it all filter the way that you want. I'm not sure how to go about separating them with a formula.

 

[harinsh]

Honestly the only way I know how to do that is to turn it into a pivot table. Move those headings into the filter. Then you can have it all filter the way that you want. I'm not sure how to go about separating them with a formula.

No Pivot, won't work for my output ...I am sure using index/aggregate can be done...