formula to list dates in a table (Listing duplicate dates only once)

**JRidge** · 10-08-2013, 10:31 AM

Hi,

I am attemping to write an Array formula that checks a set Table and then lists all the dates that are entered in it in a new column in the correct order and also listing duplicate dates only once.

My first try :

Please Login or Register  to view this content.

English Translation:

Please Login or Register  to view this content.

This does mostly what i would like except it doesn't list the dates in order and it also counts the Empty cell as 0.Jan.

1. How can i adapt this so it lists the dates in order.
2. That it does not count the empty cells and give 0.Jan

And lastly is there anyway i can trigger this formula to run with a button:

Hi XOR LO,

Attached File.

Top table is where dates are imputed and it copies them to bottom table coloumn B

Many thanks for any help.

Regards

J

**XOR LX** · 10-08-2013, 10:43 AM

Hi again J,

You're really going to be much better off posting an attachment with this as that's quite a large formula to have to reconstruct in order to test.

Regards

**JRidge** · 10-08-2013, 10:49 AM

Originally Posted by XOR LX

Hi again J,

You're really going to be much better off posting an attachment with this as that's quite a large formula to have to reconstruct in order to test.

Regards

Attached in Original post

regards

J

**XOR LX** · 10-08-2013, 12:13 PM

Hi,

That looks like an old David Hager approach you've tried to adapt, but of course that method is not designed to produce returns in size order, and you have the issue of blanks as well. Instead, try this in B83 and copy down as required:

=IFERROR(SMALL(IF(FREQUENCY(SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>""))))),SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>""))))))>0,SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>"")))))),ROUNDUP(ROWS($1:1)/5,0)),"")

Regards

**JRidge** · 10-09-2013, 03:18 AM

Originally Posted by XOR LX

Hi,

That looks like an old David Hager approach you've tried to adapt, but of course that method is not designed to produce returns in size order, and you have the issue of blanks as well. Instead, try this in B83 and copy down as required:

=IFERROR(SMALL(IF(FREQUENCY(SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>""))))),SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>""))))))>0,SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>"")))))),ROUNDUP(ROWS($1:1)/5,0)),"")

Regards

Hi XOR LX,

Translated:

=WENNFEHLER(KKLEINSTE(WENN(HÄUFIGKEIT(KKLEINSTE(WENN($C$8:$AG$48<>"";$C$8:$AG$48);ZEILE(INDIREKT("1:"&SUMMENPRODUKT(--($C$8:$AG$48<>"")))));KKLEINSTE(WENN($C$8:$AG$48<>"";$C$8:$AG$48);ZEILE(INDIRECT("1:"&SUMMENPRODUKT(--($C$8:$AG$48<>""))))))>0;KKLEINSTE(WENN($C$8:$AG$48<>"";$C$8:$AG$48);ZEILE(INDIREKT("1:"&SUMMENPRODUKT(--($C$8:$AG$48<>""))))));AUFRUNDEN(ZEILEN($1:1)/5;0));"")

Doesn't do anything... Have i done something wrong.

Regards

J

**JRidge** · 10-09-2013, 03:20 AM

Originally Posted by XOR LX

Hi,

That looks like an old David Hager approach you've tried to adapt, but of course that method is not designed to produce returns in size order, and you have the issue of blanks as well. Instead, try this in B83 and copy down as required:

=IFERROR(SMALL(IF(FREQUENCY(SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>""))))),SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>""))))))>0,SMALL(IF($C$8:$AG$48<>"",$C$8:$AG$48),ROW(INDIRECT("1:"&SUMPRODUCT(--($C$8:$AG$48<>"")))))),ROUNDUP(ROWS($1:1)/5,0)),"")

Regards

Hi XOR LX,

Translated:

=WENNFEHLER(KKLEINSTE(WENN(HÄUFIGKEIT(KKLEINSTE(WENN($C$8:$AG$48<>"";$C$8:$AG$48);ZEILE(INDIREKT("1:"&SUMMENPRODUKT(--($C$8:$AG$48<>"")))));KKLEINSTE(WENN($C$8:$AG$48<>"";$C$8:$AG$48);ZEILE(INDIRECT("1:"&SUMMENPRODUKT(--($C$8:$AG$48<>""))))))>0;KKLEINSTE(WENN($C$8:$AG$48<>"";$C$8:$AG$48);ZEILE(INDIREKT("1:"&SUMMENPRODUKT(--($C$8:$AG$48<>""))))));AUFRUNDEN(ZEILEN($1:1)/5;0));"")

Doesn't do anything... Have i done something wrong.

Regards

J

**XOR LX** · 10-09-2013, 03:22 AM

Did you array-enter it?

Regards

**JRidge** · 10-09-2013, 03:25 AM

Yep but the cell is empty, no dates are copied acroos?

**XOR LX** · 10-09-2013, 03:31 AM

I hope you've not been messing about with those merged cells too much!

See attached.

Regards

**JRidge** · 10-09-2013, 03:35 AM

Originally Posted by XOR LX

I hope you've not been messing about with those merged cells too much!

