I have a set of columns with data in. I am trying to produce a list of uniqe
data based on dates from these columns. The data looks like this :

A Date
519 Sat 01 Jan
519 Sat 01 Jan
519 Sat 01 Jan
520 Sat 01 Jan
521 Sat 01 Jan
123 Sun 02 Jan
123 Sun 02 Jan
123 Sun 02 Jan
124 Sun 02 Jan
124 Sun 02 Jan
235 Mon 03 Jan
235 Mon 03 Jan
235 Mon 03 Jan

What I want to get is either the list of unique values within column a for
the date of 1st Jan which would result in 519, 520, 521 (preferable) or the
count of unique entries agaisnt the same date in this case 3.

I can extract this data using a macro, but would like the result from a
formual as the data keeps changing. I cant use filter or sort as the data is
being transferered to a seperate sheet for reporting purposes.

Thanks for any help you can provide.

I suggest that you re-consider using Advanced Filter. Use the filter:

Data Filter Advanced Filter... check unique records only

Copy the resulting, truncated, table "somewhere else". This new table can
then be transferred to a separate sheet.
--
Gary''s Student


"ah666" wrote:

I have a set of columns with data in. I am trying to produce a list of uniqe
data based on dates from these columns. The data looks like this :

A Date
519 Sat 01 Jan
519 Sat 01 Jan
519 Sat 01 Jan
520 Sat 01 Jan
521 Sat 01 Jan
123 Sun 02 Jan
123 Sun 02 Jan
123 Sun 02 Jan
124 Sun 02 Jan
124 Sun 02 Jan
235 Mon 03 Jan
235 Mon 03 Jan
235 Mon 03 Jan

What I want to get is either the list of unique values within column a for
the date of 1st Jan which would result in 519, 520, 521 (preferable) or the
count of unique entries agaisnt the same date in this case 3.

I can extract this data using a macro, but would like the result from a
formual as the data keeps changing. I cant use filter or sort as the data is
being transferered to a seperate sheet for reporting purposes.

Thanks for any help you can provide.

For a unique list...

Assumptions:

1) A2 and B2 contain your headers/labels

2) A3:B15 contains your data

3) Column B contains true date values

Formulas:

C2: enter the date of interest, such as 1/1/2005

D2: enter a 0 (zero)

D3, copied down:

=IF((A3<"")*(B3=C$2),IF(ISNA(MATCH(A3&B3,$A$1:A2& $B$1:B2,0)),LOOKUP(9.99
999999999999E+307,$D$2:D2)+1,""),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

E2:

=LOOKUP(9.99999999999999E+307,D:D)

F3, copied down:

=IF(ROW()-ROW($F$3)+1<=$E$2,INDEX($A$3:$A$15,MATCH(ROW()-ROW($F$3)+1,$D$3
:$D$15,0)),"")

For a unique count...

=SUM(IF(FREQUENCY(IF(B3:B15=C2,A3:A15),IF(B3:B15=C 2,A3:A15))0,1))

or

=COUNT(1/FREQUENCY(IF(B3:B15=C2,A3:A15),IF(B3:B15=C2,A3:A15 )))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

In article ,
"ah666" wrote:

I have a set of columns with data in. I am trying to produce a list of uniqe
data based on dates from these columns. The data looks like this :

A Date
519 Sat 01 Jan
519 Sat 01 Jan
519 Sat 01 Jan
520 Sat 01 Jan
521 Sat 01 Jan
123 Sun 02 Jan
123 Sun 02 Jan
123 Sun 02 Jan
124 Sun 02 Jan
124 Sun 02 Jan
235 Mon 03 Jan
235 Mon 03 Jan
235 Mon 03 Jan

What I want to get is either the list of unique values within column a for
the date of 1st Jan which would result in 519, 520, 521 (preferable) or the
count of unique entries agaisnt the same date in this case 3.

I can extract this data using a macro, but would like the result from a
formual as the data keeps changing. I cant use filter or sort as the data is
being transferered to a seperate sheet for reporting purposes.

Thanks for any help you can provide.

Domenic wrote:
[...]

D3, copied down:

=IF((A3<"")*(B3=C$2),IF(ISNA(MATCH(A3&B3,$A$1:A2& $B$1:B2,0)),LOOKUP(9.99
999999999999E+307,$D$2:D2)+1,""),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

[...]

Just ENTER will suffice.

It seems that on my version of Excel (Excel:Mac v.X) CSE is required.
Otherwise, the second IF statement is evaluated as FALSE and returns a
blank. Can you please confirm whether you get the same result?

In article ,
Aladin Akyurek wrote:

Domenic wrote:
[...]

D3, copied down:

=IF((A3<"")*(B3=C$2),IF(ISNA(MATCH(A3&B3,$A$1:A2& $B$1:B2,0)),LOOKUP(9.99
999999999999E+307,$D$2:D2)+1,""),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

[...]

Just ENTER will suffice.

Please forget my reply since I overlooked the concatenation bit in the
formula.

Domenic wrote:
It seems that on my version of Excel (Excel:Mac v.X) CSE is required.
Otherwise, the second IF statement is evaluated as FALSE and returns a
blank. Can you please confirm whether you get the same result?

In article ,
Aladin Akyurek wrote:


Domenic wrote:
[...]


D3, copied down:

=IF((A3<"")*(B3=C$2),IF(ISNA(MATCH(A3&B3,$A$1: A2&$B$1:B2,0)),LOOKUP(9.99
999999999999E+307,$D$2:D2)+1,""),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.


[...]

Just ENTER will suffice.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

A quick question...

Would you have used a helper column instead to concatenate Column A and
Column B, and then use ISNA(MATCH(...)) on the new column?

In article ,
Aladin Akyurek wrote:

Please forget my reply since I overlooked the concatenation bit in the
formula.

Domenic wrote:
It seems that on my version of Excel (Excel:Mac v.X) CSE is required.
Otherwise, the second IF statement is evaluated as FALSE and returns a
blank. Can you please confirm whether you get the same result?

In article ,
Aladin Akyurek wrote:


Domenic wrote:
[...]


D3, copied down:

=IF((A3<"")*(B3=C$2),IF(ISNA(MATCH(A3&B3,$A$1: A2&$B$1:B2,0)),LOOKUP(9.99
999999999999E+307,$D$2:D2)+1,""),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.


[...]

Just ENTER will suffice.