Formula: copy/concatenate cell contents matching condition

[Neil Hutchinson]

I have been doing this manually but I'm sure there must be a way of doing it
automatically:
on a sheet with cells containing a school form reference (eg '7A') with an
abbreviation for 1) subject 2) teacher and 3) room (all three in separate
columns!)

I would like to be able to (automatically!) look up and read the cells that
contain say '7A' and concatenate the three strings into one cell.

Later I can remove the extra occurrences of the form ID (eg '7A')

I hope this is clear enough
Thank you

 

[Pete_UK]

I don't quite understand - could you give some more details of what
data you have (sheetnames, columns, rows etc) and what you want to
achieve with it. I'm sure it can be done, it's just that I can't
picture what you have.

Pete

 

[Neil Hutchinson]

I don't quite understand - could you give some more details of what
data you have (sheetnames, columns, rows etc) and what you want to
achieve with it. I'm sure it can be done, it's just that I can't
picture what you have.

Pete

Each row represents a lesson (time), say 9am
In each row is every form class with their subject (this in a single cell
per class: '7A FR' menas that 7A have French; (ditto 7B to K, 8A to K etc.)
further to the right we have a similar arrangement with '7A JB' to indicate
that 7A have Joe Bloggs (therefore Joe Bloggs is 7A's French teacher!);
further right again, we have '7A G14' which indicates that the lesson takes
place in Room G14. What I want to do is 'pick up' the three pieces of data
and put them together in one cell, which can be later stripped out and
separately saved/printed

I hope that makes it clear
(Have done it manually for now but would like to know for future reference
Cheers
Neil

 

[Pete_UK]

Neil,

it's getting late here now, so I'll get back to you tomorrow.

Pete

 

[Pete_UK]

Neil,

could you tell me which columns you use for the subject, the teacher
and for the room? If you have 6 classes per year group, say, and five
year groups, then I imagine you have 30 columns for each of the above
- do these start at column B and run on continuously, or do you have
gaps? How many rows of data do you have?

Is the concatenated information going into a different sheet? If so
what sheetnames do you use?

Again, more details please.

Pete