Making list of names under their matching Day of the week - help!

[aftbrah]

Hi,

this may seem simple but its only because I'm new so bear with me!

I have a list that looks sort of like this:

Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..


This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:

Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...


I tried using the vlookup function but then it confused me when i also needed
to match the names with their respective service days. I tried looking through the youtube channel but just couldn't quite find what I was looking for. any suggestions?

 

[aftbrah]

oh and im using Excel 2010 on windows 7

 

[T. Valko]

oh and im using Excel 2010 on windows 7

Let's assume your data is in the range A2:D8.

A10:G10 = Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

Enter this array formula** in A11:

=IFERROR(INDEX($A$2:$A$8&", "&$B$2:$B$8,SMALL(IF($D$2:$D$8=A$10,ROW(A$2:A$8)),ROWS(A$11:A11))-ROW(A$2)+1),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to G11 then down until you get a row full of blanks.

 

[aftbrah]

Let's assume your data is in the range A2:D8.

A10:G10 = Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

Enter this array formula** in A11:

=IFERROR(INDEX($A$2:$A$8&", "&$B$2:$B$8,SMALL(IF($D$2:$D$8=A$10,ROW(A$2:A$8)),ROWS(A$11:A11))-ROW(A$2)+1),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to G11 then down until you get a row full of blanks.

OK I did just as you said but replaced the ranges with the ones I have:

A36:F36 (Monday to Saturday only)

and A2:F26 (range of data)

I copied it across to F36 and then down and as you said they're all blanks. so now I have blank cells under the service days columns.. what next?

 

[scottylad2]

try this

Excel Workbook
	B	C	D	E
13	Last name	First name	date	Service Day
14	smith	john	01/01/2011	Monday
15	gray	allan	01/02/2011	Monday
16	malford	sam	01/03/2011	Tuesday
17	calder	jim	01/04/2011	Wednesday
18	burrows	sheila	01/05/2011	Tuesday
19	mack	isa	01/06/2011	Wednesday
20	smithieson	debbie	01/05/2011	Wednesday
Sheet2

[/B]

Excel Workbook
	B	C	D
24	Monday	Tuesday	Wednesday
25	smith john	malford sam	calder jim
26	gray allan	burrows sheila	mack isa
27			smithieson debbie
28
29
30
Sheet

 

[T. Valko]

OK I did just as you said but replaced the ranges with the ones I have:

A36:F36 (Monday to Saturday only)

and A2:F26 (range of data)

I copied it across to F36 and then down and as you said they're all blanks. so now I have blank cells under the service days columns.. what next?

Here's the same formula and method using defined named ranges.

• Name: Last
• Refers to: =Sheet1!$A$2:$A$26
• Name: First
• Refers to: Sheet1!$B$2:$B$26
• Name: Day
• Refers to: =Sheet1!$D$2:$D$26

Then, this array formula** entered in A37:

=IFERROR(INDEX(Last&", "&First,SMALL(IF(Day=A$36,ROW(Last)),ROWS(A$37:A37))-MIN(ROW(Last))+1),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F37 then down until you get a row full of blanks.

Here's a small sample file that demonstrates this.

zzzAftbrah.xlsx 11kb

http://cjoint.com/?AIzvleuCk5q

 

[aftbrah]

Thanks so much guys you've all been a great help! Definitely recommending this site!

Cheers

 

[T. Valko]

Thanks so much guys you've all been a great help! Definitely recommending this site!

Cheers

You're welcome. Thanks for the feedback!