Excel Data Cleanup or consolidate

Arindam Deka 0
Viewed 20

So I have a Large excel Data. Current Data Style. I need to Clean Up the data as per here.

For Each Column 1 data point, there are 5 data in column B, I want the data to be formated like this

Points to note: For most of the data in column 1, there should be 5 data . But for some there may be less. So Whenever there is less data, we can skip that cell or mark with something like nil/0. Tried Sorting the data and also tried with excel formula of

  • =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) It basically tranpose each 5 row to a column. But since there are some data having less than 5 Column b Data, Data gets unusable. Looking forward to some solution Either via excel or via some python script. Sample Data Can Be Found here.
data-cleaning transpose excel python
1 Answers

Try the following to identify unique elements on Column 1 (adding a filter condition to exclude empty cells) on cell D2:

=UNIQUE(FILTER(A2:A20, A2:A20<>""))

Then starting on E2 the following formula:

=IF(D2="","", 
  TRANSPOSE(FILTER($B$2:$B$20, ($A$2:$A$20=$D2) * ($A$2:$A$20<>""))))

sample excel file

then expand down the formula in E2. It works regardless of the number of elements on Column 1.

David Leal0
Related