Displaying multiple duplicates from a list into one cell.

**caj5888** · 11-28-2013, 10:05 PM

Hello,

I am making a rota for work. I have a list of names and all the shifts that need covering everyday. I have drop down boxes on each shift that lists each job to cover. You can select a job and it pulls the persons name and put it into the rota at the top. I'm populating the main rota with this formula:

=IF(ISNA(INDEX($B$25:$B$35,MATCH("JOB 1",E$25:E$35,0))),"",INDEX($B$25:$B$35,MATCH("JOB 1",E$25:E$35,0)))

The spread sheet looks like this.

Sun N Sun D Sun N
JOB 1
JOB 2
JOB 3

MAN 1 JOB 1
MAN 2 JOB 2
MAN 3 JOB 1
MAN 4 JOB 3

The trouble is, the formula only finds one instance of the job. I want it to recognise if 2, 3, 4 people are running the same job and put them in a list, one under the other in the corresponding coverage box.

Thanks in advance.

**caj5888** · 11-28-2013, 10:51 PM

EXCEL HELP.png

This image hopefully explains the problem better. The formula in the cell shown only seems to find one instance of "JOB 1" and places "MAN 1" into the cell. I would like it to place all 3 men that have "JOB 1" seleced. So the cell would show:

MAN 1
MAN 6
MAN 7

Thanks in advance for your help.

**caj5888** · 11-29-2013, 02:57 PM

Bumping thread.

**caj5888** · 12-02-2013, 02:50 AM

Anybody able to shed some light, still cant get this to work.

**Max, Singapore** · 12-02-2013, 04:21 AM

Perhaps one formulae approach to automate your core intents ....

Assume this data is in B2:C5
MAN 1 JOB 1
MAN 2 JOB 2
MAN 3 JOB 1
MAN 4 JOB 3

In an area to the right, create the full matrix of possible Job - Man match-ups
In E2:E5, input all the Man
MAN 1
MAN 2
MAN 3
MAN 4

In F1:H1, input all the Jobs, viz: JOB 1, JOB 2, JOB 3

Then place in F2: =IF(AND($B2=$E2,$C2=F$1),ROW(),"")
Copy across, fill down to H5 to populate the matrix

Now you could place in say, A20: =INDEX($F$1:$H$1,ROWS($1:1))
Copy down to A22 to produce the Job labels aligned with the matrix headers
JOB 1
JOB 2
JOB 3

And place this in B20:
=IF(COLUMNS($A:A)>COUNT(OFFSET($E:$E,,ROWS($1:1))),"",INDEX($E:$E,SMALL(OFFSET($E:$E,,ROWS($1:1)),COLUMNS($A:A))))
Copy B20 across to E20 to cover the max extent of Man assignments per Job (= 4 in this example), fill down to E22. This will return all the Man assignments per Job, all neatly packed to the left. Results will be dynamic to data changes for Man - Job assignments in A2:C5 (try this out)
----------------------------------------
Any good? Wave it, hit the little star at the bottom left of my responses

Displaying multiple duplicates from a list into one cell.

LinkBack

Thread Tools

Rate This Thread

Display

Displaying multiple duplicates from a list into one cell.

Re: Displaying multiple duplicates from a list into one cell.

Re: Displaying multiple duplicates from a list into one cell.

Re: Displaying multiple duplicates from a list into one cell.

Re: Displaying multiple duplicates from a list into one cell.

Thread Information

Users Browsing this Thread

Similar Threads

Multiple rows are duplicates EXCEPT only ONE cell. Make that ONE cell a list

[SOLVED] Filter - Hiding duplicates but displaying 1 of the duplicates item

[SOLVED] Hiding duplicates but displaying 1 of the duplicates item

List displaying data in multiple cells based on list selection

Displaying Multiple Values froma List Box

Bookmarks

Bookmarks

Posting Permissions