Create Unique Sorted List from 3 Columns

[ootkhopdi]

Hi
i have 3 columns data as various dates like this

Date 1 date 2 date 3
01-01-2006 04-04-2007 05-09-2008
01-07-2006
01-07-2007
01-07-2008
01-07-2009

now i want to create a list of dates like
01-01-2006
01-07-2006
04-04-2007
01-07-2007
01-07-2008
05-09-2008
01-07-2009


how can i got it,
as create list with unique data and sorted list

please give me solution with formula step.. to understand easily


thanks in advance

 

[oldbrewer]

[TABLE="width: 795"]
<colgroup><col><col span="2"><col span="8"></colgroup><tbody>[TR]
[TD]Date 1 date 2 date 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]01-01-2006 04-04-2007 05-09-2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]now i want to create a list of dates like[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/04/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2006[/TD]
[TD="align: right"]04/04/2007[/TD]
[TD="align: right"]05/09/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]your data in columns[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2006[/TD]
[TD][/TD]
[TD="colspan: 2"]copy first row[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2007[/TD]
[TD][/TD]
[TD="colspan: 2"]select first empty cell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2008[/TD]
[TD][/TD]
[TD="colspan: 4"]edit copy paste special values transpose[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2006[/TD]
[TD][/TD]
[TD="colspan: 2"]delete first row[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/04/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2006[/TD]
[TD][/TD]
[TD="colspan: 2"]sort ascending[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/04/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2008[/TD]
[TD][/TD]
[TD="colspan: 8"]if you expect duplicate dates use a macro to run through and delete duplicates[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] [/td][td][/td][td][/td][td][/td][td]

7​

[/td][/tr]
[tr][td]

2​

[/td][td]

1/1/2006​

[/td][td]

4/4/2007​

[/td][td]

5/9/2008​

[/td][td][/td][td]List[/td][/tr]
[tr][td]

3​

[/td][td]

1/7/2006​

[/td][td]

1/7/2006​

[/td][td][/td][td][/td][td]

1/1/2006​

[/td][/tr]
[tr][td]

4​

[/td][td]

1/7/2007​

[/td][td][/td][td]

1/7/2007​

[/td][td][/td][td]

1/7/2006​

[/td][/tr]
[tr][td]

5​

[/td][td]

1/7/2008​

[/td][td][/td][td][/td][td][/td][td]

1/7/2007​

[/td][/tr]
[tr][td]

6​

[/td][td]

1/7/2009​

[/td][td][/td][td][/td][td][/td][td]

4/4/2007​

[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1/7/2008​

[/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td]

5/9/2008​

[/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td]

1/7/2009​

[/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In E1 just enter:

=SUM(IF(FREQUENCY(A2:C6,A2:C6),1))

In E3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$2:E2)>$E$1,"",MIN(IF(ISNA(MATCH(SMALL($A$2:$C$6,ROW(INDIRECT("1:"&COUNT($A$2:$C$6)))),$E$2:E2,0)),SMALL($A$2:$C$6,ROW(INDIRECT("1:"&COUNT($A$2:$C$6)))))))

 

[ootkhopdi]

Thanks Aladin...
it works great....


But Can u please tell me stepwise

how it works...

 

[Aladin Akyurek]

Thanks Aladin...
it works great....


But Can u please tell me stepwise

how it works...

1. The formula of E1

=SUM(IF(FREQUENCY(A2:C6,A2:C6),1))

computes the amount of different numbers the data range contains.

2. The SMALL bit

SMALL($A$2:$C$6,ROW(INDIRECT("1:"&COUNT($A$2:$C$6))))

creates a sorted array of numbers from the numeric range A2:C6 from smallest to largest. The ROW part here runs from 1 to the amount of numeric values in the data range. This corresponds to k in the function syntax:

small(array,k)

3. The MIN part picks one by one the smallest numeric value from the sorted array of numbers which is not yet in the growing list $E$2:E2, $E$2:E3, $E$2:E4, etc. until it gets them all. If 1/7/2006 for example is already in the growing list, another instance of 1/7/2006 will be skipped by the inner IF and not be seen by the MIN function.

Hope this helps.

 

[Mike Szczesny]

Can you use something like this. Assume range1, range2, and range3 re A2:A5,B2:B5, and C2:C5. Unique text is in E1. Place formula is E2 [TABLE="width: 87"]
<tbody>[TR]
[TD="width: 87"] =IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$5,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$5),0)),INDEX($B$2:$B$5,MATCH(0,COUNTIF($E$1:E1,$B$2:$B$6),0))),INDEX($C$2:$C$5,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$5),0)))," ")

Use C-S-E copy down. The you can select cell in range and sort data ascending.

Mike Szczesny[/TD]
[/TR]
</tbody>[/TABLE]

 

[ootkhopdi]

1. The formula of E1

=SUM(IF(FREQUENCY(A2:C6,A2:C6),1))

computes the amount of different numbers the data range contains.

2. The SMALL bit

SMALL($A$2:$C$6,ROW(INDIRECT("1:"&COUNT($A$2:$C$6))))

creates a sorted array of numbers from the numeric range A2:C6 from smallest to largest. The ROW part here runs from 1 to the amount of numeric values in the data range. This corresponds to k in the function syntax:

small(array,k)

3. The MIN part picks one by one the smallest numeric value from the sorted array of numbers which is not yet in the growing list $E$2:E2, $E$2:E3, $E$2:E4, etc. until it gets them all. If 1/7/2006 for example is already in the growing list, another instance of 1/7/2006 will be skipped by the inner IF and not be seen by the MIN function.

Hope this helps.

Thanks...i understand it how it works..thanks once again

 

[Aladin Akyurek]

Thanks...i understand it how it works..thanks once again

You are welcome.