This should get you what you need:

WITH

xxx AS

(

SELECTpe.IPCode AS pe_ip,

pr.IPCode AS pr_ip,

pr.ID,

DENSE_RANK() OVER (PARTITION BY pr.IPCode ORDER BY ID) AS x

FROM Person pe

CROSS APPLY [Profile] pr

WHERE pe.IPCode=pr.IPCode

),

qualifiers AS

(

SELECT DISTINCT pr_ip

FROM xxx

WHERE x>1

)

SELECT q.pr_ip , xxx.ID

FROM qualifiers q

CROSS APPLY xxx

WHERE q.pr_ip=xxx.pr_ip

GROUP BY q.pr_ip, ID

Both tables should also have clustered indexes and non-clustered index on the join keys (e.g. on IP_Code). You would also want to include NULL constraints; it's a best practice to make all your columns not-nullable whenever possible.

Including DDL and sample data is super-helpful. It's good to include any constraints and indexes that are present too (if you have not done so).