Replace specific value in a column with first-row value

Question

I want to create a condition where if a column (e.g., 10/15/2024) contains 'X', it should be replaced with the value from the first row of that column (e.g., 5805). Essentially, all occurrences of 'X' in the column should be replaced with the column's first-row value

Input                           F1F2F3F410/15/20242024-10-15_22024-10-15_32024-10-15_42024-10-15_52024-10-15_62024-10-15_72024-10-15_82024-10-15_92024-10-15_10Categories of NCLevel 1Level 0Issue count5805580658075808580958105811581258135814AssemblyPiercing system Assembly ErrorPiercing system: Belt alignment5     0  00AssemblyPiercing system Assembly ErrorThumb Screw not secured1          AssemblyPiercing system Assembly ErrorPiercing system Tubing rubbing against RC clip 20         AssemblyPiercing system Assembly ErrorPiercing system: Tubing routed incorrectly70  0    00              Replace all 0s in the column with the value from the first rowOutput                           F1F2F3F410/15/20242024-10-15_22024-10-15_32024-10-15_42024-10-15_52024-10-15_62024-10-15_72024-10-15_82024-10-15_92024-10-15_10Categories of NCLevel 1Level 0Issue count5805580658075808580958105811581258135814AssemblyPiercing system Assembly ErrorPiercing system: Belt alignment5     5810  58135814AssemblyPiercing system Assembly ErrorThumb Screw not secured1          AssemblyPiercing system Assembly ErrorPiercing system Tubing rubbing against RC clip 25805         AssemblyPiercing system Assembly ErrorPiercing system: Tubing routed incorrectly75805  5808    58135814

KGT · Accepted Answer

This will get you there. I would choose this method as I'm sure you need to do something else to the data and this is easily extensible. The renaming may not be needed in 2024.2. I copied the data from the post, so might be worse than the actual data, but there are other things I would clean up like spaces/empty cells etc.

* Split the data so that "Categories of NC" is separate.
* Transpose the data twice in parallel.
* Join the default value
* Do your replace
* Cross-tab back with whatever is needed for renaming.

Qiu · Accepted Answer

@NanditaPatil 
A bit different approach.

I tried with Multirow Formula tool.