Customer Records - does current value follow previous

[dtl1975]

Good Morning,

I am hopeful that someone is able to help as this is driving me mad...I am certain that this can can be done with a formula but can't fathom it today....

Basically, I have a set of data with several thousand records

The customer has 3 criteria to identify them (Cust_ID / Payment_Type / Cust_Type) and the payments that they have made and I need to see if the payment received follow from the previous.

This is strightforward but is complicated by the fact that the customer can have more than one customer type and payment record.

Hopefully the above makes sense.

I have attached an example of the dataset I am working with...this includes examples that pass and one that fails validation.

I am currently doing this manually using filters and cut and pasting to separate sheets...but I am hopeful that this can be done via one fomula.

Thanks in advance for any help given.

dtl1975CustQuery_Check.xlsx

[Miroslav R.]

Hi there,
maybe:
CustQuery_Check.xlsx

Indicates difference on the row, which does not match row above...

[dtl1975]

Excellent - much appreciated.

I was trying to do this with three separate lookups and then combining the results and this was still generating errors.

Thank you....

[ARGK]

Hi
have a look at the attached and see if that provided you with what you need.
Good luck
Tony

[dtl1975]

Hi
have a look at the attached and see if that provided you with what you need.
Good luck
Tony

Thanks for this...never even thought to try the Indirect function....(even if I did I doubt I could have come up with valid formula).

[Miroslav R.]

Excellent - much appreciated.

I was trying to do this with three separate lookups and then combining the results and this was still generating errors.

Thank you....

You are welcome!

(thnx for rep)

[excel_one]

Hi dtl1975,

You have already got answers I think. Here are my 2c...

It seems that the gap is only relevant when a certain combination of CUSTID, Payment Type, Cust_Type already exists. Then the From and To should match in two consecutive records. The formula in the attached spreadsheet checks for these three columns to be the same. Then determines whether there is a gap or not. Where the gap check is not relevant, it says 'na'. I have used a simple IF function.

[dtl1975]

Hi excel_one,

Thanks for this...I was using the formulas provided and Miroslav and ARGK and further manuiplating into my real dataset...I was coming across a few issues, not with the formulas but rather the inconsistencies in my dataset.

Your solution will certainly help provide a solution to my problem.

dtl1975