Same dates in Sheet1 put as one date in Sheet2
 

In the workbook Ive two sheets.
In Sheet1 ColumnA Ill 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.

--
Message posted via http://www.officekb.com

Same dates in Sheet1 put as one date in Sheet2
 

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$2 2<Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1))

HTH
Kostis Vezerides

inta251 via OfficeKB.com wrote:
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.

--
Message posted via http://www.officekb.com

Same dates in Sheet1 put as one date in Sheet2
 

Hi

One way would be to use Advanced Filter
Mark data in column A of sheet1, DataFilterAdvanced FilterClick
Unique values onlyCopy toSheets!A1

--
Regards

Roger Govier


"inta251 via OfficeKB.com" <u30987@uwe wrote in message
news:6c825d5579942@uwe...
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.

--
Message posted via http://www.officekb.com

Same dates in Sheet1 put as one date in Sheet2
 

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Same dates in Sheet1 put as one date in Sheet2
 

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(She et1!$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 wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Same dates in Sheet1 put as one date in Sheet2
 

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(Sheet 1!$A$2:$A$21)-ROW(Sheet1!$A
$2)+1),ROW()-ROW($B$2)+1)),"")}

--
Message posted via http://www.officekb.com

Same dates in Sheet1 put as one date in Sheet2
 

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(Sheet 1!$A$2:$A$21)-ROW(Sheet1!$A
$2)+1),ROW()-ROW($B$2)+1)),"")}

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1