Help with dynamic formula that will prints text

[locksmith55]

Hello,

I am trying to make a dynamic formula in excel that will read when there is 1 inside of a cell and will print the serial number that is above it in column I. I need the formula to work row by row and left to right. If there is a better way to do this using helper columns or chaning the date but still getting the same result, please let me know.

Would prefer if the solution is not too complex. Helper columns are welcome.

Appreciate any help I can get!

Book1
	A	B	C	D	E	F	G	H	I	J
1		Serial Numbers				Rows to print of serial number when 1	3		HOW I WANT IT TO LOOK	Just to better visualize data
2		VNR12456	BRD32561	AKK98631					BRD32561	these are from apple row 3
3	Apple	0	1	0		Use of helper columns are welcome			BRD32561
4	Mango	0	1	1					BRD32561
5	Pear	0	1	0					BRD32561	these are from mango row 4
6	Banana	0	1	1					BRD32561
7	Orange	1	0	0					BRD32561
8									AKK98631	these are from mango row 4
9									AKK98631
10									AKK98631
11									BRD32561	these are from pear row 5
12									BRD32561
13									BRD32561
14									BRD32561	these are from banana row 6
15									BRD32561
16									BRD32561
17									AKK98631	these are from banana row 6
18									AKK98631
19									AKK98631
20									VNR12456	these are from orange row 7
21									VNR12456
22									VNR12456
Sheet1

Cell Formulas
Range	Formula
I2	I2	=C2
I3	I3	=C2
I4	I4	=C2
I5	I5	=C2
I6	I6	=C2
I7	I7	=C2
I8	I8	=D2
I9	I9	=D2
I10	I10	=D2
I11	I11	=C2
I12	I12	=C2
I13	I13	=C2
I14	I14	=C2
I15	I15	=C2
I16	I16	=C2
I17	I17	=D2
I18	I18	=D2
I19	I19	=D2
I20	I20	=B2
I21	I21	=B2
I22	I22	=B2

 

[jolivanes]

Not a formula but just a simple macro.

Sub Maybe()
Dim i As Long, j As Long
ActiveSheet.Columns(5).ClearContents    '<---- Comment out if not needed
    For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
        For j = 2 To 4
            If Cells(i, j).Value = 1 Then Cells(Rows.Count, 5).End(xlUp).Offset(1).Resize(3).Value = Cells(2, j).Value
        Next j
    Next i
End Sub

 

[Peter_SSs]

See how this goes.

22 07 24.xlsm
	A	B	C	D	E	F	G	H	I
1		Serial Numbers				Rows to print	3		RESULT
2		VNR12456	BRD32561	AKK98631					BRD32561
3	Apple	0	1	0					BRD32561
4	Mango	0	1	1					BRD32561
5	Pear	0	1	0					BRD32561
6	Banana	0	1	1					BRD32561
7	Orange	1	0	0					BRD32561
8									AKK98631
9									AKK98631
10									AKK98631
11									BRD32561
12									BRD32561
13									BRD32561
14									BRD32561
15									BRD32561
16									BRD32561
17									AKK98631
18									AKK98631
19									AKK98631
20									VNR12456
21									VNR12456
22									VNR12456
23
locksmith55

Cell Formulas
Range	Formula
I2:I22	I2	=FILTERXML("<p><c>"&REPLACE(CONCAT(IF(B3:D7=1,REPT("</c><c>"&B2:D2,G$1),"")),1,7,"")&"</c></p>","//c")
Dynamic array formulas.