Finding more than one occurrence

[John Sio]

I have attached a mockup of the below requirement. I hope someone can assist!

I need to be notified (in column G and H) when there is a second and third occurrence of the same team (located in row J and K). A notification of the value 2 for the second occurrence, 3 for the third.

The following condition must also be met, i.e. values in rows of columns B,C,D,E,F must be equal.

In the mockup you will see that all values from B5-F5 down to B18-F18 are equal. So, within this range, you can check from J5-J18 and K5-K18 how many times a team exists. You need to check both J and K for the same team and must start at J5-J18 followed by K5-K18.

For example J5 = Reading. By checking J5-J18 Reading only exists once. But checking also K5-K18, you will find Reading in K18. So, I would like you to place a 2 in H18. Same applies for other teams in J and K. Any second, third, fourth occurrence found “in” column J should have the notification in column G on the row the occurrence was found. Any second, third, fourth occurrence found “in” column K should have the notification in column H on the row the occurrence was found.

Note I used fill colour in the mockup to highlight the some examples. Colour is not a requirement.
Also note that the real excel I need to run this in has close to 700k rows of data.

Thank you

[windknife]

Try this in G2 and copied down and across.

Please Login or Register  to view this content.

[Estevaoba]

Edited.

I realized I was not taking both columns J & K at once.

[protonLeah]

G2:

Please Login or Register  to view this content.

h2:

Please Login or Register  to view this content.

[HansDouwe]

Please try in G2 and copy down and to the right:

Please Login or Register  to view this content.

[John Sio]

G2:

Please Login or Register  to view this content.

h2:

Please Login or Register  to view this content.

Thanks Ben, works well apart from one part. It doesn't seem to consider always the condition "B,C,D,E,F must be equal".
For example, in the range B19-F19 down to B21-F21 there is one difference in E20 but it still placed a '2' in G20.

[John Sio]

Please try in G2 and copy down and to the right:

Please Login or Register  to view this content.

Thanks Hans. It doesn't seem to consider always the condition "B,C,D,E,F must be equal".
For example,there is a '2' in G19 for Chester but D16 and E16 are different to D19 and E19

[John Sio]

Try this in G2 and copied down and across.

Please Login or Register  to view this content.

It hasn't found the 2 that should be in G4 and the 3 that should be in G14

[windknife]

Amended formula in G2 copied down and across.

Please Login or Register  to view this content.

[John Sio]

Dearly appreciated.. I will try it in the big sheet that i have and let you know if i come across something that doesn't match. Thanks again

[John Sio]

Amended formula in G2 copied down and across.

Please Login or Register  to view this content.

The formula seems to work but I am having extreme difficulties running it on my sheet (800,000 rows). I have tried running it on smaller chunks of 20k-50k rows but still not much success.
I notice that it keeps a lookup of all columns from B to F even once it has processed them. This is what's maybe causing the delay?
These groups of information (i.e. same info in B to F) only appear once in blocks of usually 10 rows and a max of 50 rows. So, if they are processed there is no need to keep looking for them. I also have my sheet sorted by B to F to make sure they don't appear elsewhere.
Is it possible the formula can be modified to take this into account so that it doesn't keep a stranglehold on the entire sheet?

[windknife]

Perhaps, you must use VBA to solve it for big data.

[John Sio]

Unfortunately, I have no idea about VBA and coding.

[windknife]

How about this with a helper column?

G2
=B2&C2&D2&E2&F2

H2
=IF($B2="","",IFERROR(1/(1/(COUNTIFS($G$1:G1,G2,$K$1:K1,K2)+COUNTIFS($G$1:G1,G2,$L$1:L1,K2)))+1,""))

I2
=IF($B2="","",IFERROR(1/(1/(COUNTIFS($G$1:G1,G2,$K$1:K1,K2)+COUNTIFS($G$1:G1,G2,$L$1:L1,K2)))+1,""))

copied down.

[John Sio]

@windknife - seems to be working brilliantly. I will finish it all off and check it and close the thread.
Brilliant thinking on your behalf! Wish I could give you more credit for this but I gotta spread the love around.

[bebo021999]

Do not employ helper column, but not sure it is OK for, 800000 rows, but try:

G3:

Please Login or Register  to view this content.

copy to H3 and down

[John Sio]

How about this with a helper column?

G2
=B2&C2&D2&E2&F2

H2
=IF($B2="","",IFERROR(1/(1/(COUNTIFS($G$1:G1,G2,$K$1:K1,K2)+COUNTIFS($G$1:G1,G2,$L$1:L1,K2)))+1,""))

I2
=IF($B2="","",IFERROR(1/(1/(COUNTIFS($G$1:G1,G2,$K$1:K1,K2)+COUNTIFS($G$1:G1,G2,$L$1:L1,K2)))+1,""))

copied down.

@windknife
I ran your formulas and indeed they were extremely quick for large volumes of data.
However, I can't understand why I have incorrect results now compared to the initial mockup where the results were correct.
I have attached an example and can't understand why there is a date in H21 instead of a '2' and why there is a '2' in I21 instead of null.
Can you please take a look? Thanks John

[windknife]

I have attached an example and can't understand why there is a date in H21 instead of a '2' and why there is a '2' in I21 instead of null.

1. H column's format is date, change it to Number.
2. I type wrong formula in I2.

I2
=IF($B2="","",IFERROR(1/(1/(COUNTIFS($G$1:G1,G2,$K$1:K1,L2)+COUNTIFS($G$1:G1,G2,$L$1:L1,L2)))+1,""))

[John Sio]

@windknife thank you, at the end of the day I had to create 8 different excel sheets, with 100k per sheet in order for it to complete successfully.
Just wondering if you have any good ideas about how I can keep the values instead of the formulas in G, H and I?
I have tried copy / paste special / values but it takes an eternity.
Let me know if there is a quicker way to do it please.
Thanks, John

[windknife]

How about use Hot-Key ?

1. Ctrl+V (Copy the cells)
2. Shift+F10
3. V (Paste as Values)

[John Sio]

@windknife
No unfortunately same result. If you come across something else, pls let me know.

[JohnTopley]

Only VBA if want to avoid "key" actions as you have to select the cells for copy/paste.

[John Sio]

Ok, thanks John

[JohnTopley]

You can copy/paste whole column rather individual cells so not too onerous!

[John Sio]

Yes, i have done that but it brings the computer to a standstill.
So, with VBA, I would need someone to write the code for me and then I run it via a macro?

[JohnTopley]

Try .. converts columns H:I to values

Please Login or Register  to view this content.

Select a sheet and run the macro. Repeat for all sheets.

Ensure you have a COPY of your "master" file!

[John Sio]

@JohnTopley
Once again Mr Topley to the rescue. I will try it out John and take all your instructions into consideration. Will let you know the outcome. Thanks