"Matching 2 numbers in tables together"

M

marwan1

Board Regular
Joined
Mar 2, 2011
Messages
145
Hello every one,

I have two tables. One has long list of numbers, and the other one has lesser. I'd like to match the identical numbers in both tables. By the way the first table has 14600 cells and the short one has 1466 cells.


14247711424782
14247721424786
14247731424796
14247741424799
14247751424876
14247751424881
14247761424885
14247791424886
1424780
1424781
1424782
1424783
1424784
1424785
1424785
1424786
1424787
1424787
1424788
1424788
1424789
1424789
1424790
1424790
1424791
1424791
1424792
1424792
1424793
1424793
1424794
1424794
1424795
1424795
1424796
1424796
1424797
1424797
1424798
1424799
1424799
1424800
1424800
1424801
1424801
1424802
1424803
1424804
1424805
1424806
1424807
1424808
1424809
1424810
1424811
1424812
1424813
1424814
1424815
1424816
1424817
1424818
1424819
1424820
1424821
1424822
1424823
1424824
1424825
1424826
1424827
1424828
1424829
1424830
1424831
1424831
1424832
1424832
1424833
1424833
1424834
1424835
1424836
1424837
1424838
1424839
1424840
1424841
1424842
1424842
1424843
1424843
1424844
1424845
1424847
1424848
1424849
1424850
1424851
1424852
1424853
1424854
1424855
1424856
1424857
1424858
1424859
1424860
1424862
1424863
1424864
1424865
1424866
1424867
1424867
1424868
1424869
1424876
1424880
1424881
1424882
1424883
1424884
1424885
1424886

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

Thanks for your help.
 
Sort by date Sort by votes
Sorry to bring this up again guys but appeared that I'm not getting exactly what I thought. When I built in this formula =(MATCH(A2,$B$2:$B$1209,0)) in the conditional formating I got the below results:


A
B
2
1410000
1410000
3
1410000
1410000
4
1410000
1410000
5
1410000
1410000
6
1410000
1410000
7
1410000
1410000
8
1410000
1410000
9
1410000
1410000
10
1410000
1410000
11
1410000
7990000
12
1410000
13
1410000
14
1410000
15
1410000
16
1410000
17
7990000
18
7990000
19
7990000
20
7990000
21
7990000
22
7990000
23
7990000
24
7990000
25
7990000
26
7990000
27
7990000
28
7990000
29
7990000
30
7990000
31
7990000
32
7990000
33
7990000
34
7990000
35
7990000
36
7990000

<TBODY>
</TBODY>

I got the followimg:

1- All data in column B was hilighted red.
2- Dulicate numbers in column B such as 1410000 totaled 9 were matched with 15's same numbers in column A! Which is Ok.
3- which is weird, the last number in column (B) 7990000 was not matched with any of the same numbers in column A?! Unlike point#2.

Thanks for your help again!
 
Last edited:
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try highlighting column A starting from the first cell going down, and using this formula in conditional formatting:

=ISNUMBER(MATCH(B3,$C$3:$C$12,0))

Then highlight column B starting from the first cell going down, and use this formula in conditional formatting:

=ISNUMBER(MATCH(C3,$B$3:$B$38,0))
 
Upvote 0
Correction to previous post:

Formula for conditional formatting of column A:

=ISNUMBER(MATCH(A2,$B$2:$B$1500,0))

Formula for conditional formatting of column B:

=ISNUMBER(MATCH(B2,$A$2:$A$15000,0))
 
Upvote 0
Hi Marwan1,

Try this:

1) Select the entire column A and put this formula in CF

=VLOOKUP($A1,$B:$B,1,0)

Finally, set your custom color

2) Select the entire column B and put this formula in the CF

=VLOOKUP($B1,$A:$A,1,0)

Finally, set your custom color

Markmzz
 
Upvote 0
Thanks guys so much. Thanks bschwartz so much you formula just worked fine! Thanks also markmzz your formula did make a difference!


One last thing I was trying to filter these numbers by colour to show the matched numbers, but appeared to me that the filtering doesn't show the recurring numbers in both sides.
Is there a way to present my work any better?
 
Upvote 0
marwan1 said:
Thanks guys so much. Thanks bschwartz so much you formula just worked fine! Thanks also markmzz your formula did make a difference!


One last thing I was trying to filter these numbers by colour to show the matched numbers, but appeared to me that the filtering doesn't show the recurring numbers in both sides.
Is there a way to present my work any better?
Click to expand...

Sorry, but i don't know how to do that with Filter.

Maybe another user. Lets wait.

Markmzz
 
Upvote 0
If you put the same formula in a neighboring column =ISNUMBER(MATCH(A2,$B$2:$B$1500,0)) you will get a TRUE when it matched and a FALSE if it does not. You can then filter both columns together by the TRUE values of the second column.
 
Upvote 0
That's true I can do that but I'd like to use the filter function to show the matched and non matched ones.

Thanks gent's any way!
 
Upvote 0
marwan1 said:
That's true I can do that but I'd like to use the filter function to show the matched and non matched ones.

Thanks gent's any way!
Click to expand...

Maybe this:

Layout

Data01 Data02 Criteria Matched # Criteria No matched #2 Criteria No matched #1 Matched # No matched #No matched #
1424771 1424782 VERDADEIRO FALSO VERDADEIRO Data02 Data02Data01
1424772 1424786 1424782 14247961424771
1424773 1424796 1424786 14247991424772
1424774 1424799 1425067 14248761424773
1424775 1424876 14248811424774
1424775 1424881 14248851424775
1424776 1424885 14248861424776
1424779 1424886 14249191424779
1424780 1424919 14249381424780
1424781 1424938 14249561424781
1424782 1424956 14249751424783
1424783 1424975 14249931424784
1424784 1424993 14250121424785
1424785 1425012 14250301424787
1424785 1425030 14250491424788
1424786 1425049 1424789
1425067 1425067
1424787
1424788
1424788
1424789
*****************************************************************************************************************************

<colgroup><col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" span="2"> <tbody>
</tbody>


Advanced Filter - Calculated Criteria

Code: 
E2-> =ISNUMBER(VLOOKUP($C2,$A:$A,1,0))

G2-> =ISERROR(VLOOKUP($A2,$C:$C,1,0))

I2-> =ISERROR(VLOOKUP($C2,$A:$A,1,0))

PS: VERDADEIRO = TRUE and FALSO = FALSE


More information about Advancced Filter:
Advanced Filter in Excel - Easy Excel Tutorial
Atlas: Excel Training | Testing | Consulting
Filter by using advanced criteria - Excel - Office.com

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,222
Messages
6,123,706
Members
449,118
Latest member
MichealRed

Share this page

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
Back
Top