|
Count or result of matching cells
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. |
| All times are GMT +1. The time now is 09:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com