So I am trying to make an excel sheet for my product development department to keep track of receipts with their totals and a break down of these receipts (each item assigned a category "project" was bought for). What I am trying to do is have the Date, Location, Receipt Number (Which I am going to assign according to date/how many receipts from that day), Category, Item Description, and Price on one Sheet 1 and on Sheet 2 I will have Date, Receipt Number, and the Receipt Total.
Sheet 1
<tbody>
</tbody>
Sheet 2
<tbody>
</tbody>
<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>So what I am trying to do is as I enter date in the column Receipt Number from Sheet 1, Excel will automatically fill in the data into Sheet 2 in the column Receipt Number, but I do not want any duplicates in Sheet 2 to show up. This is due to I want to be able to take the individual information from sheet 1 and have it sum the total of the receipt into Sheet 2 for me for each receipt so I am not having to look at a lot of repeated numbers/prices. So if i want to look at just one receipt I can use Sheet 2 and compare to see if the price matches what is on the paper receipt.
I have tried using the formula below in Sheet 2 for cell B2 and did not have any luck:
=INDEX(Sheet1!$C$2:Sheet1!$C$5,MATCH(0,INDEX(COUNTIF($C$1:C1,Sheet1!$C$2:Sheet1!$C$5),0,0),0))
I have a feeling that I may need to use a VBA or Macro for this, but I am unfamiliar with VBA so I am not very sure how to go about using it if what I am trying to do is possible.
Any help would be great! Thanks!
Sheet 1
Date | Location | Receipt Number | Category | Item Description | Price |
07/01/2016 | Fry's Grocer | 0701A | Project 1 | ||
0701B | |||||
0701B | |||||
0701B | |||||
0701C |
<tbody>
</tbody>
Sheet 2
Date | Receipt Number | Receipt Total |
0701A | ||
0701B | ||
0701C |
<tbody>
</tbody>
|
<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
I have tried using the formula below in Sheet 2 for cell B2 and did not have any luck:
=INDEX(Sheet1!$C$2:Sheet1!$C$5,MATCH(0,INDEX(COUNTIF($C$1:C1,Sheet1!$C$2:Sheet1!$C$5),0,0),0))
I have a feeling that I may need to use a VBA or Macro for this, but I am unfamiliar with VBA so I am not very sure how to go about using it if what I am trying to do is possible.
Any help would be great! Thanks!