Change specific values in column based on value in another column

[dpakapd]

Hello,

I have a table similar to the sample shown below. It is a larger table but the representation below captures the information layout in it:

Value                   Group_Num

Resistor	10
Ammeter	20
Inductor	30
Resistor	30
Capacitor	40
Resistor	20
Resistor	10
Inductor	30

Resistor should have Group_Num of 10 but there are instances where the value is set as 20 or 30. How can I clean my data such that I can replace "Resistor-20" and 'Resistor-30' with "Resistor-10"? Since it is a large dataset, the resistor in column A (or any value actually) could have multiple values in column B. But the correct combination is:

Resistor-10

Ammeter-20

Inductor-30

Capacitor-40.

How can I find the incorrect combinations and correct the values in Column Group-Num?

I tried the 'Filter' tool to extract all 'Resistor-10' combinations. For the values that didn't get filtered out (Filter 'F') how can I compare the Filtered vs Unfiltered parts to see if 'Resistor-20' or 'Resistor-30' combinations exist and suitably replace the '20' and '30' with '10'?

Thanks

[Luke_C]

Hi @dpakapd 

Try this in a formula tool for Group_Num.

IF [Value] = 'Resistor'

Then '10' <You may not need the quotes depending on data type

else [Group_Num]

Endif

To find them in a filter tool, you could do a custom filter:

[Value] = 'Resistor' AND [Group_Num] != '10' <again, quotes may not be needed depending on data type

[dpakapd]

Thanks Luke!

However in the sample dataset, I showed I know the value as 'Resistor'. But in the large dataset, there are different part numbers in column A and I don't know each part number. How can I tackle such a situation?

I tried the Cross Tab tool and I was able to group by column B and see where I am getting duplicates, i.e. for which rows I am getting duplicates. But is there a simpler way to assign correct group number based on the value in column A (esp when there are 3000 different possibilities for column A)?

Thanks.

[Luke_C · Accepted solution]

Hi @dpakapd 

The best approach would be to use the find and replace tool. Keep a master list of the values & acceptable numbers, and just use the find and replace to substitute. The tool is fairly straightforward but if you give some more sample data someone can probably mock up a quick example.

[dpakapd]

Thanks Luke!