Unique Filtering Needs

[meppwc]

I have a worksheet with data in 10,000 rows
Some of the data in column A is duplicated and some are unique.

I am trying to create a filter on this data and am struggling
I need to be able to list all records that are unique in column A
In addition to
Any duplicate value in column A where column G is NOT blank.

Any assistance greatly appreciated

 

[mackc557]

Are you willing to use a helper column? You could do something like:
=IF(INDIRECT(ADDRESS(ROW(),1))="","",IF(COUNTIF(A:A,INDIRECT(ADDRESS(ROW(),1)))>1,IF(ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(),1)),A:A,0),1)=ADDRESS(ROW(),1),INDIRECT(ADDRESS(ROW(),1)),""),INDIRECT(ADDRESS(ROW(),1))))
and expand that down into all rows of your helper column. It will list all unique values within column A as well as the first instance of a duplicate value. This will not ignore spaces, so "Jerry" and "Jerry " will both show up because one has a space at the end. It is not case-sensitive.

 

[meppwc]

As much as I appreciate the effort you must have put into this formula, it is not providing what I need. Based on what I said in my original post, I need to see all of that data in each row that applies (if that makes sense). So it would seem that once I take into account removing duplicates that do not have data in column G, these should be far less than 10,000 rows of data

 

[JGordon11]

in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl1 = Table.AddIndexColumn(Source,"Index"),
    tbl2 = Table.SelectRows(tbl1, each (List.PositionOf(tbl1[Column1], _[Column1]) = _[Index] or _[Column7]<>null)),
    Result = Table.RemoveColumns(tbl2,{"Index"})
in
    Result

Note for PQ to work your data would need to be a formal excel table (I used a Table named "Table1"). Change "Table1" in the code above to your table name. Also It assumes the data in column A has Header "Column1" and in col G it is "Column7". Change the three occurences of Column1 and Column7 to your column headers.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7		Column1	Column2	Column3	Column4	Column5	Column6	Column7
2	A	43	32	34	17	57	T		A	43	32	34	17	57	T
3	B	60	1	41	55	37	T		B	60	1	41	55	37	T
4	C	3	21	42	38	18			C	3	21	42	38	18
5	D	44	39	50	40	15			D	44	39	50	40	15
6	A	49	30	52	22	56	T		A	49	30	52	22	56	T
7	B	11	51	58	9	59	T		B	11	51	58	9	59	T
8	C	25	24	36	27	29			B	2	53	35	4	26	T
9	D	6	28	23	45	12			C	20	48	16	7	33	T
10	A	47	19	13	54	10			D	8	14	46	5	31	T
11	B	2	53	35	4	26	T
12	C	20	48	16	7	33	T
13	D	8	14	46	5	31	T
14
Sheet1