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.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 17, 2020

mollyvickers Wondering what you are trying to achieve here. Is it always two years that need to be put side-by-side? Seems that you can achieve something very similar with a simple pivot table (standard functionality in Excel). I've attached an example.

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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 17, 2020

    mollyvickers Personally, I would prefer the Pivot Table option. It's easy, extends automatically when you add new years for existing ID's. See attached. But, that's up to you of course.

    Perhaps ExcelExciting can re-write his formulae so that it dynamically accommodates more than two years.

Resources