nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Designer Cloud

Accepted answers

Qiu

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

0905-TB_Mylene.yxmd

flying008

Hi, @TB_Mylene

A other way to filter the duplicate columns.

录制_2022_09_26_08_11_43_148.gif

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. 😁

All comments

Scott_Snowman

One option:

Use a Transpose tool with Store Number as the key column
Use a Unique tool and de-dupe such that you have unique Store Number / Value columns.
Use a Filter tool and remove any records with [Value] empty.
Use a Formula tool and add a New Field, [Count] equal to 1 for every record.
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.
Form a new field, [Sequence] as the string "Sequence " concatenated with the Running Count total (i.e. "Sequence 1", "Sequence 2" etc.)
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

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

0905-TB_Mylene.yxmd

flying008

Hi, @TB_Mylene

A other way to filter the duplicate columns.

录制_2022_09_26_08_11_43_148.gif

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.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15905

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703