Forum Discussion

mollyvickers's avatar
mollyvickers
Copper Contributor
Mar 17, 2020
Solved

does excel have a function to move data into one row if same value/unique identifier is the same?

I am not sure how to describe this but I am looking to consolidate data by putting it into the same row if the unique identification is the same in the first column.. I have attached an image to help...
excel
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    Mar 17, 2020

    Hi mollyvickers 

     

    Please see that attached sample file with your desired result, It an array formula construction. When you paste this formula don't forget to press ctrl+shift+enter

    {=IFERROR(INDEX(data[Year],SMALL(IF(($F3=data[Unique ID]),(ROW(data[Unique ID])-2),""),COLUMN(A$1))),"")}

     

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer.

ExcelExciting's avatar
ExcelExciting
MVP
Mar 17, 2020

Hi mollyvickers 

 

Please see that attached sample file with your desired result, It an array formula construction. When you paste this formula don't forget to press ctrl+shift+enter

{=IFERROR(INDEX(data[Year],SMALL(IF(($F3=data[Unique ID]),(ROW(data[Unique ID])-2),""),COLUMN(A$1))),"")}

 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

Resources