Same dates in Sheet1 put as one date in Sheet2

[inta251 via OfficeKB.com]

In the workbook I’ve two sheets.
In Sheet1 ColumnA I’ll put dates.
Same date can be repeated to infinity.
Row1 will be ‘Freeze Panes’

Sample:
ColumnA
Row1 Date
Row2 01/01/2007
Row3 01/01/2007..................as one day in Sheet2
Row4 01/02/2007
Row5 01/02/2007
Row6 01/02/2007
Row7 01/02/2007
Row8 01/02/2007..................as one day in Sheet2
Row9 01/03/2007
Row10 01/03/2007
Row11 01/03/2007..................as one day in Sheet2
Row12 01/04/2007..................as one day in Sheet2
Row13 01/05/2007..................as one day in Sheet2
and so on

In Sheet2 ColumnA I need formula or Macro or VB
take new date inserted in Sheet1 and put same date
to Sheet2 ColumnA to the next blank cell.
Row1 will be ‘Freeze Panes’

In my case I need same dates in Sheet1 put as one date
in Sheet2 ColumnA to the next available blank cell.

Sample:
ColumnA
Row1 Date
Row2 01/01/2007
Row3 01/02/2007
Row4 01/03/2007
Row5 01/04/2007
Row6 01/05/2007
and so on

Thanks in advance.
Sincerely, Igor.

 

[vezerid]

Assuming data in Sheet1 is in A2:A21. Assuming your formulas start at
B2 of active sheet. This formula assumes that the dates in Sheet1 are
in increasing order.

The formula is an *array* formula, which means you must commit it with
Ctrl+Shift+Enter.

=INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1))

HTH
Kostis Vezerides

 

[Roger Govier]

Hi

One way would be to use Advanced Filter
Mark data in column A of sheet1, Data>Filter>Advanced Filter>Click
Unique values only>Copy to>Sheets!A1

 

[inta251 via OfficeKB.com]

Thanks for quick respond.
Formula working as I need it.
Only one problem.
If no new date in Sheet1 ColumnA,
cells in Sheet2 ColumnA show #NUM! result

Sample:
Row1 Date
Row2 January 08, 2007
Row3 January 09, 2007
Row4 January 10, 2007
Row5 January 11, 2007
Row6 January 12, 2007
Row7 January 14, 2007
Row8 January 15, 2007
Row9 January 16, 2007
Row10 January 17, 2007
Row11 January 19, 2007
Row12 #NUM!
Row13 #NUM!
Row14 #NUM!
Row15 #NUM!
Row16 #NUM!
Row17 #NUM!
Row18 #NUM!
Row19 #NUM!
Row20 #NUM!
and so on

When new date insert in Sheet1,
#NUM! in Row12 will be change to that date.
And so on.

{=INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW
(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1))}

Thank agaim.
Sincerely, Igor.

 

[vezerid]

If you want the formula to display blank instead of #NUM! you can use
the following variant (again array formula):

=IF(ISNUMBER(SMALL(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),"")

HTH
Kostis

 

[inta251 via OfficeKB.com]

With this formula cell is blank.
When put new date in Sheet1, cell in Sheet2
the same (blank) and cells in the same row start do function,
because they link to that cell in ColumnA. No changes.

vezerid wrote:
If you want the formula to display blank instead of #NUM! you can use
the following variant (again array formula):

{=IF(ISNUMBER(SMALL(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:
$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),INDEX(Sheet1!$A$2:$A$21,SMALL
(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A
$2)+1),ROW()-ROW($B$2)+1)),"")}

 

[inta251 via OfficeKB.com]

Please, disregard previous message.
With few adjustments formula got back to work for me.
Thanks again.
Sincerely, Igor.

{=IF(ISNUMBER(SMALL(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:
$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),INDEX(Sheet1!$A$2:$A$21,SMALL
(IF(Sheet1!$A$3:$A$22<>Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A
$2)+1),ROW()-ROW($B$2)+1)),"")}