Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 1st 08, 08:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 2
Default Formula: copy/concatenate cell contents matching condition

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


  #2   Report Post  
Old September 1st 08, 09:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Formula: copy/concatenate cell contents matching condition

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

On Sep 1, 8:11*pm, Neil Hutchinson
wrote:
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


  #3   Report Post  
Old September 1st 08, 10:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 2
Default Formula: copy/concatenate cell contents matching condition



"Pete_UK" wrote:

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

On Sep 1, 8:11 pm, Neil Hutchinson
wrote:
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


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
  #4   Report Post  
Old September 2nd 08, 01:48 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Formula: copy/concatenate cell contents matching condition

Neil,

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

Pete

On Sep 1, 10:13*pm, Neil Hutchinson
wrote:
"Pete_UK" wrote:
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


On Sep 1, 8:11 pm, Neil Hutchinson
wrote:
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


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- Hide quoted text -

- Show quoted text -


  #5   Report Post  
Old September 2nd 08, 09:51 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Formula: copy/concatenate cell contents matching condition

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

On Sep 2, 1:48*am, Pete_UK wrote:
Neil,

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

Pete

On Sep 1, 10:13*pm, Neil Hutchinson



wrote:
"Pete_UK" wrote:
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


On Sep 1, 8:11 pm, Neil Hutchinson
wrote:
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


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- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to copy the contents from last cell Lamb Chop Excel Discussion (Misc queries) 2 January 17th 08 01:27 AM
excel - formula to copy the cell contents and colour. Possible? Jason Excel Discussion (Misc queries) 3 August 24th 07 10:05 PM
Copy contents of one cell to another based on matching in 2 other TerryB Excel Discussion (Misc queries) 1 February 22nd 07 08:54 AM
Concatenate cells, replacing blanks with cell contents from other rows [email protected] Excel Worksheet Functions 10 February 21st 07 03:16 PM
matching cell contents GARY Excel Discussion (Misc queries) 14 February 15th 06 10:30 AM


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017