Choose items from a list and add to a string in a cell

[Ben]

Hello

I have Excel Spreadsheet, with the following columns containing a list of
our office with an office code. ':' is the column seperator here

Code:Office:Selected?
LEI:Leicester:Y
NOT:Nottingham:N
SHF:Sheffield:N
DONoncaster:Y
MAN:Manchester:N
BHM:Birmingham:Y

On a seperate sheet, I have a box that I want to populate with a list of
office codes, seperated by a comma, which has a 'Y' in the selected column.

So, in the example above, this box on the seperate sheet would show as

Offices Selected: LEI, DON, BHM

I'm thinking a CONCATENATE string, but can't work out how to do a look up
the column and identify the code in column A if column C=y...


Can anyone help please?

Thanks

Ben

 

[JLatham]

Ben,
Any Lookup function you use is going to stop looking at the first match it
finds, so if you just look in column C for a 'Y', it will always find the
first one, and none after that. You're going to have to examine all rows for
very specific results. Assuming your data is all that there is, and that the
sheet the list is on is named Sheet1 and the data is in cells A2:C7 then this
formula on another sheet would give you what you want. It performs a
specific VLOOKUP() for each opssible Code on Sheet1 and if the Selected?
entry is Y, then it just forces that code to appear and it concatenates all
of the results.

Note that if any of the codes (SHF, MAN, etc. are not in the list, that
causes an #N/A error within the formula, which causes the entire formula to
return #N/A. Here's the basic formula:

=IF(VLOOKUP("LEI",Sheet1!$A$2:$C$7,3,FALSE)="Y","LEI ","") &
IF(VLOOKUP("NOT",Sheet1!$A$2:$C$7,3,FALSE)="Y","NOT ","") &
IF(VLOOKUP("SHF",Sheet1!$A$2:$C$7,3,FALSE)="Y","SHF ","") &
IF(VLOOKUP("DON",Sheet1!$A$2:$C$7,3,FALSE)="Y","DON ","") &
IF(VLOOKUP("MAN",Sheet1!$A$2:$C$7,3,FALSE)="Y","MAN","") &
IF(VLOOKUP("BHM",Sheet1!$A$2:$C$7,3,FALSE)="Y","BHM ","")

 

[Pete_UK]

This will work for your sample data:

=IF(Sheet1!C2="Y",Sheet1!A2&", ","")&IF(Sheet1!C3="Y",Sheet1!A3&",
","")&IF(Sheet1!C4="Y",Sheet1!A4&", ","")&IF(Sheet1!C5="Y",Sheet1!
A5&", ","")&IF(Sheet1!C6="Y",Sheet1!A6&", ","")&IF(Sheet1!
C7="Y",Sheet1!A7&", ","")

assuming it is on Sheet1 and occupies A1:C7.

Hope this helps.

Pete