Match 3 Values & CONCATENATE custom text + Cell Reference

[mohdamir1989]

Hi All,

I am working with bar codes of products. Any bar code repeated in specific period is considered redo. For this I have created 4 helper columns which are somewhat fulfilling the need. But I still need to read whole description of work done to specify if it is redo or not.

What I am trying to achieve is as below and I have no clue how it can be achieved:

[B]Excel 2007 32 bit[/B][SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH][CENTER][COLOR=#FFFFFF]A[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]B[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]C[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]P[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]U[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]Y[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]Z[/COLOR][/CENTER]
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]1[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER][B]O[/B][/CENTER]
[/TD]
[TD][CENTER][B]P[/B][/CENTER]
[/TD]
[TD][CENTER][B]Q[/B][/CENTER]
[/TD]
[TD][CENTER][B]AD[/B][/CENTER]
[/TD]
[TD][CENTER][B]AI[/B][/CENTER]
[/TD]
[TD][CENTER][B]AM[/B][/CENTER]
[/TD]
[TD][CENTER][B]AN[/B][/CENTER]
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]2[/B][/COLOR][/CENTER]
[/TD]
[TD="bgcolor: #FF0000"][B][COLOR=#FFFFFF]Notif.date[/COLOR][/B][/TD]
[TD="bgcolor: #FF0000"][B][COLOR=#FFFFFF]SEND Date[/COLOR][/B][/TD]
[TD="bgcolor: #00B050"][CENTER][B]Bar Code[/B][/CENTER]
[/TD]
[TD="bgcolor: #00B050"][B]Redo[/B][/TD]
[TD="bgcolor: #FFFF00"][B]Complaint Handling[/B][/TD]
[TD="bgcolor: #FFFF00"][B]Compressor[/B][/TD]
[TD="bgcolor: #FFFF00"][B]Gas[/B][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]3[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]1-Oct[/CENTER]
[/TD]
[TD][CENTER]2-Oct[/CENTER]
[/TD]
[TD][CENTER]300227519[/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][COLOR=#333333]Gas Charges[/COLOR][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]4[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]3-Oct[/CENTER]
[/TD]
[TD][CENTER]11-Oct[/CENTER]
[/TD]
[TD][CENTER]300289708[/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] GAS CHARGING & SERVICE - BASE - L2[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]5[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]31-Oct[/CENTER]
[/TD]
[TD][CENTER]1-Nov[/CENTER]
[/TD]
[TD][CENTER]300133608[/CENTER]
[/TD]
[TD][/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]6[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]12-Nov[/CENTER]
[/TD]
[TD][CENTER]13-Nov[/CENTER]
[/TD]
[TD][CENTER]300146956[/CENTER]
[/TD]
[TD][/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]7[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]13-Nov[/CENTER]
[/TD]
[TD][CENTER]14-Nov[/CENTER]
[/TD]
[TD][CENTER]300187650[/CENTER]
[/TD]
[TD][/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]8[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]10-Nov[/CENTER]
[/TD]
[TD][CENTER]14-Nov[/CENTER]
[/TD]
[TD][CENTER]300149826[/CENTER]
[/TD]
[TD][/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]9[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]10-Nov[/CENTER]
[/TD]
[TD][CENTER]14-Nov[/CENTER]
[/TD]
[TD][CENTER]300355055[/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][COLOR=#333333]Compressor 1/4 HP (8.5G) REFURBISHED[/COLOR][/TD]
[TD][COLOR=#333333]Gas Charges[/COLOR][/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: [B]Daily Feedback[/B][/TD]
[/TR]
</tbody>[/TABLE]

[B]Excel 2007 32 bit[/B][SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH][CENTER][COLOR=#FFFFFF]A[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]B[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]E[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]F[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]H[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]I[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]J[/COLOR][/CENTER]
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]1[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER][B]D[/B][/CENTER]
[/TD]
[TD][CENTER][B]E[/B][/CENTER]
[/TD]
[TD][CENTER][B]H[/B][/CENTER]
[/TD]
[TD][CENTER][B]I[/B][/CENTER]
[/TD]
[TD][CENTER][B]K[/B][/CENTER]
[/TD]
[TD][CENTER][B]L[/B][/CENTER]
[/TD]
[TD][CENTER][B]M[/B][/CENTER]
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]2[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER][B]Bar Code[/B][/CENTER]
[/TD]
[TD][CENTER][B]Asset Code[/B][/CENTER]
[/TD]
[TD][CENTER][B]Notif.date[/B][/CENTER]
[/TD]
[TD][CENTER][B]Order Number [/B][/CENTER]
[/TD]
[TD][CENTER][B]Complaint handling[/B][/CENTER]
[/TD]
[TD][CENTER][B]Gas[/B][/CENTER]
[/TD]
[TD][CENTER][B]Compressor[/B][/CENTER]
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]3[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]300030734[/CENTER]
[/TD]
[TD][CENTER]L043008121[/CENTER]
[/TD]
[TD][CENTER]30-Sep-18[/CENTER]
[/TD]
[TD][CENTER]937051[/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]4[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]300443334[/CENTER]
[/TD]
[TD][CENTER]300443334[/CENTER]
[/TD]
[TD][CENTER]30-Sep-18[/CENTER]
[/TD]
[TD][CENTER]937075[/CENTER]
[/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]5[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]300151559[/CENTER]
[/TD]
[TD][CENTER]M103016740[/CENTER]
[/TD]
[TD][CENTER]30-Sep-18[/CENTER]
[/TD]
[TD][CENTER]937086[/CENTER]
[/TD]
[TD][/TD]
[TD][COLOR=#333333]Gas Charg[/COLOR][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]6[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]300180010[/CENTER]
[/TD]
[TD][CENTER]M063105912[/CENTER]
[/TD]
[TD][CENTER]30-Sep-18[/CENTER]
[/TD]
[TD][CENTER]937122[/CENTER]
[/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]7[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]300180010[/CENTER]
[/TD]
[TD][CENTER]M063105912[/CENTER]
[/TD]
[TD][CENTER]30-Sep-18[/CENTER]
[/TD]
[TD][CENTER]937122[/CENTER]
[/TD]
[TD] Complaint Handling Charges[/TD]
[TD][/TD]
[TD][COLOR=#333333]Compressor[/COLOR][/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: [B]Previous[/B][/TD]
[/TR]
</tbody>[/TABLE]

Historical data is available in "Previous" arranged Newest to oldest as bar code is repetitive. I have to check the last most. If bar code have any of the charges mentioned "complaint handling charges, Gas Charges, Compressor" and the same is repeated again it should return Order number in "Redo" column.

[B]Excel 2007 32 bit[/B][SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH][CENTER][COLOR=#FFFFFF]A[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]B[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]C[/COLOR][/CENTER]
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]1[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]Redo Criteria[/CENTER]
[/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]2[/B][/COLOR][/CENTER]
[/TD]
[TD]Complaint Handling[/TD]
[TD]If bar code is repeated within 30 days[/TD]
[TD]Redo Service + Previous Order No.[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]3[/B][/COLOR][/CENTER]
[/TD]
[TD]Compressor[/TD]
[TD]If bar code is repeated within 180 days[/TD]
[TD]Redo Compressor + Previous Order No.[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"][CENTER][COLOR=#FFFFFF][B]4[/B][/COLOR][/CENTER]
[/TD]
[TD]Gas[/TD]
[TD]If bar code is repeated in 90 days[/TD]
[TD]Redo Gas + Previous Order No.[/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: [B]Sheet3[/B][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance.

Best Regards

 

[marious]

	A	B	C	D	E	F	G	H	I	J
Redu 937051
Complaint Handling Charges	Complaint Handling Charges
Gas Charg	Complaint Handling Charges
Complaint Handling Charges	Redu 937122
Complaint Handling Charges	Compressor	Redu Compressor 937122
Complaint Handling Charges
Complaint Handling Charges	Complaint Handling Charges
Gas Charg	Complaint Handling Charges
Complaint Handling Charges	Redu 937122
Complaint Handling Charges	Compressor	Complaint Handling Charges

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Bar Code[/TD]
[TD="align: center"]Asset Code[/TD]
[TD="align: center"]Notif.date[/TD]
[TD="align: center"]Order Number [/TD]
[TD="align: center"]Complaint handling[/TD]
[TD="align: center"]Gas[/TD]
[TD="align: center"]Compressor[/TD]
[TD="align: center"]Period[/TD]
[TD="align: center"]Array[/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]300030734[/TD]
[TD="align: center"]L043008121[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937051[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937075[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]300151559[/TD]
[TD="align: center"]M103016740[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937086[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]300030734[/TD]
[TD="align: center"]L043008121[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]20-Sep-18[/TD]
[TD="align: center"]937051[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937075[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]300151559[/TD]
[TD="align: center"]M103016740[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937086[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(G2="Compressor",180,IF(F2="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=IF(G3="Compressor",180,IF(F3="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=IF(G4="Compressor",180,IF(F4="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF(G5="Compressor",180,IF(F5="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]=IF(G6="Compressor",180,IF(F6="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H7[/TH]
[TD="align: left"]=IF(G7="Compressor",180,IF(F7="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H8[/TH]
[TD="align: left"]=IF(G8="Compressor",180,IF(F8="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H9[/TH]
[TD="align: left"]=IF(G9="Compressor",180,IF(F9="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H10[/TH]
[TD="align: left"]=IF(G10="Compressor",180,IF(F10="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H11[/TH]
[TD="align: left"]=IF(G11="Compressor",180,IF(F11="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(I2>1,"Redu "& CONCAT(F2:G2)&" "&INDEX(D2:D11,I2,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=IF(I3>1,"Redu "& CONCAT(F3:G3)&" "&INDEX(D3:D12,I3,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=IF(I4>1,"Redu "& CONCAT(F4:G4)&" "&INDEX(D4:D13,I4,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J5[/TH]
[TD="align: left"]=IF(I5>1,"Redu "& CONCAT(F5:G5)&" "&INDEX(D5:D14,I5,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J6[/TH]
[TD="align: left"]=IF(I6>1,"Redu "& CONCAT(F6:G6)&" "&INDEX(D6:D15,I6,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J7[/TH]
[TD="align: left"]=IF(I7>1,"Redu "& CONCAT(F7:G7)&" "&INDEX(D7:D16,I7,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J8[/TH]
[TD="align: left"]=IF(I8>1,"Redu "& CONCAT(F8:G8)&" "&INDEX(D8:D17,I8,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J9[/TH]
[TD="align: left"]=IF(I9>1,"Redu "& CONCAT(F9:G9)&" "&INDEX(D9:D18,I9,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J10[/TH]
[TD="align: left"]=IF(I10>1,"Redu "& CONCAT(F10:G10)&" "&INDEX(D10:D19,I10,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J11[/TH]
[TD="align: left"]=IF(I11>1,"Redu "& CONCAT(F11:G11)&" "&INDEX(D11:D20,I11,1),"Complaint Handling Charges")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=LARGE(((C2:C11)+H2>C2)*(ROW(C2:C11)-ROW(C1))*((A2:A11)=A2),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]{=LARGE(((C3:C12)+H3>C3)*(ROW(C3:C12)-ROW(C2))*((A3:A12)=A3),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]{=LARGE(((C4:C13)+H4>C4)*(ROW(C4:C13)-ROW(C3))*((A4:A13)=A4),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]{=LARGE(((C5:C14)+H5>C5)*(ROW(C5:C14)-ROW(C4))*((A5:A14)=A5),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]{=LARGE(((C6:C15)+H6>C6)*(ROW(C6:C15)-ROW(C5))*((A6:A15)=A6),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I7[/TH]
[TD="align: left"]{=LARGE(((C7:C16)+H7>C7)*(ROW(C7:C16)-ROW(C6))*((A7:A16)=A7),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I8[/TH]
[TD="align: left"]{=LARGE(((C8:C17)+H8>C8)*(ROW(C8:C17)-ROW(C7))*((A8:A17)=A8),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I9[/TH]
[TD="align: left"]{=LARGE(((C9:C18)+H9>C9)*(ROW(C9:C18)-ROW(C8))*((A9:A18)=A9),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I10[/TH]
[TD="align: left"]{=LARGE(((C10:C19)+H10>C10)*(ROW(C10:C19)-ROW(C9))*((A10:A19)=A10),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I11[/TH]
[TD="align: left"]{=LARGE(((C11:C20)+H11>C11)*(ROW(C11:C20)-ROW(C10))*((A11:A20)=A11),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[marious]

It doesn't consider the option of more than one order in the same day but most of the work is done with the array in col I

 

[mohdamir1989]

	A	B	C	D	E	F	G	H	I	J
Redu 937051
Complaint Handling Charges	Complaint Handling Charges
Gas Charg	Complaint Handling Charges
Complaint Handling Charges	Redu 937122
Complaint Handling Charges	Compressor	Redu Compressor 937122
Complaint Handling Charges
Complaint Handling Charges	Complaint Handling Charges
Gas Charg	Complaint Handling Charges
Complaint Handling Charges	Redu 937122
Complaint Handling Charges	Compressor	Complaint Handling Charges

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Bar Code[/TD]
[TD="align: center"]Asset Code[/TD]
[TD="align: center"]Notif.date[/TD]
[TD="align: center"]Order Number[/TD]
[TD="align: center"]Complaint handling[/TD]
[TD="align: center"]Gas[/TD]
[TD="align: center"]Compressor[/TD]
[TD="align: center"]Period[/TD]
[TD="align: center"]Array[/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]300030734[/TD]
[TD="align: center"]L043008121[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937051[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937075[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]300151559[/TD]
[TD="align: center"]M103016740[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937086[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Sep-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]300030734[/TD]
[TD="align: center"]L043008121[/TD]
[TD="align: center"]20-Sep-18[/TD]
[TD="align: center"]937051[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]300443334[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937075[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]300151559[/TD]
[TD="align: center"]M103016740[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937086[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]300180010[/TD]
[TD="align: center"]M063105912[/TD]
[TD="align: center"]30-Jun-18[/TD]
[TD="align: center"]937122[/TD]

[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=IF(G2="Compressor",180,IF(F2="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H3[/TH]
[TD="align: left"]=IF(G3="Compressor",180,IF(F3="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H4[/TH]
[TD="align: left"]=IF(G4="Compressor",180,IF(F4="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H5[/TH]
[TD="align: left"]=IF(G5="Compressor",180,IF(F5="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H6[/TH]
[TD="align: left"]=IF(G6="Compressor",180,IF(F6="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H7[/TH]
[TD="align: left"]=IF(G7="Compressor",180,IF(F7="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H8[/TH]
[TD="align: left"]=IF(G8="Compressor",180,IF(F8="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H9[/TH]
[TD="align: left"]=IF(G9="Compressor",180,IF(F9="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H10[/TH]
[TD="align: left"]=IF(G10="Compressor",180,IF(F10="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]H11[/TH]
[TD="align: left"]=IF(G11="Compressor",180,IF(F11="Gas Charg",90,30))[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=IF(I2>1,"Redu "& CONCAT(F2:G2)&" "&INDEX(D2:D11,I2,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J3[/TH]
[TD="align: left"]=IF(I3>1,"Redu "& CONCAT(F3:G3)&" "&INDEX(D3:D12,I3,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J4[/TH]
[TD="align: left"]=IF(I4>1,"Redu "& CONCAT(F4:G4)&" "&INDEX(D4:D13,I4,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J5[/TH]
[TD="align: left"]=IF(I5>1,"Redu "& CONCAT(F5:G5)&" "&INDEX(D5:D14,I5,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J6[/TH]
[TD="align: left"]=IF(I6>1,"Redu "& CONCAT(F6:G6)&" "&INDEX(D6:D15,I6,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J7[/TH]
[TD="align: left"]=IF(I7>1,"Redu "& CONCAT(F7:G7)&" "&INDEX(D7:D16,I7,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J8[/TH]
[TD="align: left"]=IF(I8>1,"Redu "& CONCAT(F8:G8)&" "&INDEX(D8:D17,I8,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J9[/TH]
[TD="align: left"]=IF(I9>1,"Redu "& CONCAT(F9:G9)&" "&INDEX(D9:D18,I9,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J10[/TH]
[TD="align: left"]=IF(I10>1,"Redu "& CONCAT(F10:G10)&" "&INDEX(D10:D19,I10,1),"Complaint Handling Charges")[/TD]
[/TR]
[TR]
[TH]J11[/TH]
[TD="align: left"]=IF(I11>1,"Redu "& CONCAT(F11:G11)&" "&INDEX(D11:D20,I11,1),"Complaint Handling Charges")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]{=LARGE(((C2:C11)+H2>C2)*(ROW(C2:C11)-ROW(C1))*((A2:A11)=A2),1)}[/TD]
[/TR]
[TR]
[TH]I3[/TH]
[TD="align: left"]{=LARGE(((C3:C12)+H3>C3)*(ROW(C3:C12)-ROW(C2))*((A3:A12)=A3),1)}[/TD]
[/TR]
[TR]
[TH]I4[/TH]
[TD="align: left"]{=LARGE(((C4:C13)+H4>C4)*(ROW(C4:C13)-ROW(C3))*((A4:A13)=A4),1)}[/TD]
[/TR]
[TR]
[TH]I5[/TH]
[TD="align: left"]{=LARGE(((C5:C14)+H5>C5)*(ROW(C5:C14)-ROW(C4))*((A5:A14)=A5),1)}[/TD]
[/TR]
[TR]
[TH]I6[/TH]
[TD="align: left"]{=LARGE(((C6:C15)+H6>C6)*(ROW(C6:C15)-ROW(C5))*((A6:A15)=A6),1)}[/TD]
[/TR]
[TR]
[TH]I7[/TH]
[TD="align: left"]{=LARGE(((C7:C16)+H7>C7)*(ROW(C7:C16)-ROW(C6))*((A7:A16)=A7),1)}[/TD]
[/TR]
[TR]
[TH]I8[/TH]
[TD="align: left"]{=LARGE(((C8:C17)+H8>C8)*(ROW(C8:C17)-ROW(C7))*((A8:A17)=A8),1)}[/TD]
[/TR]
[TR]
[TH]I9[/TH]
[TD="align: left"]{=LARGE(((C9:C18)+H9>C9)*(ROW(C9:C18)-ROW(C8))*((A9:A18)=A9),1)}[/TD]
[/TR]
[TR]
[TH]I10[/TH]
[TD="align: left"]{=LARGE(((C10:C19)+H10>C10)*(ROW(C10:C19)-ROW(C9))*((A10:A19)=A10),1)}[/TD]
[/TR]
[TR]
[TH]I11[/TH]
[TD="align: left"]{=LARGE(((C11:C20)+H11>C11)*(ROW(C11:C20)-ROW(C10))*((A11:A20)=A11),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your time and consideration dear @marious . The scenario is bit different. As you can see first sheet is "Daily Feedback" main worksheet where "Redo" column needs desired results. Worksheet "Previous" is just to store and recall historical bar code data to calculate redo.