Duplikat

Ask for Help
1

image
Hi Guys
how to get rid of duplicate numbers?

2

image
image360Ă—558 17.6 KB

Wish there was a way to calculate with a

Sum Unique

3

you can extract unique numbers, and then sum them

4

what is this one?
image

5

yes, this is the one

6

Thank You @Uzo
how do i get the data i want?
image

7

It is a little complicated…
First, you have to join all the numbers, then use that unique array column, then split these unique numbers, then sum that split

8

image
image795Ă—148 16.1 KB

I’ve arrived here

image
image382Ă—576 20.9 KB

there is no option for summation

9

You have to turn that array into rows… and then use sum rollup

10

Whatever column you are using for Rel toko, you can use that same column to create a single relation back to itself in the same table. Then create a Lookup column that returns the Row ID from the relation. Finally create an IF column that checks if the Row ID for that row matches the Lookup Row ID. If it’s a match, then return the Lookup Ongkir value, else return blank or 0. If done correctly, this will give you a number only once, the first time it appears. The second or third time it appears, it will be blank or zero. Then you can sum that final IF column.

This works because a single relation will only find the first match. Both instances of Dapur Bu Yanti will have a single relation that only matches the first instance of Dapur Bu Yanti. So the Lookup RowID will only contain the RowID of the first match. As a result, the IF column will only match on the first instance of Dapur Bu Yanti when comparing the row’s RowID to the Lookup RowID.

I would definitely not use the Unique Elements method in this case. It will cause problems in the future if you have two different unrelated rows that happen to have the same number. All of a sudden you would eliminate too many numbers and your sum would be wrong. It may not happen often, but it definitely could happen.

2 Likes
11

He is looking for unique numbers… so what do you mean that it will create a problem? If the other row has a same number, it should not be counted

12

What type should I use to convert an array into rows?

13

also a bit complicated :wink:
I posted this method somewhere many times… let me find it

14

I can’t find it anymore… so

  1. add a row id column
  2. add a lookup column and point to that row id
  3. add an array index column and find the index of row id in the lookup column
    that will give you row numbers starting from zero
    then add a single value column and extract values from your unique numbers, use row numbers as a position of the single value