Extract unique values from a table and list along a row

hi Colin. are you still using Excel 2003? do change your MS Off Ver in your profile if you have upgraded. we can provide you better formulas available. i'm guessing you have a new version because you uploaded an xlsx file. it's also good to type out manually what you hope to see in the file. i'm guessing you want the unique products in row 9 based on the Advertiser. so try this array formula in F9:
=IFERROR(INDEX($C$2:$C$16,MATCH(0,IF($B$2:$B$16=$G$6,COUNTIF($E9:E9,$C$2:$C$16)),0)),"")

in F10:F12, i'm guessing you want the unique dates, so:
=IFERROR(INDEX($A$2:$A$16,MATCH(0,IF($B$2:$B$16=$G$6,COUNTIF(F$9:F9,$A$2:$A$16)),0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

not sure how you want the values there, but Pivot Table actually solves the issue. check the file. do note that cereal1 & cereal 1 appeared twice because of the space

You can also use non-array, regular formulas to get unique values

If you using Excel 2007 or higher use these formulas

for Advertisers:

=IFERROR(INDEX($B$2:$B$16,MATCH(0,INDEX(COUNTIF($F$1:F1,$B$2:$B$16),0,0),0)),"")

for Dates

=IFERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($F$9:F9,$A$2:$A$16),0,0),0)),"")

If you still using Excel 2003 use formulas below

=IF(ISERROR(INDEX($B$2:$B$16,MATCH(0,INDEX(COUNTIF($F$1:F1,$B$2:$B$16),0,0),0))),"",INDEX($B$2:$B$16,MATCH(0,INDEX(COUNTIF($F$1:F1,$B$2:$B$16),0,0),0)))


=IF(ISERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($F$9:F9,$A$2:$A$16),0,0),0))),"",INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($F$9:F9,$A$2:$A$16),0,0),0)))

What about a pivot table ?

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Is there any benefits from not using the array method?

The only difference in the array and non-array formulas is that the non-array formulas use an additional INDEX function.

So, the non-array formulas are no more efficient than the array entered formulas.

Some tasks (using formulas) take some amount of time to complete when you apply them to a lot of data. Extracting uniques from 40k rows of data is one of those tasks.