Formula or alternative to populate a cell value based on the criteria of multiple other cells in a table.

[Derick_T]

I have this table that is taking some form data, then using a macros to populate an equipment log for the day some equipment (filter bags) were installed. The form data is filled out with the respective operator who installed, and the relative information we need to uniquely identify the bag and reason for replacement (failure mode, bag. mfr., main compartment #, sub-compartment #, cell, column, row and even comments if needed). Every time data is entered a new line(s) are created on the "history log" tab in the workbook, so that the new filter bag(s) are new line items. This part all works well, and there is no problem there.

My next step, and what I am hoping for some help with, being able to back-populate the existing filter bag line items in the history log with a an equipment Removal Date, which should match the new bag Installation Date. Is there any formulas or good way to do this?

There are thousands of bags installed on this equipment and being storable to track the failures, age, and average failure age is value added data I would like to track and trend. My first though to accomplish this was to have a formula Removal Date to match the new Installation Date as new line items were populated into the history log table although there may be some constraints to doing that or more efficient ways.

Reference snapshots
The form data that we fill out to a create a new line item(s) in the history log tab:

History Log table example with the above data/line item:

So up to now everything is good because these are all new bags that are "In Use". But now if make another entry with those exact bags, I would like the Removal Date column to populate with the Installation Date line items for the respective bags.

For example:

Thanks in advance for the help. I'll gladly share the workbook if needed.

 

[AlanY]

this will work with a helper column I;

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	2	1	1				1|1|2|1|1
2	1	1	2	1	2				1|1|2|1|2
3	1	1	2	1	3				1|1|2|1|3
4	1	1	2	1	4				1|1|2|1|4
5	1	1	2	1	5				1|1|2|1|5
6
7
Sheet3

Cell Formulas
Range	Formula
G1:G5	G1	=IF((COUNTIF($I$1:$I$100,I1)-COUNTIF($I$1:I1,I1))>0,"Removed","")
I1:I5	I1	=TEXTJOIN("|",,A1,B1,C1,D1,E1)

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	2	1	1		Removed		1|1|2|1|1
2	1	1	2	1	2				1|1|2|1|2
3	1	1	2	1	3				1|1|2|1|3
4	1	1	2	1	4				1|1|2|1|4
5	1	1	2	1	5				1|1|2|1|5
6	1	1	2	1	1				1|1|2|1|1
7
8
Sheet3

Cell Formulas
Range	Formula
G1:G6	G1	=IF((COUNTIF($I$1:$I$100,I1)-COUNTIF($I$1:I1,I1))>0,"Removed","")
I1:I6	I1	=TEXTJOIN("|",,A1,B1,C1,D1,E1)

Book1
	A	B	C	D	E	F	G	H	I
1	1	1	2	1	1		Removed		1|1|2|1|1
2	1	1	2	1	2				1|1|2|1|2
3	1	1	2	1	3		Removed		1|1|2|1|3
4	1	1	2	1	4				1|1|2|1|4
5	1	1	2	1	5				1|1|2|1|5
6	1	1	2	1	1				1|1|2|1|1
7	1	1	2	1	3				1|1|2|1|3
8
Sheet3

Cell Formulas
Range	Formula
G1:G7	G1	=IF((COUNTIF($I$1:$I$100,I1)-COUNTIF($I$1:I1,I1))>0,"Removed","")
I1:I7	I1	=TEXTJOIN("|",,A1,B1,C1,D1,E1)

 

[AlanY]

another way without the extra column

Book1
	A	B	C	D	E	F	G
1	1	1	2	1	1
2	1	1	2	1	2
3	1	1	2	1	3
4	1	1	2	1	4
5	1	1	2	1	5
6
7
Sheet3

Cell Formulas
Range	Formula
G1:G7	G1	=IF((COUNTIFS($A$1:$A$100,A1,$B$1:$B$100,B1,$C$1:$C$100,C1,$D$1:$D$100,D1,$E$1:$E$100,E1)-COUNTIFS($A$1:A1,A1,$B$1:B1,B1,$C$1:C1,C1,$D$1:D1,D1,$E$1:E1,E1))>0,"Removed","")

Book1
	A	B	C	D	E	F	G
1	1	1	2	1	1		Removed
2	1	1	2	1	2
3	1	1	2	1	3
4	1	1	2	1	4
5	1	1	2	1	5
6	1	1	2	1	1
7
Sheet3

Cell Formulas
Range	Formula
G1:G7	G1	=IF((COUNTIFS($A$1:$A$100,A1,$B$1:$B$100,B1,$C$1:$C$100,C1,$D$1:$D$100,D1,$E$1:$E$100,E1)-COUNTIFS($A$1:A1,A1,$B$1:B1,B1,$C$1:C1,C1,$D$1:D1,D1,$E$1:E1,E1))>0,"Removed","")

Book1
	A	B	C	D	E	F	G
1	1	1	2	1	1		Removed
2	1	1	2	1	2
3	1	1	2	1	3		Removed
4	1	1	2	1	4
5	1	1	2	1	5
6	1	1	2	1	1
7	1	1	2	1	3
Sheet3

Cell Formulas
Range	Formula
G1:G7	G1	=IF((COUNTIFS($A$1:$A$100,A1,$B$1:$B$100,B1,$C$1:$C$100,C1,$D$1:$D$100,D1,$E$1:$E$100,E1)-COUNTIFS($A$1:A1,A1,$B$1:B1,B1,$C$1:C1,C1,$D$1:D1,D1,$E$1:E1,E1))>0,"Removed","")