Move multiple rows from pivot in columns

[gaurav bhatia]

Exsisting data format Required format
Sr. DOA Serial DOA 1st date DOA 2nd date DOA 3rd date DOA 4th date DOA 5th date
2104 20-06-2020 2104 20-06-2020 05-09-2020
2104 05-09-2020 2117 26-06-2020 14-07-2020 04-06-2020
2117 26-06-2020
2117 14-07-2020
2117 04-06-2020

[JeteMc]

Hello gaurav bhatia and Welcome to Excel Forum.
The formula for the Serial column could be: =IFERROR(IF(MATCH(0,INDEX(COUNTIF(L$2:L2,A$3:A$10),,),)>COUNTA(A$3:A$10),"",INDEX(A$3:A$10,MATCH(0,INDEX(COUNTIF(L$2:L2,A$3:A$10),,),))),"")
The formula for the DOA columns could be: =IF($L3="","",IFERROR(INDEX($B$3:$B$10,AGGREGATE(15,6,(ROW($B$3:$B$10)-ROW($B$2))/($A$3:$A$10=$L3),COLUMNS($A$1:A$1))),""))
Let us know if you have any questions.

[gaurav bhatia]

It's correct, but when I paste the data in required columns blank cells are coming.

[JeteMc]

The formula ranges will need to be changed as there are 7680 rows of data instead of the 10 rows in the original file.
The formula for cells L3 and down* should be:

Please Login or Register  to view this content.

The formula for cells M3 and down should be:

Please Login or Register  to view this content.

**I suggest only pulling the formula in column L down until you get a blank cell, which should be in row 4284, and it still may take a LONG time for the calculation to complete. Once the formula in cell M3 is activated (just press Enter) then you may double click the fill handle.
Let us know if you have any questions.

[gaurav bhatia]

Thanks a lot!!!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[Olly]

Or you could use Power Query.

Format your source data as a table, then use:

Please Login or Register  to view this content.

Should be much quicker.