I am currently working on some XML data and when I use the ParseXML macro, I kind of get the result similar to the below "Input" table.
Next, I am using a Multi-Row Formula tool and generating a new column called Rank with the below formula
IF [Node] = "Part" then [Row-1:Rank] + 1
ELSE [Row-1:Rank] ENDIF
From the data, we can see Name and Code repeat multiple times in different rows. Now, I want to change the value of Code if it repeats more than once and it has the same rank.
For example: Code - Rank 1 - > Code - Rank 1
Code - Rank 1 - > Code1 - Rank 1
Code - Rank 1 - > Code2 - Rank 1
I would like to achieve the same with Name values.
After processing the data as required, my objective is to transpose the data with Node being the column Names and Value being the data.
Expected Input
| Node | Value | Rank |
| Part | 101ABC | 1 |
| Module | | 1 |
| Code | ZMA | 1 |
| Description | WHEEL | 1 |
| Version | | 1 |
| Code | WHL | 1 |
| Name | LUGNUTS | 1 |
| Engine | | 1 |
| Code | 1ZA2F | 1 |
| Name | LUGNUTS | 1 |
| Part | 111ABC | 2 |
| Module | | 2 |
| Code | XMA | 2 |
| Description | BATTERY | 2 |
| Version | | 2 |
| Code | BAR | 2 |
| Name | HYBRID TECHNOLOGY | 2 |
| Engine | | 2 |
| Code | 2AS3F | 2 |
| Name | HYBRID TECHNOLOGY | 2 |
| Part | 211ABC | 3 |
| Module | | 3 |
| Code | RAC | 3 |
| Description | Bumper | 3 |
| Version | | 3 |
| Code | BUMP | 3 |
| Name | Front Bumper | 3 |
| Engine | | 3 |
| Code | 3SF5F | 3 |
| Name | Front Bumper | 3 |
Expected Data after Processing
| Node | Value | Rank |
| Part | 101ABC | 1 |
| Module | | 1 |
| Code | ZMA | 1 |
| Description | WHEEL | 1 |
| Version | | 1 |
| Code1 | WHL | 1 |
| Name | LUGNUTS | 1 |
| Engine | | 1 |
| Code2 | 1ZA2F | 1 |
| Name1 | LUGNUTS | 1 |
| Part | 111ABC | 2 |
| Module | | 2 |
| Code | XMA | 2 |
| Description | BATTERY | 2 |
| Version | | 2 |
| Code1 | BAR | 2 |
| Name | HYBRID TECHNOLOGY | 2 |
| Engine | | 2 |
| Code2 | 2AS3F | 2 |
| Name1 | HYBRID TECHNOLOGY | 2 |
| Part | 211ABC | 3 |
| Module | | 3 |
| Code | RAC | 3 |
| Description | Bumper | 3 |
| Version | | 3 |
| Code1 | BUMP | 3 |
| Name | Front Bumper | 3 |
| Engine | | 3 |
| Code2 | 3SF5F | 3 |
| Name1 | Front Bumper | 3 |
