Bring three rows to one row remove duplicates

[LuGyver]

This seems simple but can't figure it out. Thank you for helping!

Need a excel 2007 formula (not macro/vba) to bring three rows containing data into one row.
ie As this sheet shows, all three rows 1,2,3 are brought into Row 5 and then duplicates are removed.

 

[István Hirsch]

Try this:

Enter the first formula in cell A4 with Shift+Ctrl+Enter and copy across, then enter the second one with Shift+Ctrl+Enter in cell B5 and copy across.

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

 

[LuGyver]

Try this:

Enter the first formula in cell A4 with Shift+Ctrl+Enter and copy across, then enter the second one with Shift+Ctrl+Enter in cell B5 and copy across.

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]95[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A4 {=IFERROR(SMALL($A$1:$F$3,COLUMNS($A:A)),"")} B4 {=IFERROR(SMALL($A$1:$F$3,COLUMNS($A:B)),"")} C4 {=IFERROR(SMALL($A$1:$F$3,COLUMNS($A:C)),"")} B5 {=IFERROR(INDEX($A$4:$N$4, MATCH(0, COUNTIF($A$5:A5, $A$4:$N$4), 0)),"")} C5 {=IFERROR(INDEX($A$4:$N$4, MATCH(0, COUNTIF($A$5:B5, $A$4:$N$4), 0)),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

István Hirsch, This works and will use it..., but would love to have a single formula. Seems like sumproduct capabilities but could be wrong.
Thank you!
Happy Day!

 

[bosco_yip]

Maybe,

1] A5 kept blank.

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),"")

Regards
Bosco

 

[LuGyver]

Maybe,

1] A5 kept blank.

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),"")

Regards
Bosco

There it is! Bosco this is perfect. I traded the formula in A5 to =SMALL(A1:F3,1) and works great!
Thanks to you both for helping!
Happy New Year!

 

[LuGyver]

There it is! Bosco this is perfect. I traded the formula in A5 to =SMALL(A1:F3,1) and works great!
Thanks to you both for helping!
Happy New Year!

Bosco, everything was fine until a 0 was the smallest number.
The results all turned to zero

I can assume that it is due to the * multiplier. If there is a chance you see this reply, could you help me get past this? Thank you

 

[bosco_yip]

Bosco, everything was fine until a 0 was the smallest number.
The results all turned to zero

I can assume that it is due to the * multiplier. If there is a chance you see this reply, could you help me get past this? Thank you

Do you want your result include zero or exclude zero.

Regards
Bosco

 

[LuGyver]

Do you want your result include zero or exclude zero.

Regards
Bosco

include the zero please
kind wishes for your new year!

 

[bosco_yip]

include the zero please
kind wishes for your new year!

Hi,

1] Source table layout as per post #1 range A1:F3 with 0 (zero) added in C2

2] A5 kept blank

3] In B5, formula copied across right to until blank :

=IF(COLUMNS($A:A)<=SUMPRODUCT(($A$1:$F$3<>"")/(COUNTIF($A$1:$F$3,$A$1:$F$3))),SMALL($A$1:$F$3,COUNTIF($A$1:$F$3,"<="&A5)+1),"")

4] Happy new year to you!

Regards
Bosco

 

[LuGyver]

Hi,

1] Source table layout as per post #1 range A1:F3 with 0 (zero) added in C2

That works. Thank you, One more thing I need to get past... Going to throw this to you and see if I should post a new question or not.
Suppose I wanted to perform the same idea in a different layout?

ie all in one row.
Cells A1:P1 are formula created numbers in three different sets
Cell S1:AD1 --- bring three sets of numbers in same row and remove duplicates.

Is this possible?

[TABLE="width: 200"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G
[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M
[/TD]
[TD]N[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50
[/TD]
[TD]
[/TD]
[TD]85
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]85
[/TD]
[TD]5
[/TD]
[TD]15[/TD]
[TD]65
[/TD]
[TD]75
[/TD]
[TD]95
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]28
[/TD]
[TD]40
[/TD]
[TD]58
[/TD]
[TD]50
[/TD]
[TD]58
[/TD]
[TD]65
[/TD]
[TD]75
[/TD]
[TD]85
[/TD]
[TD]95
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]