how to get rid of duplicate numbers?
you can extract unique numbers, and then sum them
what is this one?
yes, this is the one
Thank You @Uzo
how do i get the data i want?
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
You have to turn that array into rows… and then use sum rollup
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.
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
What type should I use to convert an array into rows?
also a bit complicated
I posted this method somewhere many times… let me find it
I can’t find it anymore… so
- add a row id column
- add a lookup column and point to that row id
- 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