<Excel Automation> Comparing 2 column and write final value in another column

Help Studio
, , ,
1

I want to put value in column C/Final color wrt column A/order number and column B/color.
For e.g.

  1. same order number will have same color in final color column.
  2. But final color value in Final color column for respective Order Number depends on preference of color.

Preference of color will be as per below criteria:
a. Value of Final color cell needs to be white only when all of them are white.
b. Value will be Yellow if any of them is Yellow
c. Value will be green only if all of them is green.

image

Would prefer if someone can provide a excel formula for same instead of going via UiPath. Thanks in advance.

Want similar solution like this:

=LET(Matrixvals;CHOOSECOLS(UNIQUE(FILTER($A$2:$B$9;$A$2:$A$9=A2);FALSE;FALSE);2);
IFS(
ISNUMBER(MATCH(“white”;Matrixvals;0));“white”;
ISNUMBER(MATCH(“yellow”;Matrixvals;0));“yellow”;
ISNUMBER(MATCH(“green”;Matrixvals;0));“green”;
TRUE;“n/a”
))

2

Hi @anjasing

Could you try the below excel formula and let me know it’s working or not,

=IF(COUNTIFS(A:A, A2, B:B, "White")=COUNTIFS(A:A, A2), "White", IF(COUNTIFS(A:A, A2, B:B, "Yellow")>0, "Yellow", "Green"))

Hope it helps!!

2 Likes
3

Hi @mkankatala ,

Thank you so much for your help,

But, It’s not fulfilling one criteria:
When for e.g. Colour column has 2 white and 1 green for same order number 4, final color should be Yellow.

Sorry it was not mentioned in question asked.

Final color output should be like this:
image

4

Is the above expression working for you excluding the new rule @anjasing

5

Yes, it’s working for me.

6

Try the below one and let me know, @anjasing

=IF(COUNTIFS(A:A, A2, B:B, "White")=COUNTIFS(A:A, A2), "White", 
 IF(COUNTIFS(A:A, A2, B:B, "Yellow")>0, "Yellow", 
 IF(COUNTIFS(A:A, A2, B:B, "Green")=1, "Yellow", "Green")))

Hope it helps!!

7

1st one is only coming yellow, which should be green. Everything else is fine. @mkankatala

8

Provide the output that you have got, share the excel file if possible @anjasing

9

Excel Output.xlsx (9.2 KB)

10

Try this one @anjasing

=IF(COUNTIFS(A:A, A2, B:B, "White")=COUNTIFS(A:A, A2), "White", 
 IF(COUNTIFS(A:A, A2, B:B, "Yellow")>0, "Yellow", 
 IF(COUNTIFS(A:A, A2, B:B, "Green")=COUNTIFS(A:A, A2), "Green", "Yellow")))

Check the below output,
image

Hope you understand!!

1 Like
11

Yes, it works. Thank you so much :slight_smile:

12

It’s my pleasure… @anjasing

Happy Automation!!

1 Like
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.