Excel vba or formula to Look for non zero in excel sheet row

[Muhammad Hussaan]

Hello,
I need help to avoid alot of manual work and error.
I have sheet with width of material in row 4 column C to AV and possible cut patterns in row 5 to 103 in column C to AV having values of 0 to 5. I want the width against non zeros and number of times if it is greater than 1. As in Row 5 only two values are greater than ZERO in range C:5 to AV5 and values are 1 and 2 in cell V5 and Z5 respectively and againts these cells in row 4 we have 56 and 60. 56 has 1 in row 5 and 60 has 2 in row 5 therefore i write it as 56 |60 |60 columns AZ to BF manually.
How to to it by some formula or vba macro?
Kindly help.
Thanks

 

[Peter_SSs]

To get better & quicker answers I suggest ..

1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
2. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
3. Provide small sample data that still demonstrates your issue. For example we didn't need to see 30 columns of this to get the idea (though you could still tell us that there are about 30 columns)

If you have the CONCAT function (if your version was shown in your profile I would know if you have the function) you could try this for a formula approach. The formula in H5 is copied across as far as you might need and down.

20 04 18.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	80	81	82	83	84
5	1	2	0	1	0		80	81	81	83
6	0	5	0	0	2		81	81	81	81	81	84	84
Non zero columns

Cell Formulas
Range	Formula
H5:P6	H5	=IFERROR(TRIM(MID(CONCAT(REPT(REPT(" ",100)&$B$4:$F$4,$B5:$F5)),COLUMNS($H:H)*100,100))+0,"")

 

[Muhammad Hussaan]

To get better & quicker answers I suggest ..

1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
2. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
3. Provide small sample data that still demonstrates your issue. For example we didn't need to see 30 columns of this to get the idea (though you could still tell us that there are about 30 columns)

If you have the CONCAT function (if your version was shown in your profile I would know if you have the function) you could try this for a formula approach. The formula in H5 is copied across as far as you might need and down.

20 04 18.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	80	81	82	83	84
5	1	2	0	1	0		80	81	81	83
6	0	5	0	0	2		81	81	81	81	81	84	84
Non zero columns

Cell Formulas
Range	Formula
H5:P6	H5	=IFERROR(TRIM(MID(CONCAT(REPT(REPT(" ",100)&$B$4:$F$4,$B5:$F5)),COLUMNS($H:H)*100,100))+0,"")

Thanks i will try this.
I will follow the instructions also.
I have excel 2013.

 

[Peter_SSs]

I have excel 2013.

Please add it to your profile as suggested.

i will try this.

No need to, it will not work for you - because you have Excel 2013 (why you need it in your profile ).

Provided row 4 is ever-increasing as in your sample, you could try this instead.

20 04 18.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	80	81	82	82.5	90
5	1	2	0	1	0		80	81	81	82.5
6	0	5	0	0	2		81	81	81	81	81	90	90
Non zero columns (2)

Cell Formulas
Range	Formula
H5:P6	H5	=IF(COLUMNS($H:H)>SUM($B5:$F5),"",INDEX($B$4:$F$4,IFERROR(MATCH(COLUMNS($H5:H5)-1,SUMIF($B$4:$F$4,"<="&$B$4:$F$4,$B5:$F5)),0)+1))

 

[Peter_SSs]

If you wanted to consider a vba approach, then you could try this user-defined function.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.

Function Seq(rHdrs As Range, rCount As Range, Num As Long) As Variant
  Dim a As Variant, d As Variant
  Dim i As Long, j As Long, k As Long
  
  a = rHdrs.Value
  d = rCount.Value
  Seq = vbNullString
  For i = 1 To UBound(d, 2)
    For j = 1 To d(1, i)
      k = k + 1
      If k = Num Then Seq = a(1, i)
    Next j
  Next i
End Function

Muhammad Hussaan 2020-04-18 1.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	80	81	82	82.5	90
5	1	2	0	1	0		80	81	81	82.5
6	0	5	0	0	2		81	81	81	81	81	90	90
Non zero columns (3)

Cell Formulas
Range	Formula
H5:P6	H5	=Seq($B$4:$F$4,$B5:$F5,COLUMNS($H:H))

 

[Muhammad Hussaan]

If you wanted to consider a vba approach, then you could try this user-defined function.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.

Function Seq(rHdrs As Range, rCount As Range, Num As Long) As Variant
  Dim a As Variant, d As Variant
  Dim i As Long, j As Long, k As Long
  
  a = rHdrs.Value
  d = rCount.Value
  Seq = vbNullString
  For i = 1 To UBound(d, 2)
    For j = 1 To d(1, i)
      k = k + 1
      If k = Num Then Seq = a(1, i)
    Next j
  Next i
End Function

Muhammad Hussaan 2020-04-18 1.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	80	81	82	82.5	90
5	1	2	0	1	0		80	81	81	82.5
6	0	5	0	0	2		81	81	81	81	81	90	90
Non zero columns (3)

Cell Formulas
Range	Formula
H5:P6	H5	=Seq($B$4:$F$4,$B5:$F5,COLUMNS($H:H))

Dear Peter,
Thanks alot, vba code working perfectly.

 

[Peter_SSs]

Dear Peter,
Thanks alot, vba code working perfectly.

You're welcome. Thanks for the follow-up.

(Still cannot see your Excel version in your profile at left. )

 

[Muhammad Hussaan]

You're welcome. Thanks for the follow-up.

(Still cannot see your Excel version in your profile at left. )

How to view the UDF code that we use?

 

[Peter_SSs]

How to view the UDF code that we use?

Same as this ..

1. Right click the sheet name tab and choose "View Code".