Compare number strings

[Havouza15]

I have an excel sheet with about 10000 rows of numbers. 10 different numbers, all integers between 1 and 99

Every day I get a 10 numbers that should be compared to the existing rows and a result written out in new cell after each row how many matches there are. Is this possible?

I am new to advanced excel so any help is highly appreciated

Regards

Anders Yuran

 

[My Aswer Is This]

What column are these numbers in?
Where is the list of 10 numbers we want to search for?

Not sure what this means:

and a result written out in new cell after each row​

 

[Havouza15]

What column are these numbers in?
Where is the list of 10 numbers we want to search for?

Not sure what this means:

and a result written out in new cell after each row​

One number in each cell A-J

Could the result be written after each row in f.ex K

Regards

Anders Yuran

 

[pgc01]

Hi Anders

It is possible to use formula that calculates the result directly, for ex.: countifs()

Another option, lighter and very simple is to use an auxiliary column.

Concatenate the rows for ex. in column L and then you get your result just with a countif().

 

[Havouza15]

Hi Anders

It is possible to use formula that calculates the result directly, for ex.: countifs()

Another option, lighter and very simple is to use an auxiliary column.

Concatenate the rows for ex. in column L and then you get your result just with a countif().

OK. Its new for me but If I use the countif f.ex =COUNTIF(B8:G8,21). This will check one number of the ten. I tried to use a range also for the criteria but that does not work. How can I use Countif to check all numbers in the criteriarange against the range of existing numbers

Anders

 

[pgc01]

Hi

For the first case, compare directly the 10 numbers in the row to all the other sets of numbers in the other rows, I said COUNTIFS(), not COUNTIF().

Assuming your numbers in A1:J10000, use in K1:

=COUNTIFS($A$1:$A$10000,A1,$B$1:$B$10000,B1,$C$1:$C$10000,C1,$D$1:$D$10000,D1,$E$1:$E$10000,E1,$F$1:$F$10000,F1,$G$1:$G$10000,G1,$H$1:$H$10000,H1,$I$1:$I$10000,I1,$J$1:J$10000,J1)

Copy down till K10000

 

[Havouza15]

Hi

For the first case, compare directly the 10 numbers in the row to all the other sets of numbers in the other rows, I said COUNTIFS(), not COUNTIF().

Assuming your numbers in A1:J10000, use in K1:

=COUNTIFS($A$1:$A$10000,A1,$B$1:$B$10000,B1,$C$1:$C$10000,C1,$D$1:$D$10000,D1,$E$1:$E$10000,E1,$F$1:$F$10000,F1,$G$1:$G$10000,G1,$H$1:$H$10000,H1,$I$1:$I$10000,I1,$J$1:J$10000,J1)

Copy down till K10000

Solved it!

MANY THANKS!

Anders

 

[pgc01]

The other option, with the auxiliary column, could be

In L1:

=TEXTJOIN("_",FALSE,A1:J1)

Copy down till L10000

The result, in K1:

=COUNTIF($L$1:$L$10000;L1)

Copy down till K10000

 

[pgc01]

You're welcome.

If in the future you need to use more rows, like hundreds of thousands, you may want to try the option with the auxiliary column, which may be faster to calculate.