Extract next unique value for column name

[DashboardNovice]

In the attached file, on sheet1 in column J, I have date/time values. Each value repeats several times. There are 5 unique values in this case which I color coded so they stand out.

On sheet2, I would like to use each unique value as a field name in the gray cells on row 1.
I prepopulated C1 to show what I am looking for. Cell D1 would have the next unique value which is 7/22/2014 5:04PM. Then E1 would have 7/29/2014 6:18PM, etc.

Any suggestions would help. Would it be necessary to transpose somehow?

 

[Mike H..]

In the attached file, on sheet1 in column J, I have date/time values. Each value repeats several times. There are 5 unique values in this case which I color coded so they stand out.

On sheet2, I would like to use each unique value as a field name in the gray cells on row 1.
I prepopulated C1 to show what I am looking for. Cell D1 would have the next unique value which is 7/22/2014 5:04PM. Then E1 would have 7/29/2014 6:18PM, etc.

Any suggestions would help. Would it be necessary to transpose somehow?

Hi,

Have a look at your file, the formula is in all the grey cells. It returns blanks when it runs out of values. Note it's an ARRAY formula and you enter it like this.

=IFERROR(INDEX(Sheet1!$J$3:$J$46,MATCH(0,COUNTIF($B$1:B1,Sheet1!$J$3:$J$46),0)),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[Bhawani]

try below link
http://www.get-digital-help.com/200...-and-the-duplicates-in-excel-from-one-column/

by using above link your array function will be
=INDEX(Sheet1!$J$3:$J$47,MATCH(0,COUNTIF($B$1:B1,Sheet1!$J$3:$J$47),0))
Press Ctrl+Shift+Enter file is attached

 

[DashboardNovice]

Hi,

Have a look at your file, the formula is in all the grey cells. It returns blanks when it runs out of values. Note it's an ARRAY formula and you enter it like this.

=IFERROR(INDEX(Sheet1!$J$3:$J$46,MATCH(0,COUNTIF($B$1:B1,Sheet1!$J$3:$J$46),0)),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Thank you. That's what I was looking for.