SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dupecheck] (
  [id] int identity(1, 1), 
  [Data Area] varchar(255), 
  [Supplier_No] varchar(255), 
  [Name] varchar(255), 
  [Address] varchar(255), 
  [City] varchar(255), 
  [State] varchar(255), 
  [Zip] varchar(255), 
  [Country] varchar(255), 
  [Remit_Address] varchar(255), 
  [Remit_City] varchar(255), 
  [Remit_State] varchar(255), 
  [Remit_Zip] varchar(255), 
  [Remit_Country] varchar(255), 
)

CREATE TABLE [sample_data] (
    [Supplier_No]           varchar(255),
    [Name]                  varchar(255),
    [Address]               varchar(255),
    [City]                  varchar(255),
    [State]                 varchar(255),
    [Zip]                   varchar(255),
    [Country]               varchar(255),
    [Remit_Address]         varchar(255),
    [Remit_City]            varchar(255),
    [Remit_State]           varchar(255),
    [Remit_Zip]             varchar(255),
    [Remit_Country]         varchar(255),
    [cleanAddress]          varchar(255),
    [cleanRemit_Address]    varchar(255),
    CONSTRAINT [suppliers_pk] PRIMARY KEY ([Supplier_No])
)

INSERT INTO [sample_data] VALUES
    ('1039104','Geez Companies','100 Aero Hudson Rd','Streetsboro','OH','44241','','100 Aero Hudson Road','Streetsboro','OH','44241','USA','100 Aero Hudson Rd','100 Aero Hudson Rd'),
    ('1218409','SouthWestern Medical','100 West Balor Ave','Osceola','AR','72370','USA','SouthWestern Medical100 W Balor Ave','Osceola','AR','72370','USA','100 W Balor Ave','SouthWestern Medical100 W Balor Ave'),
    ('1243789','SouthWestern Medical','100 West Balor Ave','Osceola','AR','72370','USA','SouthWestern Medical100 West Balor Ave','Osceola','AR','72370','USA','100 W Balor Ave','SouthWestern Medical100 W Balor Ave'),
    ('1243636','SIRI SYSTEMS','15 BRAD ROAD','WEXFORD','PA','15090','','','','','','','15 BRAD RD',''),
    ('1152482','FLEETWOOD MACK','22 WINDSOCK CT','ADDISON','IL','60101','','PO BOX 951','CHICAGO','IL','60694-5124','','22 WINDSOCK CT','PO BOX 951'),
    ('1224483','Aerospace Junction','211500 Communicate Ave','Mingo Junction','OH','43939','USA','P O Box 99','Mingo Junction','OH','43939','USA','211500 Communicate Ave','PO Box 99'),
    ('1243397','Squeezy Felt','SCHREIBER DIST','NEW KENSINGTON','PA','15068','','','','','','','SCHREIBER DIST',''),
    ('1230895','NERO CO','28 North US State Highway 99','Osceola','AR','72370','USA','PO Box 204','Cape Girardeau','MO','63702-2045','USA','28 N US State Hwy 99','PO Box 204'),
    ('1243782','NERO CO','28 North US State Highway 99','Osceola','AR','72370','USA','PO Box 204','Cape Girardeau','MO','63702-2045','USA','28 N US State Hwy 99','PO Box 204'),
    ('1135880','RICHARD PRYOR SEMINARS','PO BOX 2194','KANSAS CITY','MO','64121-9468','USA','RICHARD PRYOR SEMINARS P O BOX 2194','KANSAS CITY','MO','64121-9468','USA','PO BOX 2194','RICHARD PRYOR SEMINARS PO BOX 2194'),
    ('1241328','INFINITY AND BEYOND','P.O. BOX 169','GASTONIA','NC','28053-0269','USA','','','','','','PO BOX 169',''),
    ('1259522','ZEEBO INC','PO BOX 169','GASSTONIA','NC','28053-0269','USA','','','','','','PO BOX 169',''),
    ('1255253','AT&T','PO Box 50221','Carol Stream','IL','60197','USA','','','','','','PO Box 50221',''),
    ('1135513','AT&T','PO Box 50221','Carol Stream','IL','60197-5080','USA','','','','','','PO Box 50221',''),
    ('1119161','Machine Co, Inc','3306 N Thorne Blvd','Chattanooga','TN','','','PO BOX 5301','CHATTANOOGA','TN','37406','USA','3306 N Thorne Blvd','PO BOX 5301'),
    ('1176587','Topsy Turvy','365 Welmington Road','Chicago','IL','60606','USA','','','','','','365 Welmington Rd',''),
    ('2156671','Topsy Turvvy, Inc.','P.O. Box 55217','Columbus','OH','43081','','365 Welmington Road','Chicago','IL','60606','USA','','365 Welmington Rd')

