formula correction

[LuGyver]

This works great until A1:A3 has a zero involved, then it returns all zero's. Pretty sure it has to do with *(multiplication) but would like to stay with sumproduct if possible.

1] A5 =SMALL(A1:F3,1)

2] In B5, formula copied across right until blank :

=IFERROR(SMALL($A$1:$F$3,SUMPRODUCT(($A$1:$F$3>0)*($A$1:$F$3<=A5))+1),"")

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	28	48	58	20	00	50
2	85	05
3	85	05	15	65	75	95
4
5	00	00	00	00	00	00	00	00	00	00	00	00	00
6

<tbody>
</tbody>

Much appreciated

 

[mrhstn]

Hi

Are you trying to pull a list of unique values from A1:F3?

In B5 try =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>A5), 1), "")

 

[LuGyver]

Hi

Are you trying to pull a list of unique values from A1:F3?

In B5 try =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>A5), 1), "")

Actually want to bring three rows containing data into one row Row 5 and then duplicates are removed.

 

[mrhstn]

OK, the formula I provided will remove the duplicates, but if you want all the values in one row (including duplicates) then use this in A5

=IFERROR(SMALL($A$1:$F$3, COLUMNS($A$1:A1)), "")

Then drag across.

 

[LuGyver]

OK, the formula I provided will remove the duplicates, but if you want all the values in one row (including duplicates) then use this in A5

=IFERROR(SMALL($A$1:$F$3, COLUMNS($A$1:A1)), "")

Then drag across.

The first formula didn't return anything. However the 2nd did fine with duplicates. I can work with that, but if you see a way to overcome the duplicates... even better
Thank you mrhstn

 

[mrhstn]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	28	48	58	20	0	50
2	85	5
3	85	5	15	65	75	95
4
5	0	5	15	20	28	48	50	58	65	75	85
Sheet1

Cell Formulas
Range	Formula
A5	=SMALL(A1:F3, 1)
B5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>A5), 1), "")
C5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>B5), 1), "")
D5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>C5), 1), "")
E5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>D5), 1), "")
F5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>E5), 1), "")
G5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>F5), 1), "")
H5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>G5), 1), "")
I5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>H5), 1), "")
J5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>I5), 1), "")
K5	=IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>J5), 1), "")

 

[LuGyver]

	A	B	C	D	E	F	G	H	I	J	K
1	28	48	58	20	0	50
2	85	5
3	85	5	15	65	75	95
4
5	0	5	15	20	28	48	50	58	65	75	85

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

Sheet1​

Worksheet Formulas Cell Formula A5 =SMALL(A1:F3, 1) B5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>A5), 1), "") C5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>B5), 1), "") D5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>C5), 1), "") E5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>D5), 1), "") F5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>E5), 1), "") G5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>F5), 1), "") H5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>G5), 1), "") I5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>H5), 1), "") J5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>I5), 1), "") K5 =IFERROR(AGGREGATE(15, 6, $A$1:$F$3/($A$1:$F$3>J5), 1), "") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Using Excel 2007...?
still nothing B5 auto fill across to K5. format is number with zero decimal places. Looks like this is working on your side.

 

[mrhstn]

Excel 2007 doesn't have AGGREGATE, so... in place of the AGGREGATE, but retaining the first SMALL try this


This needs to be entered using CTRL+SHIFT+ENTER, not just enter.

=IFERROR(SMALL(IF($A$1:$F$3>A5, $A$1:$F$3), 1), "")

Put it in B5 and copy across.

 

[mrhstn]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	28	48	58	20	0	50
2	85	5
3	85	5	15	65	75	95
4
5	0	5	15	20	28	48	50	58	65	75	85	95
Sheet1

Cell Formulas
Range	Formula
A5	=SMALL(A1:F3, 1)
B5	{=IFERROR(SMALL(IF($A$1:$F$3>A5, $A$1:$F$3), 1), "")}
C5	{=IFERROR(SMALL(IF($A$1:$F$3>B5, $A$1:$F$3), 1), "")}
D5	{=IFERROR(SMALL(IF($A$1:$F$3>C5, $A$1:$F$3), 1), "")}
E5	{=IFERROR(SMALL(IF($A$1:$F$3>D5, $A$1:$F$3), 1), "")}
F5	{=IFERROR(SMALL(IF($A$1:$F$3>E5, $A$1:$F$3), 1), "")}
G5	{=IFERROR(SMALL(IF($A$1:$F$3>F5, $A$1:$F$3), 1), "")}
H5	{=IFERROR(SMALL(IF($A$1:$F$3>G5, $A$1:$F$3), 1), "")}
I5	{=IFERROR(SMALL(IF($A$1:$F$3>H5, $A$1:$F$3), 1), "")}
J5	{=IFERROR(SMALL(IF($A$1:$F$3>I5, $A$1:$F$3), 1), "")}
K5	{=IFERROR(SMALL(IF($A$1:$F$3>J5, $A$1:$F$3), 1), "")}
L5	{=IFERROR(SMALL(IF($A$1:$F$3>K5, $A$1:$F$3), 1), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LuGyver]

Excel 2007 doesn't have AGGREGATE, so... in place of the AGGREGATE, but retaining the first SMALL try this


This needs to be entered using CTRL+SHIFT+ENTER, not just enter.

=IFERROR(SMALL(IF($A$1:$F$3>A5, $A$1:$F$3), 1), "")

Put it in B5 and copy across.

Bingo! Thank you mrhstn, I appreciate you