Need help Matching numbers

[vatsk]

I have a very large file with two columns with about 40,000 rows, is there an excel formula to match the same numbers in column A to the same numbers in column B and all the other non matching numbers to be pushed to the bottom of the columns?? please help me

 

[SteveO59L]

Use vlookup, something like

=if(iserror(vlookup(a1,b:b,1,false)),100,1)


will populate with 1 where the number in a1 is in col b, and with 100 if not, then sort on this data

 

[vatsk]

Thanks, but is that get me all the matchs or just do i have to but one at a time in??? thanks

 

[SteveO59L]

Just fill the formula down

 

[vatsk]

Thanks, that helped alot, is there any way i could get the matching numbers side by side without searching for it after i know where is a match, because looking through 40,000 items to find one match is alot of work, thanks

 

[vatsk]

can anyone help me??? please

 

[yomero]

Can't really understand what you are trying to do. It would be helpful for you to provide a detailed description and screen shots, se we can help you.

 

[jim may]

Maybe it's a starting point, maybe not,, But I worked on this awhile ealier today.

Col A & B (after header row) are 15 numbers

I created Col C & D to cross-reference whether the Larter numbers
were there or not, maintaining the numbers or making 0 if too Low.

Col E & F are meant to sort High to Low...

Excel Workbook
	A	B	C	D	E	F
1	a	b	c	d	e	f
2	10	70	10	70	90	90
3	20	1	20	0	80	80
4	55	2	0	0	70	70
5	30	10	30	10	60	60
6	40	80	40	80	50	50
7	50	3	50	0	40	40
8	44	90	0	90	30	30
9	60	20	60	20	20	20
10	33	4	0	0	10	10
11	70	30	70	30	0	0
12	80	5	80	0	0	0
13	22	40	0	40	0	0
14	90	6	90	0	0	0
15	11	50	0	50	0	0
16	12	60	0	60	0	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C2	=IF(COUNTIF($B$2:$B$16,A2),A2,0)
C3	=IF(COUNTIF($B$2:$B$16,A3),A3,0)
C4	=IF(COUNTIF($B$2:$B$16,A4),A4,0)
C5	=IF(COUNTIF($B$2:$B$16,A5),A5,0)
C6	=IF(COUNTIF($B$2:$B$16,A6),A6,0)
C7	=IF(COUNTIF($B$2:$B$16,A7),A7,0)
C8	=IF(COUNTIF($B$2:$B$16,A8),A8,0)
C9	=IF(COUNTIF($B$2:$B$16,A9),A9,0)
C10	=IF(COUNTIF($B$2:$B$16,A10),A10,0)
C11	=IF(COUNTIF($B$2:$B$16,A11),A11,0)
C12	=IF(COUNTIF($B$2:$B$16,A12),A12,0)
C13	=IF(COUNTIF($B$2:$B$16,A13),A13,0)
C14	=IF(COUNTIF($B$2:$B$16,A14),A14,0)
C15	=IF(COUNTIF($B$2:$B$16,A15),A15,0)
C16	=IF(COUNTIF($B$2:$B$16,A16),A16,0)
D2	=IF(COUNTIF($A$2:$A$16,B2),B2,0)
D3	=IF(COUNTIF($A$2:$A$16,B3),B3,0)
D4	=IF(COUNTIF($A$2:$A$16,B4),B4,0)
D5	=IF(COUNTIF($A$2:$A$16,B5),B5,0)
D6	=IF(COUNTIF($A$2:$A$16,B6),B6,0)
D7	=IF(COUNTIF($A$2:$A$16,B7),B7,0)
D8	=IF(COUNTIF($A$2:$A$16,B8),B8,0)
D9	=IF(COUNTIF($A$2:$A$16,B9),B9,0)
D10	=IF(COUNTIF($A$2:$A$16,B10),B10,0)
D11	=IF(COUNTIF($A$2:$A$16,B11),B11,0)
D12	=IF(COUNTIF($A$2:$A$16,B12),B12,0)
D13	=IF(COUNTIF($A$2:$A$16,B13),B13,0)
D14	=IF(COUNTIF($A$2:$A$16,B14),B14,0)
D15	=IF(COUNTIF($A$2:$A$16,B15),B15,0)
D16	=IF(COUNTIF($A$2:$A$16,B16),B16,0)
E2	=LARGE($D$2:$D$16,ROW()-1)
E3	=LARGE($D$2:$D$16,ROW()-1)
E4	=LARGE($D$2:$D$16,ROW()-1)
E5	=LARGE($D$2:$D$16,ROW()-1)
E6	=LARGE($D$2:$D$16,ROW()-1)
E7	=LARGE($D$2:$D$16,ROW()-1)
E8	=LARGE($D$2:$D$16,ROW()-1)
E9	=LARGE($D$2:$D$16,ROW()-1)
E10	=LARGE($D$2:$D$16,ROW()-1)
E11	=LARGE($D$2:$D$16,ROW()-1)
E12	=LARGE($D$2:$D$16,ROW()-1)
E13	=LARGE($D$2:$D$16,ROW()-1)
E14	=LARGE($D$2:$D$16,ROW()-1)
E15	=LARGE($D$2:$D$16,ROW()-1)
E16	=LARGE($D$2:$D$16,ROW()-1)
F2	=LARGE($C$2:$C$16,ROW()-1)
F3	=LARGE($C$2:$C$16,ROW()-1)
F4	=LARGE($C$2:$C$16,ROW()-1)
F5	=LARGE($C$2:$C$16,ROW()-1)
F6	=LARGE($C$2:$C$16,ROW()-1)
F7	=LARGE($C$2:$C$16,ROW()-1)
F8	=LARGE($C$2:$C$16,ROW()-1)
F9	=LARGE($C$2:$C$16,ROW()-1)
F10	=LARGE($C$2:$C$16,ROW()-1)
F11	=LARGE($C$2:$C$16,ROW()-1)
F12	=LARGE($C$2:$C$16,ROW()-1)
F13	=LARGE($C$2:$C$16,ROW()-1)
F14	=LARGE($C$2:$C$16,ROW()-1)
F15	=LARGE($C$2:$C$16,ROW()-1)
F16	=LARGE($C$2:$C$16,ROW()-1)

 

[alanjp]

Vatsk

Did you do as Steve059L suggested, that is, perform a sort.

I would sort on column 3 (the resultant from the formulae) and column 1.

You could then highlight all three (3) colums and use Subtotally to provide you with a count.

Menu option <Data> <Subtotal>

Alan

 

[alanjp]

Vatsk

Sorry the menu option was removed because I use symbols to enclose option.

Menu Data Subtotal