report in Excel

Nitin231

New Member
Joined
Mar 22, 2018
Messages
2
Dear All, Please help i have below report with me i want to combine some field

[TABLE="width: 631"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Below is the data available[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transaction Date[/TD]
[TD]Transaction Description[/TD]
[TD] Transaction Code [/TD]
[TD]Net Amount[/TD]
[TD]Name [/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Charges 1[/TD]
[TD] 1000 [/TD]
[TD="align: right"]718.34[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax type 1[/TD]
[TD] 8300 [/TD]
[TD="align: right"]71.83[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax Type 2[/TD]
[TD] 8400 [/TD]
[TD="align: right"]71.83[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Vat Tax[/TD]
[TD] 7500 [/TD]
[TD="align: right"]39.51[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Charges 2[/TD]
[TD] 2003 [/TD]
[TD="align: right"]31.66[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Govt Fee[/TD]
[TD] 7525 [/TD]
[TD="align: right"]15[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax Type 3[/TD]
[TD] 8460 [/TD]
[TD="align: right"]3.17[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax type 4[/TD]
[TD] 8360 [/TD]
[TD="align: right"]3.17[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Charges 1[/TD]
[TD] 1000 [/TD]
[TD="align: right"]718.34[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax type 1[/TD]
[TD] 8300 [/TD]
[TD="align: right"]71.83[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax Type 2[/TD]
[TD] 8400 [/TD]
[TD="align: right"]71.83[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Vat Tax[/TD]
[TD] 7500 [/TD]
[TD="align: right"]39.51[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Charges 2[/TD]
[TD] 2003 [/TD]
[TD="align: right"]31.66[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tourism Dirham Fee[/TD]
[TD] 7525 [/TD]
[TD="align: right"]15[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax Type 3[/TD]
[TD] 8460 [/TD]
[TD="align: right"]3.17[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax type 4[/TD]
[TD] 8360 [/TD]
[TD="align: right"]3.17[/TD]
[TD]AB Wright[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Charges 1[/TD]
[TD] 1000 [/TD]
[TD="align: right"]718.34[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax type 1[/TD]
[TD] 8300 [/TD]
[TD="align: right"]71.83[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax Type 2[/TD]
[TD] 8400 [/TD]
[TD="align: right"]71.83[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Vat Tax[/TD]
[TD] 7500 [/TD]
[TD="align: right"]39.51[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Charges 2[/TD]
[TD] 2003 [/TD]
[TD="align: right"]31.66[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Govt Fee[/TD]
[TD] 7525 [/TD]
[TD="align: right"]15[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax Type 3[/TD]
[TD] 8460 [/TD]
[TD="align: right"]3.17[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD]Tax type 4[/TD]
[TD] 8360 [/TD]
[TD="align: right"]3.17[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Charges 1[/TD]
[TD] 1000 [/TD]
[TD="align: right"]718.34[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax type 1[/TD]
[TD] 8300 [/TD]
[TD="align: right"]71.83[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax Type 2[/TD]
[TD] 8400 [/TD]
[TD="align: right"]71.83[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Vat Tax[/TD]
[TD] 7500 [/TD]
[TD="align: right"]39.51[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Charges 2[/TD]
[TD] 2003 [/TD]
[TD="align: right"]31.66[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tourism Dirham Fee[/TD]
[TD] 7525 [/TD]
[TD="align: right"]15[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax Type 3[/TD]
[TD] 8460 [/TD]
[TD="align: right"]3.17[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2018[/TD]
[TD]Tax type 4[/TD]
[TD] 8360 [/TD]
[TD="align: right"]3.17[/TD]
[TD]Mr. Bean[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Above is the Available Data [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1079"]
<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 12"]Below is the report how I want to define. Every time the upper values changed or the text same should be changed in the below report. The below report should come in the next work sheet.[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Above Data Range can be longer, so I want to define the below report as per the above range it its longer the below report should be longer as well.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 524"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Required Report [/TD]
[TD]


[/TD]
[TD] [/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 587"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Required Report [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Date [/TD]
[TD]Description[/TD]
[TD][/TD]
[TD]Amount [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]AB Wright[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Charges 1 (Charges1+Charges 2)[/TD]
[TD][/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax type 1 (Tax type 1 + Tax type3)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax Type 2 (Tax type 2 + Tax type4)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Vat Tax [/TD]
[TD][/TD]
[TD="align: right"]39.51[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Govt Fee[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Charges 1 (Charges1+Charges 2)[/TD]
[TD][/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax type 1 (Tax type 1 + Tax type3)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax Type 2 (Tax type 2 + Tax type4)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Vat Tax [/TD]
[TD][/TD]
[TD="align: right"]39.51[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Govt Fee[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Subtotal[/TD]
[TD="align: right"]1909.02[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Mr. Bean[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Charges 1 (Charges1+Charges 2)[/TD]
[TD][/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax type 1 (Tax type 1 + Tax type3)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax Type 2 (Tax type 2 + Tax type4)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Vat Tax [/TD]
[TD][/TD]
[TD="align: right"]39.51[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Govt Fee[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Charges 1 (Charges1+Charges 2)[/TD]
[TD][/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax type 1 (Tax type 1 + Tax type3)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Tax Type 2 (Tax type 2 + Tax type4)[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Vat Tax [/TD]
[TD][/TD]
[TD="align: right"]39.51[/TD]
[/TR]
[TR]
[TD]21/1/2018[/TD]
[TD]Govt Fee[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Subtotal[/TD]
[TD="align: right"]1909.02[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Total [/TD]
[TD="align: right"]3818.04[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
please help your assitance will be highly appreciated.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You will still need a additional table to help group the items as your example is setup.
For Example, the original date has an item "Tourism Dirham Fee" that needs a relationship to "Govt Fee"
For a small data set you can use VLOOKUP, but a better solution (and learning opportunity) is to link via the data model.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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