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

LuGyver

Board Regular
Joined
Mar 13, 2014
Messages
83
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1285364289917538153247487110958196887914280285364     487      
2896266869956729082707722263152672211196          26   
352284548873713985378031896573264339563821    139853780       
4125689424133158662439250429889960564669 689   662439 429     
533407421692823981246745935285668161347   692     285    
664229251725681458024095682227234078490672     580        
7672838318426796534927877524193215745922321         193215   
827480415478962411936166658839354953222 804            
9
10687352662580378805454418286285591786733603
1190495115709837492304553688235374983783675
12579849211139439769692225853138536586636273026139193215285364429439487580662689692780804853  
13419883191374778159204851263971322551870
14570269364487535988699888113756278609714804
15944215781344418193395819660116248261031
16409135295162634229874344405429452176443212
178595968086897807919169717861894499039120
18
Sheet1
Cell Formulas
RangeFormula
P1:AC8P1=IF(COUNTIF($A$10:$N$17,A1),A1,"")
P12P12=SMALL($P$1:$AC$8,1)
Q12:AG12Q12=IFERROR(SMALL($P1:$AC8,SUMPRODUCT(($P1:$AC8>0)*($P1:$AC8<=P12))+1),"")
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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
PQRSTUVWXYZAAABACADAEAFAG
1226139193215285364429439487580662689692780804853#NUM!#NUM!
Sheet6
Cell Formulas
RangeFormula
P12:AG12P12=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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

Board Regular
Joined
Mar 13, 2014
Messages
83
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640

ADVERTISEMENT

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

LuGyver

Board Regular
Joined
Mar 13, 2014
Messages
83
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1285364289917538153247487110958196887914280285364     487      
2896266869956729082707722263152672211196          26   
352284548873713985378031896573264339563821    139853780       
4125689424133158662439250429889960564669 689   662439 429     
533407421692823981246745935285668161347   692     285    
664229251725681458024095682227234078490672     580        
7672838318426796534927877524193215745922321         193215   
827480415478962411936166658839354953222 804            
9
10687352662580378805454418286285591786733603
1190495115709837492304553688235374983783675
1257984921113943976969222585313853658663627326139193215285364429439487580662689692780804853#NUM!#NUM!#NUM!
13419883191374778159204851263971322551870
14570269364487535988699888113756278609714804
15944215781344418193395819660116248261031
16409135295162634229874344405429452176443212
178595968086897807919169717861894499039120
Sheet1
Cell Formulas
RangeFormula
P1:AC8P1=IF(COUNTIF($A$10:$N$17,A1),A1,"")
P12:AH12P12=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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

Board Regular
Joined
Mar 13, 2014
Messages
83
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top