Remove columns with more than 1 value

Question

Hello

I have a huge list where i want to remove all of the values that appears more than once. i have tried with the unique tool but i cant remove all the values that appear more than once.

So i want to save all the Z values that only appear once in every x value

testnumbers.xlsx

hroderick-thr · Answer

Add a record id column with a record id tool

Uses summarize tool group by column z, count column z, max record id

Use filter tool for count column z = 1

Use join tool to join max record id with column z count = 1 to original table

Use select tool to remove extra columns like record id

Ben_H · Answer

Hi @mrbubi001,

I've attached a sample workflow. I think I understood what you were asking for.

I concatenated Column X and Z together, then summarised the data by grouping on the concatenated field and counting the distinct Z values.

Filter this to distinct counts of one and join back to the main dataset.

It gets different results from you excel screenshots but that seems to be because you've used a subset for your demo.

Regards,

Ben

Sample Workflow.yxzp

mrbubi001 · Answer

Hi JosephSerpis

The problem with the solution you have found is it removes the Z values that have another X value.