How to match 3 columns in 2 different sheets

[honkin]

How is it possible to compare cells in 3 columns of 2 different sheets? The rows will not necessarily match up, but the column data will, so if A, B & C match in any row in the 2 sheets, then it is a yes.

Let me explain.

Column A is the name of the league
Column B is the date and time of the match
Column C is the name of the match, like Barcelona - Real Madrid

So you can see that although A or C might be the same over a span of some years, matching the date as well will mean that it is a particular match

What I am trying to do is to find out if matches appear in both sheets. The result is to go in 1 of those sheets called Lay Under 15_25_35, but the other sheet, which is in the same workbook, is called Lay Over 2.5.

So what I am after is IF A in the destination sheet = A in 'Lay Over 2.5'! AND IF B in the destination sheet = B in 'Lay Over 2.5'! AND IF C in the destination sheet = C in 'Lay Over 2.5'! then put "YES" otherwise "NO"

I tried writing it, but as the rows will not be the same, was struggling to get it right.

I tried the following, but to no avail

=IF(AND($A$2:$A2='Lay Over 2.5'!A2,$B$2:$B2='Lay Over 2.5'!B2,,$C$2:$C2='Lay Over 2.5'!C2),"YES","NO")

This returned NO on all occasion, even though there were some matches which appeared in both

Any suggestions on how to improve this so it puts YES if A, B & C match in any row between the sheets?

cheers

 

[Toadstool]

Cheers toadstool

That works really well, but for whatever reason, about 1/3 of the results come back as No. ...

On the data I provided? Yes, that's because there's no match for columns A, B and C. If it's on your data then either install XL2BB or copy and paste as text the cells from both sheets.

Changing the format of a cell doesn't change its content so the formula results will be unchanged.

 

[honkin]

On the data I provided? Yes, that's because there's no match for columns A, B and C. If it's on your data then either install XL2BB or copy and paste as text the cells from both sheets.

Changing the format of a cell doesn't change its content so the formula results will be unchanged.

Yes, as soon as I copied a cell in B from the source and pasted it as Values, the result turned to a price. A painful way to do it, but have no control over the sheets downloaded from the data supplier, so it will have to do

Thanks again

 

[honkin]

Just $AD2 should do it.

Cheers Jason. Apologies for the late reply. I missed your answer initially. No, that didn't work, but Toadstool came back with a response which helped. Ta!

 

[honkin]

On the data I provided? Yes, that's because there's no match for columns A, B and C. If it's on your data then either install XL2BB or copy and paste as text the cells from both sheets.

Changing the format of a cell doesn't change its content so the formula results will be unchanged.

OK, have finally installed Xl2bb to try and resolve this correctly as it is working in some cells and not working in others.

The source sheet is Predictology Result File and the columns in both source and target are the same; D, E, F, G, H, I, J & K

In all but H, the formula works a treat. I simple change the first column reference to the new column and all is good. Columns D and H in the source are scores, so they are presented like this; 1-0, 0-0, 1-2 etc I thought this may have been the culprit, but since D has no such issues, that seems unlikely.

So, initially the scores were input manually and often when typing 1-2, it might change the cell to 1 Feb instead of 1-2. I would simply highlight the cell and F1 and change the format to Custom and @ and then type 1-2 again and it would be fine.

So as you can see, the formula in H just sits there with the formula visible, rather than actually doing anything. Even a No would be a better response, as it would indicate no match in A, B & C, but doing nothing has me scratching me head

I hope something in there strikes you as not right, as it is doing my head in

cheers

Predictology_Trading Template v3.1.xlsm
	D	E	F	G	H	I	J	K
4	1-1	1	1	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	3	4
5	0-0	0	0	0	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	2	1	3
6	1-0	1	0	1	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	2	0	2
7	1-1	1	1	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	1	2
8	0-3	0	3	3	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	3	4
9	0-2	0	2	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	0	2	2
10	0-0	0	0	0	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	0	0	0
11	0-1	0	1	1	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	0	2	2
12	1-0	1	0	1	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	0	1
13	2-0	2	0	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	2	0	2
14	0-2	0	2	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	2	3
15	0-2	0	2	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	0	3	3
16	1-0	1	0	1	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	2	3
17	1-0	1	0	1	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	1	2
18	1-0	1	0	1	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	1	0	1
19	2-0	2	0	2	=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")	4	0	4
Lay Over 2.5

