excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2791608%22%20slang%3D%22en-US%22%3Eexcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791608%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20need%20your%20help%20to%20find%20the%20right%20formula%20to%20use%20to%20analyse%20a%20set%20of%20data%20as%20follows%3A%3C%2FP%3E%3CP%3Efirst%20excel%20file%20is%20made%20of%20codes%20(column%20A)%20and%20for%20each%20code%20I%20have%20cities%20(column%20B)%3A%3C%2FP%3E%3CP%3E001%20-%20ROME%26nbsp%3B%3C%2FP%3E%3CP%3E001%20-%20MILAN%3C%2FP%3E%3CP%3E002%20-%20ROME%26nbsp%3B%3C%2FP%3E%3CP%3E003%20-%20ROME%26nbsp%3B%3C%2FP%3E%3CP%3E003%20-%20MILAN%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20the%20second%20excel%20file%20I%20have%20the%20unique%20list%20of%20codes%20(column%20A)%20and%20I%20need%20to%20differentiate%20in%20two%20different%20columns%20when%20I%20have%20ROME%3DYES%20(column%20B)%20and%20when%20I%20have%20MILAN%3DYES(column%20C)%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Ecolumn%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bcolumn%20B%20(ROME)%26nbsp%3B%20%26nbsp%3B%20column%20C%20(MILAN)%3C%2FP%3E%3CP%3E001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20YES%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BYES%3C%2FP%3E%3CP%3E002%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20YES%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BNO%3C%2FP%3E%3CP%3E003%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20YES%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BYES%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20use%20a%20vlookup%2C%20what%20kind%20of%20formula%20I%20can%20use%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2791608%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2791803%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1168856%22%20target%3D%22_blank%22%3E%40FrancescoCavazza%3C%2FA%3E%26nbsp%3BPerhaps%20a%20pivot%20table%20with%20some%20custom%20formatting%20will%20work%20for%20you.%20See%20an%20example%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2791810%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791810%22%20slang%3D%22en-US%22%3EThanks%20a%20lot%20for%20the%20help%2C%20but%20actually%20I%20would%20need%20a%20formula%20as%20it's%20a%20quite%20complex%20file%20for%20which%20the%20less%20pivots%20I%20create%20the%20better%20it%20is%2C%20with%20the%20formula%20would%20be%20faster%20let's%20put%20it%20this%20way%2C%20if%20there%20is%20a%20possible%20solution.%20Thanks%20again%3C%2FLINGO-BODY%3E
New Contributor

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 

 

5 Replies

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

 

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

@FrancescoCavazza 

It could be

image.png

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

@FrancescoCavazza , glad it helped