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.

mollyvickers's avatar
mollyvickers
Copper Contributor
Mar 17, 2020

Riny_van_Eekelen 

 

Hi Riny,

 

Thank you very much for responding and for taking the time to provide an example. Unfortunately the years aren't always the following year (e.g. 2008 then 2009) so sometimes the unique ID has a value from 2008 then the next in 2012. So it doesn't matter if the years are not after one another as long as they are chronological ... Here is the example but with years not necessarily 2008,2009,2010 etc so it needs to look like the rows shown on the right. 

Thanks again!

 

ExcelExciting's avatar
ExcelExciting
MVP
Mar 17, 2020

Hi Riny_van_Eekelen 

 

Sure buddy.. just update formula which can hold upto N number of years 😎😉

 

Hi mollyvickers 

 

Please see the attached solution file update, JUST Keep Adding Column name "Year" & "Value", data will automatically get populate.

 

=IFERROR(INDEX(INDIRECT("data["&G$2&"]",TRUE),SMALL(IF(($F3=data[Unique ID]),(ROW(data[Unique ID])-2),""),COUNTIF($G$2:G$2,G$2))),"")

 

 

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