Remove duplicate values within a row

[TB_Mylene]

Hi, I have thousands of records that I need the capability to remove duplicate values in within a row. I need to identify the different type of designs a building has had from newest to oldest throughout the life of the building. 

Here's an example of the input.

Here's how I would like the output to be.

Any advise is appreciated. 

Thanks in advance.

[Scott_Snowman]

One option:

1. Use  a Transpose tool with Store Number as the key column
2. Use a Unique tool and de-dupe such that you have unique Store Number / Value columns.
3. Use a Filter tool and remove any records with [Value] empty.
4. Use a Formula tool and add a New Field, [Count] equal to 1 for every record.
5. Use a Running Total tool grouping by Store Number and summing the [Count] Field so you end up with an incrementing number starting at 1 that increases within a Store Number, and resets to 1 when you hit a new Store Number.
6. Form a new field, [Sequence] as the string "Sequence " concatenated with the Running Count total (i.e. "Sequence 1", "Sequence 2" etc.)
7. Finally use a Cross Tab tool, grouped on Store Number, with the [Sequence] field as your column headers and the [Value] field as your column values.

[Qiu · Accepted solution]

@TB_Mylene 
End up a length one since have to consider the columns names.

[flying008 · Accepted solution]

Hi, @TB_Mylene 

A other way to filter the duplicate columns.

The Multi-Row Formula:

iif([Concat_Value]=[Row-1:Concat_Value],Null(),1)

********

If it helped you to solved, maybe you can  accept it as a solution and give a like to answer.  😁

[TB_Mylene]

This solution worked perfect! Exactly what I needed to do. Thank you so much!

[TB_Mylene]

Thank you for this information. 

[TB_Mylene]

This also worked! Thank you for showing me another way.

[Qiu]

@TB_Mylene 
Thank you for your feedback and gald it helps.