Cell Formulas
Range	Formula
D4:D19	D4	=IFERROR(INDEX('Predictology Result File'!$D$2:$D$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
E4:E19	E4	=IFERROR(INDEX('Predictology Result File'!$E$2:$E$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
F4:F19	F4	=IFERROR(INDEX('Predictology Result File'!$F$2:$F$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
G4:G19	G4	=IFERROR(INDEX('Predictology Result File'!$G$2:$G$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
I4:I19	I4	=IFERROR(INDEX('Predictology Result File'!$I$2:$I$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
J4:J19	J4	=IFERROR(INDEX('Predictology Result File'!$J$2:$J$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
K4:K19	K4	=IFERROR(INDEX('Predictology Result File'!$K$2:$K$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")

 

[Toadstool]

OK, have finally installed Xl2bb to try and resolve this correctly as it is working in some cells and not working in others.

The source sheet is Predictology Result File and the columns in both source and target are the same; D, E, F, G, H, I, J & K...

You're only showing a part of your calculation sheet and none of the other sheet so it's not possible to provide a complete solution.

Your column H was formatted as Text so didn't interpret what you entered as a formula. Format column H as General then delete all of H2 down contents. Put this in H2 and copy down:

=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")

 

[honkin]

You're only showing a part of your calculation sheet and none of the other sheet so it's not possible to provide a complete solution.

Your column H was formatted as Text so didn't interpret what you entered as a formula. Format column H as General then delete all of H2 down contents. Put this in H2 and copy down:

=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")

Cheers Toadstool and thanks for your reply.

Initially that row was formatted as Custom I believe, so I actually just tried a few different formats to see if I could get a result. I even tried General, but not with deleting the contents and then adding the formula. The formula was copied first, then I tried different formats.

That is working nicely now, so thanks very much

cheers

 

[honkin]

You obviously have vastly more experience in excel than I, so I wonder if I might pick pick your brain for a sec on the subject of the initial post about automating the copying process of results.

The sheet this went into is just 1 of about 10 in a central workbook. 1 sheet shows all the matches and their results and the subsequent sheets are all various systems, so only have the selections for that system. The aim is to try and reduce the workload by having all the results populate right through the workbook as soon as the results are pasted in, but the problem arises with things being deleted, like abandoned matches etc.

So a file is downloaded daily and I generally go a couple of days either side of today's date to catch those matches which might be very early morning GMT. The macros for each system are run on this downloaded file, with the result being copied into the central file and each of the relevant tabs. It is this sheet I have been working on, trying to in some way automate a way for the results across all sheets when a single paste of results occurs in D, E, F, G, H, I, J & K.

When things are deleted, like abandoned or postponed matches and the like, what is the best way to not lose the coding in those columns, or is it simply a matter of manually copying the code back into any cells which need it? I tried protecting each sheet and only leaving the match details and score columns unlocked, but I was then unable to delete those matches I referred to.

Is there any tried and tested method for setting up this kind of automated sheet?

Thanks in advance mate

 

[Toadstool]

I'm not sure I'm following you, examples are always best, but if I understand you're pasting columns of data into a sheet but that sheet also has columns of formulae.

If you lock the formulae columns then what's to stop you selecting the data columns, pressing the Delete key, then paste in the data columns?

 

[honkin]

I'm not sure I'm following you, examples are always best, but if I understand you're pasting columns of data into a sheet but that sheet also has columns of formulae.

If you lock the formulae columns then what's to stop you selecting the data columns, pressing the Delete key, then paste in the data columns?

Apologies for the delay, Toadstool.

This was the way I had already tried, but my issue with it is the amount of matches which are either postponed, cancelled or abandoned worldwide. Columns A, B & C are the League, Date/Time and Match details. The next 8 columns are the score details, Half Time Score, Half Time Home Goals, Half Time Away Goals, Half Time Total Goals, Full Time Score, Full Time Home Goals, Full Time Away Goals, Full Time Total Goals. The next half a dozen columns or so are other match stats which require no formula, with the last handful of columns handling a count of the number of matches, number of wins, strike rate and P&L. To lock the 8 score columns and the last few columns means that if matches are deleted, I am unable to select that row to delete the match. In fact I am unable to delete the row at all, even if I select allow user to delete rows.

I had hoped to make it a locked workbook, as you suggested, and be able to remove matches easily if required, but it seems the best way forward is to simply add the scores in the central sheet and when adding new matches to the individual model sheets, simply copy and paste to remove any matches which did not take place. So if a match in row 200 was postponed, I highlight from 201 down and copy and paste it to row 200. If I actually delete the row, I end up with the #REF! in each cell which had a formula, which would require copying and pasting the formula down again.

It's not perfect, but seems to work for now.

Thanks again for your help and suggestions