excel formula

[FrancescoCavazza]

Hello, 

I would need your help to find the right formula to use to analyse a set of data as follows:

first excel file is made of codes (column A) and for each code I have cities (column B):

001 - ROME 

001 - MILAN

002 - ROME 

003 - ROME 

003 - MILAN 

in the second excel file I have the unique list of codes (column A) and I need to differentiate in two different columns when I have ROME=YES (column B) and when I have MILAN=YES(column C):  

column A     column B (ROME)    column C (MILAN)

001                        YES                               YES

002                        YES                               NO

003                        YES                               YES

I cannot use a vlookup, what kind of formula I can use? 

Thanks a lot 

[#2]

@FrancescoCavazza Perhaps a pivot table with some custom formatting will work for you. See an example in the attached file.

[#3]

Thanks a lot for the help, but actually I would need a formula as it's a quite complex file for which the less pivots I create the better it is, with the formula would be faster let's put it this way, if there is a possible solution. Thanks again

[#4 · Accepted solution]

@FrancescoCavazza 

It could be

[#5]

F-A-N-T-A-S-T-I-C thanks a lot for the amazing help

[#6]

@FrancescoCavazza , glad it helped