Populating cell value with matching criteria into a result column

%3CLINGO-SUB%20id%3D%22lingo-sub-3074723%22%20slang%3D%22en-US%22%3EPopulating%20cell%20value%20with%20matching%20criteria%20into%20a%20result%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074723%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20occurrences%20of%20the%20same%20person%20but%20with%20a%20different%20value%20in%20a%20column(condition%20column%20in%20example%20below)%20and%20I'd%20like%20to%20show%20all%20the%20results%20for%20the%20individual%20people%20on%20one%20row%20with%20the%20different%20values%20in%20cells%20within%20the%20same%20row.%3C%2FP%3E%3CP%3EThe%20first%20three%20columns%20is%20the%20data%20I%20have%20to%20work%20with%20and%20I%20want%20to%20show%20the%20conditions%20that%20the%20same%20person%20has%20across%20from%20their%20name%20in%20one%20row%20but%20in%20different%20columns.%3C%2FP%3E%3CTABLE%20width%3D%22608%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3ECondition%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3EDOB%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3EAsthma%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJoe%20Bloggs%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E01%2F01%2F1981%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EAsthma%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJoe%20Bloggs%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E01%2F01%2F1981%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3EAsthma%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJoe%20Bloggs%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E01%2F01%2F1981%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJoe%20Bloggs%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E01%2F01%2F1981%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJoe%20Bloggs%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E01%2F01%2F1981%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJohn%20Smith%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E19%2F12%2F1965%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EJohn%20Smith%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E19%2F12%2F1965%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EMary%20James%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E11%2F07%2F1978%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EMary%20James%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E11%2F07%2F1978%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EMary%20James%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E11%2F07%2F1978%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3ECOPD%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EPolly%20Kettle%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E14%2F02%2F1976%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EIHD%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EPolly%20Kettle%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E14%2F02%2F1976%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3EPolly%20Kettle%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E14%2F02%2F1976%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3EA%20Fib%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EAsthma%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3ELaine%20Street%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E29%2F11%2F1970%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3EAsthma%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.3594px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2267.7812px%22%3ELaine%20Street%3C%2FTD%3E%3CTD%20width%3D%2292.9844px%22%3E29%2F11%2F1970%3C%2FTD%3E%3CTD%20width%3D%2272.4688px%22%3EDiabetes%3C%2FTD%3E%3CTD%20width%3D%2264.9844px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2249.75px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2256.8906px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272.0312px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074723%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3077609%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20cell%20value%20with%20matching%20criteria%20into%20a%20result%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3077609%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1139231%22%20target%3D%22_blank%22%3E%40aod2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20you%20run%20a%20recent%20version%20of%20Excel%20on%20Windows%2C%20with%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fgetting-started-with-get-transform-in-excel-a8310388-2a12-438c-9d29-c6d29cb8df6a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EGet%20%26amp%3B%20Transform%3C%2FA%3E%20aka%20Power%20Query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot.png%22%20style%3D%22width%3A%20983px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343281iC6D5957CDE241C06%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot.png%22%20alt%3D%22Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3078097%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20cell%20value%20with%20matching%20criteria%20into%20a%20result%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3078097%22%20slang%3D%22en-US%22%3EThanks%20a%20million%20Liz%2C%20I%20was%20lost%20in%20never%20ending%20formula%20and%20data%20sorting.%20Your%20solution%20looks%20like%20just%20what%20I%20need.%20I%20will%20try%20that%20out.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have occurrences of the same person but with a different value in a column(condition column in example below) and I'd like to show all the results for the individual people on one row with the different values in cells within the same row.

The first three columns is the data I have to work with and I want to show the conditions that the same person has across from their name in one row but in different columns.

ConditionNameDOBDiabetesAsthmaIHDA FibCOPD 
DiabetesJoe Bloggs01/01/1981Diabetes     
AsthmaJoe Bloggs01/01/1981 Asthma    
IHDJoe Bloggs01/01/1981  IHD   
A FibJoe Bloggs01/01/1981   A Fib  
COPDJoe Bloggs01/01/1981    COPD 
DiabetesJohn Smith19/12/1965Diabetes     
COPDJohn Smith19/12/1965    COPD 
IHDMary James11/07/1978  IHD   
A FibMary James11/07/1978   A Fib  
COPDMary James11/07/1978    COPD 
IHDPolly Kettle14/02/1976  IHD   
DiabetesPolly Kettle14/02/1976Diabetes     
A FibPolly Kettle14/02/1976   A Fib  
AsthmaLaine Street29/11/1970 Asthma    
DiabetesLaine Street29/11/1970Diabetes     
3 Replies

Hi @aod2020 

 

Assuming you run a recent version of Excel on Windows, with Get & Transform aka Power Query

 

Screenshot.png

 

Sample attached

Thanks a million Liz, I was lost in never ending formula and data sorting. Your solution looks like just what I need. I will try that out.
Welcome @aod2020. Just be aware this is a mock-up that will fail if you have duplicates, i.e. John Smith listed 2, 3... times with the same Conditions