Forum Discussion
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 explain. any help greatly appreciated!
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
5 Replies
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
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.
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!
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
