"Matching 2 numbers in tables together"

[marwan1]

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.

1424771		1424782
1424772		1424786
1424773		1424796
1424774		1424799
1424775		1424876
1424775		1424881
1424776		1424885
1424779		1424886
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.

 

[marwan1]

Thanks Markmzz. I'll try it and let you know!
Cheers

 

[markmzz]

That's incredible. I've never used that tool. Thanks for posting.

You're welcome. Advanced Filter - Calculated Criteria is an Excel feature that many users don't know.

Markmzz

 

[markmzz]

Thanks Markmzz. I'll try it and let you know!
Cheers

You're welcome.

Do some tests and tell me if works.

Markmzz

 

[marwan1]

Dear markmzz, I've done the folloowing formulas according to yours and I got this:

Sheet1

	H	I	J	K	L
2			Yellow	Light green	Green
3	10	40	TRUE	FALSE	FALSE
4	1	50	FALSE	TRUE	TRUE
5	40	11	FALSE	FALSE	TRUE
6	20	15	FALSE	FALSE	TRUE
7	20	55	FALSE	FALSE	TRUE
8	10	86	FALSE	FALSE	TRUE
9	10	10	TRUE	FALSE	FALSE
10	40	20	TRUE	FALSE	FALSE

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"> </COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula J3 =ISNUMBER(VLOOKUP($I3,$H:$H,1,0)) K3 =ISERROR(VLOOKUP($H3,$I:$I,1,0)) L3 =ISERROR(VLOOKUP($I3,$H:$H,1,0)) J4 =ISNUMBER(VLOOKUP($I4,$H:$H,1,0)) K4 =ISERROR(VLOOKUP($H4,$I:$I,1,0)) L4 =ISERROR(VLOOKUP($I4,$H:$H,1,0)) J5 =ISNUMBER(VLOOKUP($I5,$H:$H,1,0)) K5 =ISERROR(VLOOKUP($H5,$I:$I,1,0)) L5 =ISERROR(VLOOKUP($I5,$H:$H,1,0)) J6 =ISNUMBER(VLOOKUP($I6,$H:$H,1,0)) K6 =ISERROR(VLOOKUP($H6,$I:$I,1,0)) L6 =ISERROR(VLOOKUP($I6,$H:$H,1,0)) J7 =ISNUMBER(VLOOKUP($I7,$H:$H,1,0)) K7 =ISERROR(VLOOKUP($H7,$I:$I,1,0)) L7 =ISERROR(VLOOKUP($I7,$H:$H,1,0)) J8 =ISNUMBER(VLOOKUP($I8,$H:$H,1,0)) K8 =ISERROR(VLOOKUP($H8,$I:$I,1,0)) L8 =ISERROR(VLOOKUP($I8,$H:$H,1,0)) J9 =ISNUMBER(VLOOKUP($I9,$H:$H,1,0)) K9 =ISERROR(VLOOKUP($H9,$I:$I,1,0)) L9 =ISERROR(VLOOKUP($I9,$H:$H,1,0)) J10 =ISNUMBER(VLOOKUP($I10,$H:$H,1,0)) K10 =ISERROR(VLOOKUP($H10,$I:$I,1,0)) L10 =ISERROR(VLOOKUP($I10,$H:$H,1,0)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Apart from the above what i really wanted is matching all numbers on both columns with each other(including repeated numbers). Can your formulas help me fulfill my goal?

 

[markmzz]

Dear markmzz, I've done the folloowing formulas according to yours and I got this:


Apart from the above what i really wanted is matching all numbers on both columns with each other(including repeated numbers). Can your formulas help me fulfill my goal?

Maybe this (with Advanced Filter - Calculated Criteria):

Layout

**
Data01	Data02	Yellow	Light green	Green		Matched #		No matched #		No matched #
10	40	VERDADEIRO	FALSO	FALSO		Data02		Data02		Data01
1	50					40		50		1
40	11					10		11
20	15					20		15
20	55							55
10	86							86
10	10
40	20
*******	*******	************	************	************	**	************	**	************	**	************

<tbody>
</tbody>

Formulas

In J3

=ISNUMBER(VLOOKUP($I3,$H:$H,1,0))

In K3

=ISERROR(VLOOKUP($I3,$H:$H,1,0))

In L3

=ISERROR(VLOOKUP($H3,$I:$I,1,0))

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

 

[marwan1]

Thanks a lot, I got it now. I was little confused for the similar numbers in Data01 but that's alright if we make Data02 the base column. Similarily we can start from Data01 as a base.


Thanks indeed, you made my day!

 

[markmzz]

Thanks a lot, I got it now. I was little confused for the similar numbers in Data01 but that's alright if we make Data02 the base column. Similarily we can start from Data01 as a base.


Thanks indeed, you made my day!

You're welcome and thanks for the feedback.

Markmzz