CODEWITHSUNDEEP
excelms-office
Mian
Mian

Reputation: 161

Find / Search Excel with multiple conditions in MS Excel

I have been trying to get this issue resolved but finally felt need to come to super masters of stackoverflow.

I am putting record with hyphens and without hyphens in excels cells, and want to get updated if there is any duplicate number been put. GatePass and Voucher number are been put in 2 columns

Gatepass is only one number, but Voucher numbers could be one or two or more than that within the one cell so i use hyphen to separate them. now what i am trying to do is

=IF(AND(COUNTIF(C$2:C2,C3)>0,COUNTIF(D$2:D2,SEARCH("-",D$2:D2)-1)>D3),"D-GPV",IF(COUNTIF(C$12:C12,C13)>0,"Dup-V",IF(COUNTIF(D$12:D12,SEARCH("-",D$12:D12)-1)>D3,"Dup-GP","")))

to get this result given in the image [![enter image description here][2]][2]

So, I want if GP is duplicate then Dup-GP or if Voucher is duplicate then Dup-V, or if both are duplicates then D-GPV

Where i will put full record in voucher cells like, 141-142-143-144 instead of 141-144

Please help

P-s, I want simple formula instead of VB Code and i am using MS Office 2007

Upvotes: 0

Views: 105

Answers (1)

EEM
EEM

Reputation: 6659

Excel 2019 Formula:

Assuming your data starts at A1, enter this formula in L2 and copy downwards.

=CHOOSE( SUM( 1,
     IFERROR( IF( SEARCH( $C2, TEXTJOIN( "-", 1, $C$1:$C1 ) ), 1 ), 0 ),
     IF( COUNTIF( $D$1:$D1, $D2 ) > 0, 2, 0 ) ), TEXT(,), "Dup-V", "Dup-GP", "Dup-GVP" )

enter image description here

Upvotes: 2

Related Questions