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.

 

[bosco_yip]

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]

Hi,

1] Why you have 2 nos. 58 in your result range?

2] Why 48 does not show in your result range?

3] Have you try to use formula posted in post #9 , just changing the cell reference?

Regards
Bosco

 

[LuGyver]

Hi,

1] Why you have 2 nos. 58 in your result range?

2] Why 48 does not show in your result range?

3] Have you try to use formula posted in post #9 , just changing the cell reference?

Regards
Bosco

did not catch that... one of those 58 should have been the 48. I simply Hand-typed them into the cells so you could see what I was talking about.
My concern, because I am not sure if it will have an effect on results, was the entire column referencing $A:A when other data are below and above but are not included.

I gave this change a try and it seems to work, but if you see something wrong with it please let me know.
=IF(COLUMNS($A1:A3)<=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),"")

Thank you so much for your help Bosco

 

[Mike Szczesny]

Can you use something like this? This is a crazy complicated but robust formula to produce all your numbers across a row. Then I used a formula to produce the unique numbers. The complicated formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =INDEX($A$1:$L$4,INT(SMALL(IF($A$1:$L$4<>"",(ROW($A$1:$L$4)-ROW($A$1)+1)*10^9+COLUMN($A$1:$L$4)-COLUMN($A$1)+1),COLUMNS($A$6:A6))/10^9),MOD(SMALL(IF($A$1:$L$4<>"",(ROW($A$1:$L$4)-ROW($A$1)+1)*10^9+COLUMN($A$1:$L$4)-COLUMN($A$1)+1),COLUMNS($A$6:A6)),10^9))

The formula to produce the unique numbers is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =INDEX($A$6:$X$6,MATCH(0,COUNTIF($A$8:A8,$A$6:$X$6),0))[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1536"]
<colgroup><col width="64" span="24" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]28[/TD]
[TD="width: 64, align: right"]48[/TD]
[TD="width: 64, align: right"]58[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]40[/TD]
[TD="width: 64, align: right"]50[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]85[/TD]
[TD="align: right"]5[/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]
[TR]
[TD="align: right"]85[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]95[/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]
[TR]
[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]
[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]
[TR]
[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]
[TR]
[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="align: right"]85[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]95[/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]
[/TR]
[TR]
[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]
[TR]
[TD]Unique[/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="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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]