I am hoping someone can give guidance to me on this question.
I have a worksheet that identifies transactions over time. Each transaction has its own row with a date reference and a common customer number. There may be one transaction or many under the customer number, each with different dates. What I want to be able to perform is an analysis of whether the price paid for a transaction has changed over time for transactions within the same customer number. I think I should do this with embedded IF statements along the line of 1st assuring that the customer numbers are the same and if they are comparing the price paid on the date that I am looking at with the price paid on the most recent date for transactions under the common customer number. But since the number of transactions per customer number varies, I cannot see how to do this formulaically. I have 12,000 rows of data to compare. I have zero knowledge of VBA, so am nervous about going that route. Anyhow, I do appreciate the guidance.
This is what the file looks like:
<tbody>
</tbody>
I have a worksheet that identifies transactions over time. Each transaction has its own row with a date reference and a common customer number. There may be one transaction or many under the customer number, each with different dates. What I want to be able to perform is an analysis of whether the price paid for a transaction has changed over time for transactions within the same customer number. I think I should do this with embedded IF statements along the line of 1st assuring that the customer numbers are the same and if they are comparing the price paid on the date that I am looking at with the price paid on the most recent date for transactions under the common customer number. But since the number of transactions per customer number varies, I cannot see how to do this formulaically. I have 12,000 rows of data to compare. I have zero knowledge of VBA, so am nervous about going that route. Anyhow, I do appreciate the guidance.
This is what the file looks like:
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Payor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB12045 | 10/02/2012 | N | 00904-4043-60 | Product A | 30.000 | ABC | AHE790 | 003858 | A4 | Comp 1 | 2.81 | 0.00 | 2.81 | 1.14 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB27654 | 09/29/2012 | Y | 00591-3774-19 | Product B | 7.000 | ABC | NO100L | 003858 | A4 | Comp 1 | 7.60 | 0.00 | 7.60 | 1.48 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB28373 | 09/29/2012 | N | 00904-5824-60 | Product C | 14.000 | ABC | A71696 | 003858 | A4 | Comp 1 | 1.34 | 0.00 | 1.34 | 0.21 | JY3A |
| AB28373 | 10/05/2012 | N | 00904-5824-60 | Product C | 14.000 | ABC | A71696 | 003858 | A4 | Comp 1 | 1.34 | 0.00 | 1.34 | 0.21 | JY3A |
| AB28373 | 10/10/2012 | N | 00904-5824-60 | Product C | 14.000 | DEF | A71697 | 003858 | A4 | Comp 2 | 0.99 | 0.00 | 1.34 | 0.21 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB28378 | 09/29/2012 | N | 00172-3758-80 | Product D | 7.000 | ABC | A71696 | 003858 | A4 | Comp 1 | 2.57 | 0.00 | 2.57 | 0.12 | JY3A |
| AB28378 | 10/05/2012 | N | 00172-3758-80 | Product D | 7.000 | ABC | A71697 | 003858 | A4 | Comp 1 | 2.57 | 0.00 | 2.57 | 0.12 | JY3A |
| AB28378 | 10/10/2012 | N | 00172-3758-80 | Product D | 7.000 | DEF | A71698 | 003858 | A4 | Comp 2 | 2.25 | 0.00 | 2.57 | 0.12 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB29105 | 10/07/2012 | N | 00085-1132-01 | Product E | 13.400 | ABC | M4541A | 003858 | A4 | Comp 1 | 99.00 | 0.00 | 99.00 | 90.92 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB29259 | 10/09/2012 | N | 65162-0668-10 | Product F | 30.000 | DEF | AB8479 | 003858 | A4 | Comp 2 | 5.02 | 0.00 | 5.02 | 3.36 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB29265 | 10/11/2012 | N | 00093-7385-98 | Product G | 30.000 | DEF | SZ965A | 003858 | MD | Comp 2 | 13.80 | 1.10 | 14.90 | 6.16 | JY3A |
| Customer # | Date | Is COB | Product Number | Product Name | Disp Qty | Name | Cardholder ID | BIN | PCN | Processor | TP Total Amt | Patient Pay Amount | Total Claim Price | Acq Cost | Group ID |
| AB29340 | 10/01/2012 | N | 00603-4975-28 | Product H | 42.000 | ABC | C00I1H | 003858 | A4 | Comp1 | 7.94 | 0.00 | 7.94 | 2.96 | JY3A |
| AB29340 | 10/12/2012 | N | 00603-4975-28 | Product H | 42.000 | ABC | C00I1H | 003858 | A4 | Comp 1 | 7.94 | 0.00 | 7.94 | 2.96 | JY3A |
| AB29340 | 10/22/2012 | N | 00603-4975-28 | Product H | 42.000 | DEF | C00I1H | 003858 | A4 | Comp 2 | 7.34 | 0.00 | 7.94 | 2.96 | JY3A |
<tbody>
</tbody>
