get all non blank values and fill another table

[vardis]

Hi 2 all,

I have a table in excel 2007 which looks like this:

1.png

John Jim Tom Nick
9:00-10:00 A1
10:00-11:00 B5 B5
11:00-12:00 B3 D2
12:00-13:00 B5 B5 10
13:00-14:00 C4 C4 C5



ClassRooms
A1 B5 B5 B3 D2 B5 B5 C4 C4 C5






The A1:E6 table is a timetable.

I want 2 get all non blank values of that table and fill the G11:k12 table which is the classroom table.

Any Ideas ?

Ty very much


ps: Cant c the image I upload .... sry

[AliGW]

What results do you expect to see in the classrooms table? I don't see how it relates to the timetable.

[vardis]

The timetable holds 10 non blank values.

The data will fit in 5x2 table which are the classrooms.

I wish I could upload the screenshot.

Sry for the late responce

[Richard Buttrey]

Please upload the workbook and manually add the results you expect. Clearly indicate which cells are data and which are results.

[vardis]

How do I upload workbook ?

[Richard Buttrey]

Try the FAQ _reading_posting_attachments

[vardis]

ok I just upload it

[FlameRetired]

Try array-entering this formula in G11 and fill across to column K. Then simply copy that already selected range and paste to G15, G19, G23 and G27.

Formula: copy to clipboard

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

By the way. Your profile says Excel 2007. The upload is an .xls file. If backward compatibility is an issue this formula will not work there. The IFERROR function is not available until 2007.

Edit Had to edit. Posted wrong formula.

[vardis]

I couldn't upload the 2007 file for a reason so I had 2 roll back.

I use 2007 so I ll give it a try I ll answer back in a while.

thanks anyway

[vardis]

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

I have an issue with that : )

[vardis]

Is the formula bar the edit mode ?

I get an error message saying : The formula u typed contains an error

[FlameRetired]

vardis

Ooops. My mistake. I posted formula from the wrong cell. Use this instead in G11.

Please Login or Register  to view this content.

and yes click in the formula bar and the cell will be in edit mode.

[vardis]

I still get an error message

[FlameRetired]

The value in F11 has extra spaces that keep it from matching. It has

9:00 - 10:00 and needs to be
9:00-10:00

[Richard Buttrey]

One thing I'd bear in mind for other projects and which will make your data much more usable and easier to handle is to use proper Date and Time numbers and not a string like say "10:00-11:00".

Use just the upper boundary of the time slot. i.e. just 11:00.

[vardis]

Ty very much both for advise and solution

Now i have to extend the timetable and fit the classes in more classrooms.

I ll try to extend your function to my needs.

Anyway it works perfect if i have prob i ll continue the posts.

Thank you very much anyway.

[vardis]

It works perfect thank u very much.

There is another issue that we should solve, but the function works perfect.

Thank u

[FlameRetired]

You are welcome. Glad it works and thank you for the feedback.

[vardis]

The second issue i have to solve is:

If a teacher has a 2 hours lecture then he should stay in the same class.

We have a function that "kills" the blank cells and transfer the timetable to classrooms. Now we need a function that will keep the lecturer in the same class if he has a 2 hour lecture.

Any Ideas?

[vardis]

Here is the workbook

[FlameRetired]

Do I understand that you want this:

	John	Mairy	Tom	Jim	Ann	Tom	Nick
9:00-10:00	A1	A2	A5		A3	C5
10:00-11:00		A4	A2	B5			B7
11:00-12:00	B3		A7	D1	B3	D2	B3
12:00-13:00		A9		B5	A1	A9	B3
13:00-14:00	C4			B5		C4	C2

To be this:

	John	Mairy	Tom	Jim	Ann	Tom	Nick
9:00-10:00	A1	A2	A5		A3	C5
10:00-11:00		A2	A5	B5			B7
11:00-12:00	B3		A5	B5	A1	D2	B7
12:00-13:00		A9		B5	A1	D2	B7
13:00-14:00	C4			B5		D2	B7

And can you supply a list of all the class room numbers?

[vardis]

Hi there and Good morning ... is morning in Greece 10:25 : )

I upload a new workbook.

What i need is :

If a lecturer have a 2 hour lecture (this is ponted out by the formatted cells)(table 1) then he should continue in the same classroom
(formatted cells classroom tables)

in example : There is a 2 hour lecture:

12:00-13:00 B2
13:00-14:00 B2

In the corresponding classrooms table the B2 department should be in classroom 2 for both hours.

Same for B3 for 11:00 -12:00, 12:00-13:00

I can imagine that will be a combination of If, match and index functions but i cant actually make it work.

[FlameRetired]

Is this what you are wanting?

	I	J	K	L	M	N	O	P	Q	R	S
8		ClassRooms
9
10		1	2	3	4	5	6	7	8	9	10
11	9:00-10:00	A1	A2	A5		A3	C5
12
13
14		1	2	3	4	5	6	7	8	9	10
15	10:00-11:00		A4	A2	B5			B7
16
17
18		1	2	3	4	5	6	7	8	9	10
19	11:00-12:00	B3		A7	D1	B3	D2	B3
20
21
22		1	2	3	4	5	6	7	8	9	10
23	12:00-13:00		A9		B5	A1	A9	B3
24
25
26		1	2	3	4	5	6	7	8	9	10
27	13:00-14:00	C4	C5		B5		C4	C2

[vardis]

With one simple word ..... Y E S : )

Im gratefull

[vardis]

I d appreciate if u upload the workbook : )

[vardis]

Is this the commercial forum ?

[FlameRetired]

No. We 'work' for free on this side of the forum.

There is a Commercial Services section though.

Thanks for asking ... and the work book is attached.

New formula is

Please Login or Register  to view this content.