Query to find similar data..

**dvspriest** · 08-25-2008, 02:56 PM

I have a excel database of customers that I am reciving payments from...
but I have reason to belive they maybe doing some sort of fraud..

now on my excel i basically have four columns...
A1 B1 C1 D1
Customer ID Amount Card Number Checked

now each customer is given a unique reference number.. but can have made multiple payments.. what i need to do is, check if a customer is using the same credit card to make a payment for me or different cards... and also to see if the same card number is being used by multiple customers, now once I've marked it as checked, I do not want it to show up on my report...
but when ever a new payment is recevied from a customer I want it to come back into my report..

is there any way i can do this in excel?

**Ron Coderre** · 08-25-2008, 03:14 PM

Here's a thought...pivot tables?

With your data in A1:D100

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW:
Drag the CustID field here
Drag the CardNum field here

COLUMN: (Leave this area blank)

DATA: Drag the Amount field here
If it doesn't list as Sum of Amount...dbl-click it and set it to Sum

PAGE: Drag the Checked field here

Click [OK]

Select where you want the Pivot Table…Click [Finish].

That will list each CustID and the CardNums they used..with a total.
If you set the PAGE to only show UN-checked items the pivot table
will shows the stats for only those.

For an analysis of who uses which credit cards,
follow the above instructions again....BUT
...put the CardNum field in before the CustID field.

To refresh the Pivot Tables, just right click them and select Refresh Data

Is that something you can work with?

For more info on Pivot Tables, here are some links:
http://www.nickhodge.co.uk/gui/datam...ablereport.htm
http://www.contextures.com/tiptech.html

**dvspriest** · 08-25-2008, 11:33 PM

I'll give that a try...

but what i was thinking was, maybe i could group all the data by customer ID.. and then group them by credit card, and possible cross them check with each other..