[Solved] combine formula (IF AND & data validation)

[bigme]

dear friends,
i have a task that i can't solve it until now.


what i need is, in column C will be fill with Data 3 from column G, with rules if the data in Data 1 and 2 not duplicate it's use IF+AND function (for data A001 and A002) but for data A003 it will use list data validation so i can choose the data, is it possible to do? thank you.

regards,
bigMe

 

[MusihaMukta]

Hello Bigme,
First of all, I have added two more rows to your data to make it more understandable. Here, you need to use two helper columns.

• In the 1st one (I column) use the following formula to join strings. This is for finding the duplicates.

=CONCATENATE(E2,F2)

• Drag the Fill Handle icon to copy the formula for rest of cells.

• Afterward, you must find out the number of maximum repeated values. To do so, in an unused cell (A11) >> write the formula.

=UNIQUE(I2:I8)

• Then in B11 cell >> use this formula.

=COUNTIF(I2:I8,A11)

• Drag the Fill Handle icon to copy the formula for the rest of the cells. And find out the highest number. Here, the highest number is 4. So, we need to keep blank cells up to 4 adjacent rows for next step.

• Now from use range O1:R1 to write numbers from 1 to 4.

• In the 2nd helper column (J column) >> write down the following formula.

=IFERROR(IF(COUNTIF($I$2:$I$8,CONCATENATE($A2,$B2))=1,INDEX($E$2:$G$8,MATCH(CONCATENATE($A2,$B2),$I$2:$I$8,0),3),INDEX($G$2:$G$8,SMALL(IF($F$2:$F$8=$B2,ROW($F$2:$F$8)-1,""),($O$1:$R$1)))),"")

• Then drag the Fill Handle up to the J8 cell.
• Now, select C2:C8 cells >> from Data tab >> Data Tools group >> Data Validation >> choose List in Allow section >> write =J2:M2 in Source box >> press OK.

• Now, insert the value in A2 and B2 cells and get the result. Here, in all case, you need to select a value from drop-down menu.

• But when Data 1 and 2 are not duplicated then you will get only one value in the drop-down list. Otherwise, you will get multiple options and can choose any one.

When you are dealing with large dataset, I suggest then to make the calculations in different sheet. So that you can easily manage the space for your data.

 

[bigme]

good morning,
dear Musiha, thank you very much for your explanation, it very clear.....

regards,
bigMe

 

[shamimarita]

good morning,
dear Musiha, thank you very much for your explanation, it very clear.....

regards,
bigMe

You are welcome.