Need help to solve the report by formula

S

Sanjib85

New Member
Joined
Sep 5, 2016
Messages
16
Hi Everyone,

I need help to make this report by Formula. I tried to make it but i can't do it by formula please help me.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 890"]
<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Serial No.[/TD]
[TD]Audit Date[/TD]
[TD]Account Name[/TD]
[TD]Partner Name[/TD]
[TD]Campaign Name[/TD]
[TD]Campaign Type[/TD]
[TD]Call Type (General Audit / Lead Audit)[/TD]
[TD]Name of Prospect[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29-Jun-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]13-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]14-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]15-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]18-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]18-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect9[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]19-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect10[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]19-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect11[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]21-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect12[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]21-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect13[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]21-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect14[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]22-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect15[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]26-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect16[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]27-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect17[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]29-Jul-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect18[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]8-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]8-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect20[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]9-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect21[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]11-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect22[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]12-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect23[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]17-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect24[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]17-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect25[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]18-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect26[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]19-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect27[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]22-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect28[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]23-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect29[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]24-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect30[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]25-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]26-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect32[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]31-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect33[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]31-Aug-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect34[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]1-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect35[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]2-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect36[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]6-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect37[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]6-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect38[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]9-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect39[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]13-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]15-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]16-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect42[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]16-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect43[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]19-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect44[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]19-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect45[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]21-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect46[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]22-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect47[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]23-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect48[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]26-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect49[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]26-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect50[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]27-Sep-16[/TD]
[TD] [/TD]
[TD]ABCD[/TD]
[TD] [/TD]
[TD]TQL[/TD]
[TD]Lead Audit[/TD]
[TD]Prospect51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD][TABLE="width: 220"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]No of Prospect[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Does a prospect occur more than once in the same month? If it does, do we count every occurrence of it or just once?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Auto-populate data from multiple sheets into one master sheet- Help
Replies
1
Views
294
alansidman
A
S
  • Question Question
Problem with x-axis labels in excel chart
Replies
1
Views
375
sharshra
S
K
  • Solved
Modify formula to insert blank rows into sequence
Replies
3
Views
217
Fluff
Fluff
Z
  • Solved
Sum of multiple rows for each employee
2
Replies
11
Views
717
DJunqueira
DJunqueira
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
452
amolvijay
A

Forum statistics

Threads
1,223,247
Messages
6,171,004
Members
452,374
Latest member
keccles

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