Replace specific value in a column with first-row value

[NanditaPatil]

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
F1	F2	F3	F4	10/15/2024	2024-10-15_2	2024-10-15_3	2024-10-15_4	2024-10-15_5	2024-10-15_6	2024-10-15_7	2024-10-15_8	2024-10-15_9	2024-10-15_10
Categories of NC	Level 1	Level 0	Issue count	5805	5806	5807	5808	5809	5810	5811	5812	5813	5814
Assembly	Piercing system Assembly Error	Piercing system: Belt alignment	5						0			0	0
Assembly	Piercing system Assembly Error	Thumb Screw not secured	1
Assembly	Piercing system Assembly Error	Piercing system Tubing rubbing against RC clip	2	0
Assembly	Piercing system Assembly Error	Piercing system: Tubing routed incorrectly	7	0			0					0	0
Replace all 0s in the column with the value from the first row
Output
F1	F2	F3	F4	10/15/2024	2024-10-15_2	2024-10-15_3	2024-10-15_4	2024-10-15_5	2024-10-15_6	2024-10-15_7	2024-10-15_8	2024-10-15_9	2024-10-15_10
Categories of NC	Level 1	Level 0	Issue count	5805	5806	5807	5808	5809	5810	5811	5812	5813	5814
Assembly	Piercing system Assembly Error	Piercing system: Belt alignment	5						5810			5813	5814
Assembly	Piercing system Assembly Error	Thumb Screw not secured	1
Assembly	Piercing system Assembly Error	Piercing system Tubing rubbing against RC clip	2	5805
Assembly	Piercing system Assembly Error	Piercing system: Tubing routed incorrectly	7	5805			5808					5813	5814

[KGT · Accepted solution]

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 solution]

@NanditaPatil 
A bit different approach.

I tried with Multirow Formula tool.

[OTrieger]

@NanditaPatil 
Another way will be to use a Batch Macro, And with Find and Replace you could solve your issue, You will not need to use Transpose or Cross Tab.