I want date wise item wise summary report

P

Prodip Das

New Member
Joined
Nov 13, 2015
Messages
4
I want to generate date wise item wise quantitative summary report. But not able to judge the appropriate formula. Would appreciate if any one can help me out to sort out the same.Find below the table.

[TABLE="width: 489"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Buyer's Name[/TD]
[TD]Item[/TD]
[TD]Invoice No[/TD]
[TD]Unit[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Ramesh & Co[/TD]
[TD]Full Plate[/TD]
[TD="align: right"]1[/TD]
[TD]Nos.[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Ramesh & Co[/TD]
[TD]Qtr Plate[/TD]
[TD="align: right"]1[/TD]
[TD]Nos.[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Ramesh & Co[/TD]
[TD]Tumbler[/TD]
[TD="align: right"]1[/TD]
[TD]Nos.[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Prabir Das[/TD]
[TD]Full Plate[/TD]
[TD="align: right"]2[/TD]
[TD]Nos.[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Prabir Das[/TD]
[TD]Tumbler[/TD]
[TD="align: right"]2[/TD]
[TD]Nos.[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Kamal Traders[/TD]
[TD]C/Saucer Set[/TD]
[TD="align: right"]3[/TD]
[TD]Set[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Kamal Traders[/TD]
[TD]Soup Bowl[/TD]
[TD="align: right"]3[/TD]
[TD]Nos.[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Ganesh Acharya[/TD]
[TD]Bowl-Small[/TD]
[TD="align: right"]4[/TD]
[TD]Nos.[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Ganesh Acharya[/TD]
[TD]C/Saucer Set[/TD]
[TD="align: right"]4[/TD]
[TD]Set[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Ganesh Acharya[/TD]
[TD]Soup Bowl[/TD]
[TD="align: right"]4[/TD]
[TD]Nos.[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Vicky Shaw[/TD]
[TD]Qtr Plate[/TD]
[TD="align: right"]5[/TD]
[TD]Nos.[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Sanjay Dey[/TD]
[TD]Tumbler[/TD]
[TD="align: right"]6[/TD]
[TD]Nos.[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Bharati Udyog[/TD]
[TD]Bowl-Small[/TD]
[TD="align: right"]7[/TD]
[TD]Nos.[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

I want to get report using appropriate formula as per following:
[TABLE="width: 272"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Full Plate[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Qtr Plate[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01-04-2015[/TD]
[TD]Tumbler[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]C/Saucer Set[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Soup Bowl[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Bowl-Small[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Qtr Plate[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]04-04-2015[/TD]
[TD]Tumbler[/TD]
[TD="align: right"]70

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
If you insist on having a formula system...

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Date[/TD]
[TD]Buyer's Name[/TD]
[TD]Item[/TD]
[TD]Invoice No[/TD]
[TD]Unit[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Ramesh & Co[/TD]
[TD]Full Plate[/TD]
[TD]
1​
[/TD]
[TD]Nos.[/TD]
[TD]
40​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Ramesh & Co[/TD]
[TD]Qtr Plate[/TD]
[TD]
1​
[/TD]
[TD]Nos.[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Ramesh & Co[/TD]
[TD]Tumbler[/TD]
[TD]
1​
[/TD]
[TD]Nos.[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Prabir Das[/TD]
[TD]Full Plate[/TD]
[TD]
2​
[/TD]
[TD]Nos.[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Prabir Das[/TD]
[TD]Tumbler[/TD]
[TD]
2​
[/TD]
[TD]Nos.[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Kamal Traders[/TD]
[TD]C/Saucer Set[/TD]
[TD]
3​
[/TD]
[TD]Set[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Kamal Traders[/TD]
[TD]Soup Bowl[/TD]
[TD]
3​
[/TD]
[TD]Nos.[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Ganesh Acharya[/TD]
[TD]Bowl-Small[/TD]
[TD]
4​
[/TD]
[TD]Nos.[/TD]
[TD]
60​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Ganesh Acharya[/TD]
[TD]C/Saucer Set[/TD]
[TD]
4​
[/TD]
[TD]Set[/TD]
[TD]
30​
[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Ganesh Acharya[/TD]
[TD]Soup Bowl[/TD]
[TD]
4​
[/TD]
[TD]Nos.[/TD]
[TD]
150​
[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Vicky Shaw[/TD]
[TD]Qtr Plate[/TD]
[TD]
5​
[/TD]
[TD]Nos.[/TD]
[TD]
100​
[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Sanjay Dey[/TD]
[TD]Tumbler[/TD]
[TD]
6​
[/TD]
[TD]Nos.[/TD]
[TD]
70​
[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Bharati Udyog[/TD]
[TD]Bowl-Small[/TD]
[TD]
7​
[/TD]
[TD]Nos.[/TD]
[TD]
100​
[/TD]
[/TR]
</tbody>[/TABLE]


Name A2:A14 Date in Formulas | Name Manager.
Name C2:C14 Item in Formulas | Name Manager.
Name F2:F14 Qty <strike></strike>in Formulas | Name Manager.

Sheet2

<strike></strike>[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Full Plate[/TD]
[TD]
90​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Qtr Plate[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]Tumbler[/TD]
[TD]
100​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]C/Saucer Set[/TD]
[TD]
40​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Soup Bowl[/TD]
[TD]
200​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Bowl-Small[/TD]
[TD]
160​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Qtr Plate[/TD]
[TD]
100​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
4/4/2015​
[/TD]
[TD]Tumbler[/TD]
[TD]
70​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In A2 control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=IFERROR(INDEX(Date,SMALL(IF(FREQUENCY(MATCH(Item&"|"&Date,Item&"|"&Date,0),
  ROW(Item)-ROW(INDEX(Item,1,1))+1),ROW(Item)-ROW(INDEX(Item,1,1))+1),
  ROWS(A$2:A2))),"")

In B2 control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=IFERROR(INDEX(Item,SMALL(IF(FREQUENCY(MATCH(Item&"|"&Date,Item&"|"&Date,0),
  ROW(Item)-ROW(INDEX(Item,1,1))+1),ROW(Item)-ROW(INDEX(Item,1,1))+1),
  ROWS(B$2:B2))),"")

In C2 just enter and copy down:
Rich (BB code): 

=IF($A2="","",SUMIFS(Qty,Date,A2,Item,B2))
<strike></strike>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
create date wise & ledger wise report by vba in excel
Replies
8
Views
2K
oldbrewer
O
A
  • Question Question
I need help generating a summary page.
Replies
8
Views
491
a15457
A
M
  • Question Question
I want to find the Row that the Value in the Table and Column correspond To
Replies
9
Views
765
Eric W
Eric W
D
  • Question Question
Financial Year wise Reports
Replies
1
Views
736
devinali
D

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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
Back
Top