Match 3 Values & CONCATENATE custom text + Cell Reference

M

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
42
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:

Code: 
[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]

Code: 
[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.

Code: 
[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
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
ABCDEFGHIJ
1Bar CodeAsset CodeNotif.dateOrder Number Complaint handlingGasCompressorPeriodArrayResult
2300030734L04300812130-Sep-18937051306Redu 937051
330044333430044333430-Sep-18937075Complaint Handling Charges301Complaint Handling Charges
4300151559M10301674030-Sep-18937086Gas Charg901Complaint Handling Charges
5300180010M06310591230-Sep-18937122Complaint Handling Charges302Redu 937122
6300180010M06310591230-Sep-18937122Complaint Handling ChargesCompressor1806Redu Compressor 937122
7300030734L04300812120-Sep-18937051301Complaint Handling Charges
830044333430044333430-Jun-18937075Complaint Handling Charges301Complaint Handling Charges
9300151559M10301674030-Jun-18937086Gas Charg901Complaint Handling Charges
10300180010M06310591230-Jun-18937122Complaint Handling Charges302Redu 937122
11300180010M06310591230-Jun-18937122Complaint Handling ChargesCompressor1801Complaint Handling Charges

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I2{=LARGE(((C2:C11)+H2>C2)*(ROW(C2:C11)-ROW(C1))*((A2:A11)=A2),1)}
I3{=LARGE(((C3:C12)+H3>C3)*(ROW(C3:C12)-ROW(C2))*((A3:A12)=A3),1)}
I4{=LARGE(((C4:C13)+H4>C4)*(ROW(C4:C13)-ROW(C3))*((A4:A13)=A4),1)}
I5{=LARGE(((C5:C14)+H5>C5)*(ROW(C5:C14)-ROW(C4))*((A5:A14)=A5),1)}
I6{=LARGE(((C6:C15)+H6>C6)*(ROW(C6:C15)-ROW(C5))*((A6:A15)=A6),1)}
I7{=LARGE(((C7:C16)+H7>C7)*(ROW(C7:C16)-ROW(C6))*((A7:A16)=A7),1)}
I8{=LARGE(((C8:C17)+H8>C8)*(ROW(C8:C17)-ROW(C7))*((A8:A17)=A8),1)}
I9{=LARGE(((C9:C18)+H9>C9)*(ROW(C9:C18)-ROW(C8))*((A9:A18)=A9),1)}
I10{=LARGE(((C10:C19)+H10>C10)*(ROW(C10:C19)-ROW(C9))*((A10:A19)=A10),1)}
I11{=LARGE(((C11:C20)+H11>C11)*(ROW(C11:C20)-ROW(C10))*((A11:A20)=A11),1)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
marious said:
ABCDEFGHIJ
1Bar CodeAsset CodeNotif.dateOrder NumberComplaint handlingGasCompressorPeriodArrayResult
2300030734L04300812130-Sep-18937051306Redu 937051
330044333430044333430-Sep-18937075Complaint Handling Charges301Complaint Handling Charges
4300151559M10301674030-Sep-18937086Gas Charg901Complaint Handling Charges
5300180010M06310591230-Sep-18937122Complaint Handling Charges302Redu 937122
6300180010M06310591230-Sep-18937122Complaint Handling ChargesCompressor1806Redu Compressor 937122
7300030734L04300812120-Sep-18937051301Complaint Handling Charges
830044333430044333430-Jun-18937075Complaint Handling Charges301Complaint Handling Charges
9300151559M10301674030-Jun-18937086Gas Charg901Complaint Handling Charges
10300180010M06310591230-Jun-18937122Complaint Handling Charges302Redu 937122
11300180010M06310591230-Jun-18937122Complaint Handling ChargesCompressor1801Complaint Handling Charges

<tbody>
</tbody>
Sheet2​

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

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I2{=LARGE(((C2:C11)+H2>C2)*(ROW(C2:C11)-ROW(C1))*((A2:A11)=A2),1)}
I3{=LARGE(((C3:C12)+H3>C3)*(ROW(C3:C12)-ROW(C2))*((A3:A12)=A3),1)}
I4{=LARGE(((C4:C13)+H4>C4)*(ROW(C4:C13)-ROW(C3))*((A4:A13)=A4),1)}
I5{=LARGE(((C5:C14)+H5>C5)*(ROW(C5:C14)-ROW(C4))*((A5:A14)=A5),1)}
I6{=LARGE(((C6:C15)+H6>C6)*(ROW(C6:C15)-ROW(C5))*((A6:A15)=A6),1)}
I7{=LARGE(((C7:C16)+H7>C7)*(ROW(C7:C16)-ROW(C6))*((A7:A16)=A7),1)}
I8{=LARGE(((C8:C17)+H8>C8)*(ROW(C8:C17)-ROW(C7))*((A8:A17)=A8),1)}
I9{=LARGE(((C9:C18)+H9>C9)*(ROW(C9:C18)-ROW(C8))*((A9:A18)=A9),1)}
I10{=LARGE(((C10:C19)+H10>C10)*(ROW(C10:C19)-ROW(C9))*((A10:A19)=A10),1)}
I11{=LARGE(((C11:C20)+H11>C11)*(ROW(C11:C20)-ROW(C10))*((A11:A20)=A11),1)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
269
skyh3ck
skyh3ck
V
  • Question
VBA to copy column based on a header matching a cell value (date). The paste formulas into another column. Finally, hard code values after copying.
Replies
2
Views
4K
vekdas
V
M
  • Question
Check Redo Based on Date difference
2
Replies
14
Views
1K
bellman101
B
A
  • Question
Multiple lookups with date range
Replies
4
Views
470
ALRENDLE
A
M
  • Question
Sum multiple columns - using sumifs / index / match?
Replies
4
Views
1K
MixedUpExcel
M

Forum statistics

Threads
1,214,827
Messages
6,121,824
Members
449,050
Latest member
Bradel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top