Entry must be replaced form another entry

Hello,

i have a big Table. In this table I have multiple colums.

I have a Colum "Account Number". These Number can have 2 rows or 7 rows or 100 rows.

Then I have a Colum "PRC" In each Account Number is a PRC. The problem is that every "PRC" over 600 must be replaced.

They must replaced by the number under 600 in the Account number.

Example I have a Account Number with 6 rows.

The 6 rows have two different PRC. One is 100 and the other is 610. Then the 610 must be replaced by the same Account Number PRC. In this case the 100.

If I have three or more PRCs then the 600 must by replaced from this PRC, who have the same Amount in in colum "Amount"

Can u help?

Thanks

Accepted answers

vizAlter

@Massi1 -- Here is my take, please review the attached.

I am using "Find Replace" tool which is workable on Strings only, later, converting into the correct data type.

Replace if Higher.yxzp

All comments

vizAlter

@Massi1 — Could you please attach a sample file or dummy records?

Massi1

Hey ,

thanks for the reply.

here is dummy file but thats very simplify presentation of the datas.

I have 35k Records.

BEispiel.xlsx

hroderick-thr

Think it through buddy 🙂

1. Input the data

2. Use a filter to split into PRC >= 600, giving you separate true and false datasets

3. Join the true and false datasets on the fields that must match. Put true on the right and false to the left.

(You may need 2 joins, first matching account and amounts, next with matching accounts only)

4. This gives you 3 datasets Right, Join, and Left

5. On the Join dataset, use a formula tool to recalculate the PRC value that is >= 600

...do what you need with the data after that

Your rules remain ambiguous:

What if you have the 5 rows with the same account and same amount and different prices, all under 600, and a 6th row with same account and amount and price over 600, which of the 5 PRC do you use? Average? Max? Min?
What if your PRC is exactly 600, do you replace it?
What if your PRC is 601 but no other rows have the same account?

vizAlter

@Massi1 -- Here is my take, please review the attached.

I am using "Find Replace" tool which is workable on Strings only, later, converting into the correct data type.

Replace if Higher.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703