Problem with setting up formula to perform calculation if conditions are met

P

pdxbender

New Member
Joined
Jan 5, 2013
Messages
1
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:

[TABLE="width: 1001"]
<tbody>[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"] Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl64, width: 171, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl64, width: 99, bgcolor: #666699"]Payor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB12045
[/TD]
[TD="class: xl66, bgcolor: white"]10/02/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00904-4043-60
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product A
[/TD]
[TD="class: xl68, bgcolor: white"]30.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]AHE790
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]2.81
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]2.81
[/TD]
[TD="class: xl70, bgcolor: white"]1.14
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB27654
[/TD]
[TD="class: xl66, bgcolor: white"]09/29/2012
[/TD]
[TD="class: xl65, bgcolor: white"]Y
[/TD]
[TD="class: xl65, bgcolor: white"]00591-3774-19
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product B
[/TD]
[TD="class: xl68, bgcolor: white"]7.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]NO100L
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]7.60
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]7.60
[/TD]
[TD="class: xl70, bgcolor: white"]1.48
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB28373
[/TD]
[TD="class: xl66, bgcolor: white"]09/29/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00904-5824-60
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product C
[/TD]
[TD="class: xl68, bgcolor: white"]14.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]A71696
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]1.34
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]1.34
[/TD]
[TD="class: xl70, bgcolor: white"]0.21
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB28373
[/TD]
[TD="class: xl66, bgcolor: white"]10/05/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00904-5824-60
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product C
[/TD]
[TD="class: xl68, bgcolor: white"]14.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]A71696
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]1.34
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]1.34
[/TD]
[TD="class: xl70, bgcolor: white"]0.21
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB28373
[/TD]
[TD="class: xl66, bgcolor: white"]10/10/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00904-5824-60
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product C
[/TD]
[TD="class: xl68, bgcolor: white"]14.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]DEF
[/TD]
[TD="class: xl65, bgcolor: white"]A71697
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 2
[/TD]
[TD="class: xl69, bgcolor: yellow"]0.99
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]1.34
[/TD]
[TD="class: xl70, bgcolor: white"]0.21
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB28378
[/TD]
[TD="class: xl66, bgcolor: white"]09/29/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00172-3758-80
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product D
[/TD]
[TD="class: xl68, bgcolor: white"]7.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]A71696
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]2.57
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]2.57
[/TD]
[TD="class: xl70, bgcolor: white"]0.12
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB28378
[/TD]
[TD="class: xl66, bgcolor: white"]10/05/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00172-3758-80
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product D
[/TD]
[TD="class: xl68, bgcolor: white"]7.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]A71697
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]2.57
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]2.57
[/TD]
[TD="class: xl70, bgcolor: white"]0.12
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB28378
[/TD]
[TD="class: xl66, bgcolor: white"]10/10/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00172-3758-80
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product D
[/TD]
[TD="class: xl68, bgcolor: white"]7.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]DEF
[/TD]
[TD="class: xl65, bgcolor: white"]A71698
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 2
[/TD]
[TD="class: xl69, bgcolor: yellow"]2.25
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]2.57
[/TD]
[TD="class: xl70, bgcolor: white"]0.12
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB29105
[/TD]
[TD="class: xl66, bgcolor: white"]10/07/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00085-1132-01
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product E
[/TD]
[TD="class: xl68, bgcolor: white"]13.400
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]M4541A
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]99.00
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]99.00
[/TD]
[TD="class: xl70, bgcolor: white"]90.92
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB29259
[/TD]
[TD="class: xl66, bgcolor: white"]10/09/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]65162-0668-10
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product F
[/TD]
[TD="class: xl68, bgcolor: white"]30.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]DEF
[/TD]
[TD="class: xl65, bgcolor: white"]AB8479
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 2
[/TD]
[TD="class: xl69, bgcolor: yellow"]5.02
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]5.02
[/TD]
[TD="class: xl70, bgcolor: white"]3.36
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB29265
[/TD]
[TD="class: xl66, bgcolor: white"]10/11/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00093-7385-98
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product G
[/TD]
[TD="class: xl68, bgcolor: white"]30.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]DEF
[/TD]
[TD="class: xl65, bgcolor: white"]SZ965A
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]MD
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 2
[/TD]
[TD="class: xl69, bgcolor: yellow"]13.80
[/TD]
[TD="class: xl70, bgcolor: white"]1.10
[/TD]
[TD="class: xl70, bgcolor: white"]14.90
[/TD]
[TD="class: xl70, bgcolor: white"]6.16
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[TD="class: xl71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 99, bgcolor: #666699"]Customer #
[/TD]
[TD="class: xl64, width: 90, bgcolor: #666699"]Date
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Is COB
[/TD]
[TD="class: xl64, width: 121, bgcolor: #666699"]Product Number
[/TD]
[TD="class: xl63, bgcolor: #666699"]Product Name
[/TD]
[TD="class: xl63, bgcolor: #666699"]Disp Qty
[/TD]
[TD="class: xl63, bgcolor: #666699"]Name
[/TD]
[TD="class: xl64, width: 115, bgcolor: #666699"]Cardholder ID
[/TD]
[TD="class: xl63, bgcolor: #666699"]BIN
[/TD]
[TD="class: xl63, bgcolor: #666699"]PCN
[/TD]
[TD="class: xl63, bgcolor: #666699"]Processor
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]TP Total Amt
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Patient Pay Amount
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Total Claim Price
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Acq Cost
[/TD]
[TD="class: xl64, width: 64, bgcolor: #666699"]Group ID
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB29340
[/TD]
[TD="class: xl66, bgcolor: white"]10/01/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00603-4975-28
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product H
[/TD]
[TD="class: xl68, bgcolor: white"]42.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]C00I1H
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp1
[/TD]
[TD="class: xl69, bgcolor: yellow"]7.94
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]7.94
[/TD]
[TD="class: xl70, bgcolor: white"]2.96
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB29340
[/TD]
[TD="class: xl66, bgcolor: white"]10/12/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00603-4975-28
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product H
[/TD]
[TD="class: xl68, bgcolor: white"]42.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]ABC
[/TD]
[TD="class: xl65, bgcolor: white"]C00I1H
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 1
[/TD]
[TD="class: xl69, bgcolor: yellow"]7.94
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]7.94
[/TD]
[TD="class: xl70, bgcolor: white"]2.96
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]AB29340
[/TD]
[TD="class: xl66, bgcolor: white"]10/22/2012
[/TD]
[TD="class: xl65, bgcolor: white"]N
[/TD]
[TD="class: xl65, bgcolor: white"]00603-4975-28
[/TD]
[TD="class: xl67, width: 171, bgcolor: white"]Product H
[/TD]
[TD="class: xl68, bgcolor: white"]42.000
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]DEF
[/TD]
[TD="class: xl65, bgcolor: white"]C00I1H
[/TD]
[TD="class: xl65, bgcolor: white"]003858
[/TD]
[TD="class: xl65, bgcolor: white"]A4
[/TD]
[TD="class: xl67, width: 99, bgcolor: white"]Comp 2
[/TD]
[TD="class: xl69, bgcolor: yellow"]7.34
[/TD]
[TD="class: xl70, bgcolor: white"]0.00
[/TD]
[TD="class: xl70, bgcolor: white"]7.94
[/TD]
[TD="class: xl70, bgcolor: white"]2.96
[/TD]
[TD="class: xl65, bgcolor: white"]JY3A
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
I would imagine that you could accomplish what you are looking for using a pivot table as opposed to VBA. Put the customer number, then date in the rows and the Amount in the body of the report. Then it should be relatively easy to identify if the cost is changing.

I hope that helps.
 
Upvote 0
You must log in or register to reply here.

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