After reading through the thread, I see that none of the solutions offered thus far have recognized the necessity of treating the rows with a known IDNo differently than the rows with a null IDNo. Before any "filtering" of duplicates can be accomplished, these two sets of rows must be separated from one another and then related back to each other through a join on the Student values and either the Tel3 or Tel1 values, whichever matches (if either do match).

The solution below uses three CTEs to set the stage: MyData represents your original set of raw rows of data; ID_Populated represents the subset of MyData in which the IDNo has a known value; ID_Unpopulated represents the subset of rows in which the IDNo is unknown (null). After that the main query relates ID_Populated to ID_Unpopulated on the Student column and either the Tel3 or Tel1 column whichever matches. Because the join option is a FULL JOIN, if there are no matches between the two subsets, those rows are still retained. If there are still duplicates in the results of this query, it is because there are more than one duplicate entry of the same student. Additional grouping or filtering would be required to address that situation.

with

MyData as

(SELECT 'Alison' as Student, NULL as IDNo, '1111' as Tel3, '4444' as Tel1 UNION ALL

SELECT 'Alison' as Student, '80405088' as IDNo, '1111' as Tel3, '4444' as Tel1 UNION ALL

SELECT 'Amber' as Student, NULL as IDNo, '5555' as Tel3, '6666' as Tel1 UNION ALL

SELECT 'Amber' as Student, NULL as IDNo, '7777' as Tel3, '7777' as Tel1 UNION ALL

SELECT 'Carmen' as Student, NULL as IDNo, '8888' as Tel3, '8888' as Tel1 UNION ALL

SELECT 'Carmen' as Student, '20083085' as IDNo, '9999' as Tel3, '8888' as Tel1),

ID_Populated as (select Student, IDNo, Tel3, Tel1 from MyData where IDNo is not null),

ID_Unpopulated as (select Student, IDNo, Tel3, Tel1 from MyData where IDNo is null)

select

Student = isnull(ID_Populated.Student, ID_Unpopulated.Student),

IDNo = ID_Populated.IDNo,

Tel3 = isnull(ID_Populated.Tel3, ID_Unpopulated.Tel3),

Tel1 = isnull(ID_Populated.Tel1, ID_Unpopulated.Tel1),

Sort = case when ID_Populated.Tel3 = ID_Unpopulated.Tel3 then ID_Populated.Tel3

when ID_Populated.Tel1 = ID_Unpopulated.Tel1 then ID_Populated.Tel1

when isnull(ID_Populated.Tel1, ID_Unpopulated.Tel1) = isnull(ID_Populated.Tel3, ID_Unpopulated.Tel3) then isnull(ID_Populated.Tel1, ID_Unpopulated.Tel1) end

from

ID_Populated full join

ID_Unpopulated on ID_Populated.Student = ID_Unpopulated.Student and (ID_Populated.Tel3 = ID_Unpopulated.Tel3 or ID_Populated.Tel1 = ID_Unpopulated.Tel1)