Forum Discussion

aszpic's avatar
aszpic
Frequent Visitor
9 years ago
Solved

Help on making a new column with a condition

Hello everyone,

 

I couldn't find a way to do this:

I have a table with 2 columns. The first column are some codes, which CAN be repeated. The 2nd column is a binary unmber (0/1).

 

Example:

CODES   NUMBER

codeA   1

codeA   1

codeA   0

codeA   0

codeB   1

codeC   0

codeC   0

 

I need to make a 3rd column, with this logic: "If (at least one of the NUMBERs of a code is 1, then1, else 0)". So in the 3rd column, all the registers of codeA would be 1 because there are 2 of the NUMBERs of codeA that is 1. So the result would be:

 

CODES   NUMBER  3rdColumn

codeA   1   1

codeA   1   1

codeA   0   1

codeA   0   1

codeB   1   1

codeC   0   0

codeC   0   0

 

Thanks in advance!

Need Help
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi aszpic,

     

    Firstly, I will set Number column to text to prevent it sum/count that column in table/matrix

    Create Calculated column:

    3rd = IF(CALCULATE(COUNT(Sheet1[Number]),filter(all(Sheet1),Sheet1[Number]="1" && Sheet1[CODES ]=EARLIER(Sheet1[CODES ]) ))>0,"1","0")

    Earlier method to get current row, All method to select and compare in all rows in table

2 Replies

  • tringuyenminh92's avatar
    tringuyenminh92
    Icon for Memorable Member rankMemorable Member
    9 years ago

    Hi aszpic,

     

    Firstly, I will set Number column to text to prevent it sum/count that column in table/matrix

    Create Calculated column:

    3rd = IF(CALCULATE(COUNT(Sheet1[Number]),filter(all(Sheet1),Sheet1[Number]="1" && Sheet1[CODES ]=EARLIER(Sheet1[CODES ]) ))>0,"1","0")

    Earlier method to get current row, All method to select and compare in all rows in table

    • aszpic's avatar
      aszpic
      Frequent Visitor
      9 years ago

      thanks a lot! It threw me an error because it can't compare numbers with text values, but with a little variation it worked perfectly.