See attached.

Regards

I love my merged cells........

Problem sorted C and K in INDIRECT -INDIREKT.....forgot to change 1.....

Are you ready for the next problem..

Regards

J

**XOR LX** · 10-09-2013, 03:45 AM

Ah, I should've spotted it myself.

Next problem?!!?

Regards

**JRidge** · 10-09-2013, 04:07 AM

Using the Attachment you uploaded...

in the top table we now have dates for the courses on the left..
On the far right we have the coloumns:
AH (Max people on course)
AI (How many peopl need course)
AJ (How many goups)

In the table at the bottum is where the instructors are applied to each course.

Is there a way where we can:

Formula to look at each course, Work out how many instructors are needed for the courses on a specific day and shade the appropiate box in the bottom table so we can see straight away where instructors are needed.

I have tried to show the expected outcome in the attached table:

The table above with dates does not have shading (Just done it to show the expected outcome)
The table below needs shading where an instructor is required (doesn't need to be different colours, Just done so to show example)

Any ideas or is this wishful thinking.......

Regards

J

**XOR LX** · 10-09-2013, 08:42 AM

HiJ,

Can you just explain a bit more about this?

Not sure I understand how you're 'grouping' your dates: why do the four 12/Sep entries for TS01 in C8:F8 form one 'group' and the four in G8:J8 another, in the sense that you wish to have just two cells shaded in the lower table for TS01 12/Sep?

Also, assuming this is the case, are you just wanting corresponding cells to be shaded? No information to be pulled over? Just one shaded cell for each 'group' (as you seem to define them) which meets the date and "TS" criteria?

And should these be separate colours (one for each different 'group')? And also colours which match those in the top table?

Wouldn't it be easier to pull across e.g. names into these cells, rather than rely on mapping colours?

Regards

**JRidge** · 10-09-2013, 09:20 AM

Hi XOR LX,

The names are added by the drop downs once we know what courses need to be schooled and how many.

What i am trying to do is:

Only 4 students can be schooled in one group so there would be 2 groups i.e 2 instructors or instructor teaches twice.

Just one shaded cell for each group on the right day and in the right Course coloumn.

Top table has no shading, i did it to high light the groups. Bottom table can be all the same shade, i just need a visual marker where you click on that cell, use drop down and choose instructor.

So say course TS01 had 8 students for the 12 Sept, The formula would work out we need 2 instrctor periods for this course on this date and shade 2 cells in the relevant area in the bottom table. nothing in the upper table is affected. Then i can just click on the shaded boxes and add the instructor (so the Hours and Minutes and Overnights are added (This is working).)

Does this make sense...

Regards

J

**XOR LX** · 10-09-2013, 10:54 AM

Ok,

Again, things quite complex in the Conditional Formatting, so comprehensive checking required.

Cheers

**JRidge** · 10-11-2013, 03:36 AM

Hi XOR LX,

Well my, that is a mighty fine bit of work.

How can i see the conditional formating.
If the Table grows how do i adjust it.

Mighty fine bit of work, I wish i could that....

Thank you very much.

Regards

J

**XOR LX** · 10-11-2013, 03:53 AM

You're welcome.

Home tab→Conditional Formatting→Manage Rules

If you amend the range, you'll need to amend the Applies to: range accordingly.

You'll also need to edit the ranges in the formula itself - the parts in red are those which will require potential revision:

=AND(INDEX($B$83:$B$142,5*(MATCH(ROWS($1:1),1+5*(ROW(INDIRECT("1:"&1+ROWS($C$83:$AP$142)/5))-1))-1)+1)<>"",IFERROR(1/(1/(MOD(ROWS($1:1),5))),5)<=ROUNDUP(SUMPRODUCT(--(INDEX(Date,MATCH(C$82,$A$8:$A$48,0),)=INDEX($B$83:$B$142,5*(MATCH(ROWS($1:1),1+5*(ROW(INDIRECT("1:"&1+ROWS($C$83:$AP$142)/5))-1))-1)+1))/4),0))

Also bear in mind that this is the Conditional Formatting with reference to cell C83 - if you need to make amendments to it, I suggest that you ensure that this is the active cell in the worksheet when you go in and edit the Conditional Formatting - if you're not too sure about CF in general, there are many good websites out there.

And it goes without saying that this formula is dependent on C83:AP142 continuing to be divided into subsections of five rows.

And if you ever get into dissecting that formula, you'll realise what a PITA your 'beloved' merged cells are!

All the best

formula to list dates in a table (Listing duplicate dates only once)

LinkBack

Thread Tools

Rate This Thread

Display

formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Re: formula to list dates in a table (Listing duplicate dates only once)

Thread Information

Users Browsing this Thread

Similar Threads

Check dates in range either same dates or different dates by formula

Counting number of unique dates per month in a list of duplicate dates

[SOLVED] have list of dates, need how many of those dates fall between two other dates, monthly rpt

List of unique and duplicate names that correspond with dates

[SOLVED] Highlight Group of Dates if Dates Match List of other Dates

Bookmarks

Bookmarks

Posting Permissions