Find & Replace blocks of cells in worksheet

[dkjonesau]

Hi all,

I have a report from a new rostering software package that generates a roster but it doesn't show the shifts allocated, just the days that team members are working.

The shifts are a standard pattern so I could generate a roster for the work location pinboard that replaces the report output with meaningful values but can't work out how to do that. INDEX & MATCH might work but I need to replace a cell range with another range.

eg.

Shifts
Day	Day	Afternoon	Night
Report output
Bill	A	A	A	B	Off	Off	Off
Jane	Off	A	A	A	B	Off	Off
Sue	Off	Off	A	A	A	B	Off
Wally	Off	Off	Off	A	A	A	B
Desired result after replacing each block of AAAB with cell content from table Shifts
Bill	Day	Day	Afternoon	Night	Off	Off	Off
Jane	Off	Day	Day	Afternoon	Night	Off	Off
Sue	Off	Off	Day	Day	Afternoon	Night	Off
Wally	Off	Off	Off	Day	Day	Afternoon	Night

Any ideas on searching a whole table as a find & replace?

Can't use macros as enterprise has turned macros off due to security issues.

Cheers

Dave

 

[Sufiyan97]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cubist]

Here's one option using formula:

Book1
	A	B	C	D	E	F	G	H
1
2	Bill	A	A	A	B	Off	Off	Off
3	Jane	Off	A	A	A	B	Off	Off
4	Sue	Off	Off	A	A	A	B	Off
5	Wally	Off	Off	Off	A	A	A	B
6
7	Bill	Day	Day	Afternoon	Night	Off	Off	Off
8	Jane	Off	Day	Day	Afternoon	Night	Off	Off
9	Sue	Off	Off	Day	Day	Afternoon	Night	Off
10	Wally	Off	Off	Off	Day	Day	Afternoon	Night
Sheet7

Cell Formulas
Range	Formula
B7:H10	B7	=IF(B2="A",INDEX({"Day","Day","Afternoon"},COUNTIFS($B2:B2,"A")),IF(B2="B","Night","Off"))