CREATE TABLE [dupe_addresses](
    [NewAdd] [varchar](255) NULL
)

INSERT INTO [dupe_addresses] VALUES
    ('100 W Balor Ave'),
    ('28 N US State Hwy 99'),
    ('365 Welmington Rd'),
    ('PO BOX 169'),
    ('PO Box 204'),
    ('PO Box 50221'),
    ('SouthWestern Medical100 W Balor Ave')

24 rows affected

with cte 
as
(
    SELECT 
        'Address Match' AS [Reason], 
        pv.[Supplier_No], 
        pv.[Name], 
        pv.[Address], 
        pv.[City], 
        pv.[State], 
        pv.[Zip], 
        pv.[Country], 
        pv.[Remit_Address], 
        pv.[Remit_City], 
        pv.[Remit_State], 
        pv.[Remit_Zip], 
        pv.[Remit_Country],
        row_number()over (partition by supplier_no order by address  ,remit_address )rn,
        dense_rank()over(order by case when(n.[NewAdd] = pv.[cleanAddress] AND ( [Address] <> '' AND [Address] IS NOT NULL ) )
        then cleanaddress else Remit_Address end) rnk
         FROM [dupe_addresses] n 
      LEFT JOIN [sample_data] pv 
        ON 
        (n.[NewAdd] = pv.[cleanAddress] AND ( [Address] <> '' AND [Address] IS NOT NULL ) )
        or 
         (n.[NewAdd] = pv.[cleanRemit_Address] AND ( [Remit_Address] <> '' AND [Remit_Address] IS NOT NULL) )
       WHERE [Supplier_No] IS NOT NULL AND [Supplier_No] <> ''
)
select * from cte where rn=1
order by rnk desc

Reason	Supplier_No	Name	Address	City	State	Zip	Country	Remit_Address	Remit_City	Remit_State	Remit_Zip	Remit_Country	rn	rnk
Address Match	1135513	AT&T	PO Box 50221	Carol Stream	IL	60197-5080	USA						1	7
Address Match	1255253	AT&T	PO Box 50221	Carol Stream	IL	60197	USA						1	7
Address Match	1259522	ZEEBO INC	PO BOX 169	GASSTONIA	NC	28053-0269	USA						1	5
Address Match	1241328	INFINITY AND BEYOND	P.O. BOX 169	GASTONIA	NC	28053-0269	USA						1	5
Address Match	2156671	Topsy Turvvy, Inc.	P.O. Box 55217	Columbus	OH	43081		365 Welmington Road	Chicago	IL	60606	USA	1	4
Address Match	1176587	Topsy Turvy	365 Welmington Road	Chicago	IL	60606	USA						1	3
Address Match	1230895	NERO CO	28 North US State Highway 99	Osceola	AR	72370	USA	PO Box 204	Cape Girardeau	MO	63702-2045	USA	1	2
Address Match	1243782	NERO CO	28 North US State Highway 99	Osceola	AR	72370	USA	PO Box 204	Cape Girardeau	MO	63702-2045	USA	1	2
Address Match	1218409	SouthWestern Medical	100 West Balor Ave	Osceola	AR	72370	USA	SouthWestern Medical100 W Balor Ave	Osceola	AR	72370	USA	1	1
Address Match	1243789	SouthWestern Medical	100 West Balor Ave	Osceola	AR	72370	USA	SouthWestern Medical100 West Balor Ave	Osceola	AR	72370	USA	1	1