Compare number strings

Havouza15

New Member
Joined
Apr 19, 2018
Messages
4
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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().
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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