match duplicate data in rows and columns in Excel 2003

[Will123]

I have a large spreadsheet with numerous columns & rows. In column C are
patient ID#'s. In column AK are patient claim $ amounts. Some of these
patients are listed twice or three times with different $ amounts. I copy'd
this worksheet to a new tab and sorted by claims over $250k in column AK and
deleted rows less than $250k. But I see that some of these same patients
have smaller $ amounts that were missed in the 250k sort. I want to match
patients on the two wkshts together- the patients with above $250k claims to
the same patients with claims less than $250k,too? thanks.

 

[Bernard Liengme]

Not sure if I get the whole picture but here is my suggestion.
On Sheet1
In C1 I have the text "ID", in C2:C11 I have ID's - some are duplicated
In AK1 have text "Amount", in AK2:AK11 I have random numbers (in range 1 to
500)

I select all of column C; copied & pasted to A1 of Sheet2
Select all of A on Sheet 2 and use Data | Advanced Filter to get a list of
unique IDs

In B2 I used =COUNTIF(Sheet1!C:C,Sheet2!A2) and copied this down the column
by double clicking B2's fill handle, This tells me how many 'claims' were
made by each ID

In C2 I used =SUMIF(Sheet1!C:C,A2,Sheet1!AK:AK), copied down the column;
tell me the total $ amount of all claims

In D2 I used =SUMPRODUCT(--(Sheet1!C1:C10=A2),--(Sheet1!AK1:AK10<100)) to
tell me the number of claims less than 100. Note we cannot use full column
references as in =SUMPRODUCT(--(Sheet1!C:C=A2),--(Sheet1!AK:AK<100)) unless
we have Excel 2007

In E2
=SUMPRODUCT(--(Sheet1!C1:C10=A2),--(Sheet1!AK1:AK10<100),Sheet1!AK1:AK10)
tell me the total of all claims per ID that were less than 100.

Likewise =SUMPRODUCT(--(Sheet1!C1:C10=A2),--(Sheet1!AK1:AK10>=100)) tell me
the number of claims of 100 or more for each ID

And
=SUMPRODUCT(--(Sheet1!C1:C10=A2),--(Sheet1!AK1:AK10>=100),Sheet1!AK1:AK10)
tell me the sum of all claims of100 or more for each ID

best wishes

 

[Bernard Liengme]

Opps! We need absolute references

In D2
=SUMPRODUCT(--(Sheet1!$C$1:$C$10=A2),--(Sheet1!$AK$1:$AK$10<100))
In E2
=SUMPRODUCT(--(Sheet1!$C$1:$C$10=A2),--(Sheet1!$AK$1:$AK$10<100),Sheet1!$AK$1:$AK$10)
Same F2 and G2
Senior moment, sorry
Bernard