Create a new column using information from separate columns

[cfmartin76]

Hello!

Another question that I posted under Access, I hope this is the right
place to ask this. I have a little bit of a problem, and I wasn't able
to find the solution anywhere.


I have column A "Title" that is linked with column B "Day of the week".
Each title can have 1, 2 or 3 days of the week (which will then be
column C, D, etc). It looks like this:

TITLE DAY1 DAY2 DAY3
Math Mon Tue
History Tue Fri Mon
English Wed Mon

How can I create a new column that lists all the "titles" that appear
on Monday and a separate column for the ones that appear on Tuesday?
(the names of the new columns would be "Monday", "Tuesday", etc) If a
title has more than 2 days I want that title to appear on the columns
for both days.

It will look like this:
MON TUE WED THU FRI
Math Math English History
History History
English


Is there a macro for this?


Thanks!

 

[Bobocat]

Suppose at A1, you type
TITLE DAY1 DAY2 DAY3
Math Mon Tue
History Tue Fri Mon
English Wed Mon

at row 8, type
MON TUE WED THU FRI
at A9 enter the formula like this:

=IF(ISERROR(MATCH(A$8,$B2:$D2,0))," ",INDIRECT(ADDRESS(ROW(A2),1)))
then copy to rest the cells

 

[cfmartin76]

This helps, but it brings one more issue. How can I delete the cells
that are blank so instead of:
Mon Tue Wed Thu Fri
math math
history history history
english english

I'll see this:


Mon Tue Wed Thu Fri
math math english history
history history
english