match array1 W/array 2, then bring into a sorted, single row

[LuGyver]

Using Windows 10, Excel 2007.
Eric W. this is what I was talking about

Trying to create a formula that will take all that match in (Array1) A1:N8 with (Array2) A10:N17 and then place them in a single row, sorted.
I can get them singled out but no able to figure out how to get them into one row...

P1:AC8 (yellow area) shows the matching, however I can't figure out how to bring them together to look like P12:AG12 (green area).

The final goal would eliminate the yellow section (P1:AC8) and only have one single sorted row (P12:AG12) in its place

Hope that makes sense and thank you for any help

2Arrays-Compare.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	285	364	289	917	538	153	247	487	110	958	196	887	914	280		285	364						487
2	89	626	686	995	672	908	270	772	226	315	26	722	11	196												26
3	522	845	488	737	139	853	780	318	965	73	264	339	563	821						139	853	780
4	125	689	424	133	158	662	439	250	429	889	9	605	646	69			689				662	439		429
5	33	407	421	692	823	981	246	745	935	285	668	1	613	47					692						285
6	642	292	517	256	814	580	240	956	822	272	340	784	90	672							580
7	672	838	318	426	796	534	927	877	524	193	215	745	922	321											193	215
8	274	804	154	789	624	119	36	1	666	588	393	54	953	222			804
9
10	687	352	662	580	378	805	454	418	286	285	591	786	733	603
11	904	95	115	709	837	492	304	553	688	235	374	983	783	675
12	579	849	211	139	439	769	692	225	853	138	536	586	636	273		026	139	193	215	285	364	429	439	487	580	662	689	692	780	804	853
13	419	88	319	137	477	815	920	48	512	639	71	322	551	870
14	570	269	364	487	535	988	699	888	113	756	278	609	714	804
15	944	215	781	344	418	193	395	819	660	116	248	26	10	31
16	409	135	295	162	634	229	874	344	405	429	452	176	443	212
17	859	596	808	689	780	791	916	971	78	618	944	990	39	120
18
Sheet1

Cell Formulas
Range	Formula
P1:AC8	P1	=IF(COUNTIF($A$10:$N$17,A1),A1,"")
P12	P12	=SMALL($P$1:$AC$8,1)
Q12:AG12	Q12	=IFERROR(SMALL($P1:$AC8,SUMPRODUCT(($P1:$AC8>0)*($P1:$AC8<=P12))+1),"")

 

[Eric W]

Thank you for using XL2BB! It makes it much easier to work with, especially with a lot of data. You can try:

Book1 (version 1).xlsb
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
12	26	139	193	215	285	364	429	439	487	580	662	689	692	780	804	853	#NUM!	#NUM!
Sheet6

Cell Formulas
Range	Formula
P12:AG12	P12	=SMALL(IF(COUNTIF($A$10:$N$17,$A$1:$N$8)*(COUNTIF($O12:O$13,$A$1:$N$8)=0),$A$1:$N$8),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

This assumes that the numbers are actual numbers, not numbers saved as text. But your expected result of "026" makes me wonder about that. Also if you had Excel 2010 or newer, I'd put an IFERROR in the formula so you don't get the error codes. But there are some options for that if you want. But see how this works so far. This also removes duplicates, so a number will only show up once.

 

[Eric W]

Tiny typo in the formula, it should be:

=SMALL(IF(COUNTIF($A$10:$N$17,$A$1:$N$8)*(COUNTIF($O12:O$12,$A$1:$N$8)=0),$A$1:$N$8),1)

 

[LuGyver]

Tiny typo in the formula, it should be:

=SMALL(IF(COUNTIF($A$10:$N$17,$A$1:$N$8)*(COUNTIF($O12:O$12,$A$1:$N$8)=0),$A$1:$N$8),1)

Perfect! You!
Merry Christmas!
Thank You so much. All the best to you and family

 

[Eric W]

Happy to help! Hope you have a Merry Christmas too!
?

 

[LuGyver]

Tiny typo in the formula, it should be:

=SMALL(IF(COUNTIF($A$10:$N$17,$A$1:$N$8)*(COUNTIF($O12:O$12,$A$1:$N$8)=0),$A$1:$N$8),1)

Getting #NUM! when extending/dragging to right and seems to be dogging somewhat with a bigger data sheet. Guessing it could be #NUM! issue.
Thank you!

2Arrays-Compare.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	285	364	289	917	538	153	247	487	110	958	196	887	914	280		285	364						487
2	89	626	686	995	672	908	270	772	226	315	26	722	11	196												26
3	522	845	488	737	139	853	780	318	965	73	264	339	563	821						139	853	780
4	125	689	424	133	158	662	439	250	429	889	9	605	646	69			689				662	439		429
5	33	407	421	692	823	981	246	745	935	285	668	1	613	47					692						285
6	642	292	517	256	814	580	240	956	822	272	340	784	90	672							580
7	672	838	318	426	796	534	927	877	524	193	215	745	922	321											193	215
8	274	804	154	789	624	119	36	1	666	588	393	54	953	222			804
9
10	687	352	662	580	378	805	454	418	286	285	591	786	733	603
11	904	95	115	709	837	492	304	553	688	235	374	983	783	675
12	579	849	211	139	439	769	692	225	853	138	536	586	636	273		26	139	193	215	285	364	429	439	487	580	662	689	692	780	804	853	#NUM!	#NUM!	#NUM!
13	419	88	319	137	477	815	920	48	512	639	71	322	551	870
14	570	269	364	487	535	988	699	888	113	756	278	609	714	804
15	944	215	781	344	418	193	395	819	660	116	248	26	10	31
16	409	135	295	162	634	229	874	344	405	429	452	176	443	212
17	859	596	808	689	780	791	916	971	78	618	944	990	39	120
Sheet1

Cell Formulas
Range	Formula
P1:AC8	P1	=IF(COUNTIF($A$10:$N$17,A1),A1,"")
P12:AH12	P12	=SMALL(IF(COUNTIF($A$10:$N$17,$A$1:$N$8)*(COUNTIF($O12:O$12,$A$1:$N$8)=0),$A$1:$N$8),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

I doubt that the #NUM is causing a lag. Anything I'd do to eliminate that would involve more processing. I believe it would depend on how big your ranges are. Every formula would involve the umber of elements in the first array times the number of elements in the second array, or a squared function. Squared functions grow rapidly. If you have big enough arrays, it might be worthwhile to write a macro that does this, which could be more efficient.

 

[LuGyver]

I doubt that the #NUM is causing a lag. Anything I'd do to eliminate that would involve more processing. I believe it would depend on how big your ranges are. Every formula would involve the umber of elements in the first array times the number of elements in the second array, or a squared function. Squared functions grow rapidly. If you have big enough arrays, it might be worthwhile to write a macro that does this, which could be more efficient.

That makes sense. I do have a max rows of 98 cells X 10, and figure that most definately would be the issue... Back to the drawing board. I have a macro that pushes everything down starting at row20 then copies it, plus one back into row 20 so I wonder if it could simply be added to it.
All the Best and Merry Christmas