Copy and Paste Row from sheet to sheet if Cells Match

[Celticshadow]

Hi Forum

Would forum be kind enough to try and provide a solution for my formula problem in copying a row from one sheet to another dependent on a cell value matching.

I have two sheets one named Btn Fav and the other named Odds Tracker 1, if the cell I2 (containing horse name) in the Btn Fav sheet matches a cell in column B in the odds tracker sheet then I would like to copy the row from A to Q and paste it into the Btn Fav sheet starting at Col P through to Col AF. I have attached an example of what I would like to do as example. Many thanks for looking and hopefully providing a solution.

Regards

 

[TheMacroNoob]

Hey, I'm not totally sure where the data to the left of your blue arrow is coming from, but I have a formula that seems to do what you want?

Paste below in cell P2:

=FILTER('Odds Tracker 1'!A2:Q12,'Odds Tracker 1'!B2:B12=I2)

You can make a dropdown of the various horse names as a list in the cell you pointed out with the blue arrow, and the formula above will copy all data for the horse you select.

 

[Celticshadow]

Hey, I'm not totally sure where the data to the left of your blue arrow is coming from, but I have a formula that seems to do what you want?

Paste below in cell P2:


You can make a dropdown of the various horse names as a list in the cell you pointed out with the blue arrow, and the formula above will copy all data for the horse you select.

Hi TheMacroNoob

Many thanks for replying it is much appreciated. I have put your above formula into my sheet but it returns a #value! in the cell so I am not sure what I am doing wrong unless it has something to do with formatting.

Regards

 

[TheMacroNoob]

Hi TheMacroNoob

Many thanks for replying it is much appreciated. I have put your above formula into my sheet but it returns a #value! in the cell so I am not sure what I am doing wrong unless it has something to do with formatting.

Regards

Do you have the FILTER() function? When you start typing it in? If you do, then your sheet names might be slightly different?

See below for my cells and formulas: I added an index to the rows after the first if you preferred no spill array. Otherwise the formula should work if you have the correct cell references and sheet names

MrExcelHelp.xlsx
	A	B	C	D	E	F	G	H	I							P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Date	Time	Track	Race Type	Distance	Cls	Run	No	Horse							No	Horse	Jockey	Trainer	LR	FC	EARLY	0.38	0.42	0.47	0.51	0.53	Live Show	NOW	WIN %	Move %	Pos
2									Luigi							47	Luigi	81	63	50	66	81	20	84	52	72	38	63	85	45	88	11
3									Princess							25	Princess	4	17	96	66	66	40	32	8	87	82	73	81	31	7	43
4									Gin Coco							1	Gin Coco	Burke, Jonathan	Fry, Harry	2of25 1L 4.33	2.63	1.29	1.44	1.29	1.29	1.29	1.44	1.40 1.36 1.33 1.36 1.33	1.33	75.00%	13.79%	1st
Btn Fav Sheet

Cell Formulas
Range	Formula
P2:AF2	P2	=FILTER('Odds Tracker 1'!$A$2:$Q$12,'Odds Tracker 1'!$B$2:$B$12=$I2)
P3:AF4	P3	=INDEX(FILTER('Odds Tracker 1'!$A$2:$Q$12,'Odds Tracker 1'!$B$2:$B$12=$I3),COLUMNS($A$1:A$1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2:I4	List	='Odds Tracker 1'!$B$2:$B$12

Test Sheet:

MrExcelHelp.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	No	Horse	Jockey	Trainer	LR	FC	EARLY	0.38	0.42	0.47	0.51	0.53	Live Show	NOW	WIN %	Move %	Pos
2	1	Gin Coco	Burke, Jonathan	Fry, Harry	2of25 1L 4.33	2.63	1.29	1.44	1.29	1.29	1.29	1.44	1.40 1.36 1.33 1.36 1.33	1.33	0.75	0.1379	1st
3	10	Potato	50	74	23	17	43	64	81	88	17	44	72	47	22	47	40
4	20	Horsey	30	17	55	56	24	18	35	66	64	71	23	51	64	29	47
5	70	Wowzers	69	57	85	87	38	46	81	58	40	85	31	93	21	55	93
6	89	Mario	3	83	45	6	97	44	1	61	23	10	20	17	9	14	30
7	47	Luigi	81	63	50	66	81	20	84	52	72	38	63	85	45	88	11
8	25	Princess	4	17	96	66	66	40	32	8	87	82	73	81	31	7	43
9	74	Omega	55	76	24	4	27	57	98	17	100	19	35	78	22	71	100
10	33	Delta	55	93	2	10	42	10	76	87	21	51	43	72	18	43	23
11	87	Number	0	61	30	15	47	54	2	49	17	22	94	94	91	92	73
12	29	Manes	64	51	37	50	51	93	26	36	97	69	80	100	47	27	43
13
14
15
Odds Tracker 1

 

[Celticshadow]

Hi TheMacroNoob

Yes I do have the macro filter and the sheet names are correct so I am at a loss, although not having the knowledge that yourself and forum members have will more than likely mean I am missing something.

Regards

 

[TheMacroNoob]

Two alternatives in addition to the first formula (If you don't have FILTER():

MrExcelHelp.xlsx
	H	I							P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	No	Horse							No	Horse	Jockey	Trainer	LR	FC	EARLY	0.38	0.42	0.47	0.51	0.53	Live Show	NOW	WIN %	Move %	Pos
2		Gin Coco							1	Gin Coco	Burke, Jonathan	Fry, Harry	2of25 1L 4.33	2.63	1.29	1.44	1.29	1.29	1.29	1.44	1.40 1.36 1.33 1.36 1.33	1.33	75%	13.79%	1st
3
4
5		Gin Coco							1	Gin Coco	Burke, Jonathan	Fry, Harry	2of25 1L 4.33	2.63	1.29	1.44	1.29	1.29	1.29	1.44	1.40 1.36 1.33 1.36 1.33	1.33	75%	13.79%	1st
6
7
8		Gin Coco							1	Gin Coco	Burke, Jonathan	Fry, Harry	2of25 1L 4.33	2.63	1.29	1.44	1.29	1.29	1.29	1.44	1.40 1.36 1.33 1.36 1.33	1.33	75%	13.79%	1st
9
10
11
Btn Fav Sheet

Cell Formulas
Range	Formula
P2:AF2	P2	=FILTER('Odds Tracker 1'!$A$2:$Q$12,'Odds Tracker 1'!$B$2:$B$12=$I2)
P5:AF5	P5	=INDEX('Odds Tracker 1'!$A$2:$Q$12,MATCH($I5,'Odds Tracker 1'!$B$2:$B$12,0),0)
P8:AF8	P8	=XLOOKUP($I8,'Odds Tracker 1'!B2:B12,'Odds Tracker 1'!A2:Q12)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2:I5	List	='Odds Tracker 1'!$B$2:$B$12
I8	List	='Odds Tracker 1'!$B$2:$B$12

 

[TheMacroNoob]

Hi TheMacroNoob

Yes I do have the macro filter and the sheet names are correct so I am at a loss, although not having the knowledge that yourself and forum members have will more than likely mean I am missing something.

Regards

Hmm, if you have =FILTER() and it shows as you type, then the cell references need to be checked, the sheet names, or you have a typo in the horse name cell (i2).

Is there any way for you to post your sheet as it stands?

 

[Celticshadow]

Hi TheMacroNoob

Is it ok to post to google docs as I have tried to post a table before but I was unable to thats why I put up images?

Regards

 

[Celticshadow]

Hmm, if you have =FILTER() and it shows as you type, then the cell references need to be checked, the sheet names, or you have a typo in the horse name cell (i2).

Is there any way for you to post your sheet as it stands?

Hi TheMacroNoob

I have attached an image of the Odds Tracker sheet which shows all the days results within it. Thus I think the problem is ( my fault for not posting a complete query I suppose) that I want to search that column B from B2 through to B900 in the Odds Tracker sheet for the horse in the Btn Fav sheet and when a match is found then copy that row over. Hope that makes sense.

Regards

 

[TheMacroNoob]

I think it's because I did not absolute reference the formula at the beginning:

=FILTER('Odds Tracker 1'!$A$2:$Q$12,'Odds Tracker 1'!$B$2:$B$12=$I2)

You can either copy the formula above and see if that works once you drag the autofill down, or press F4 on your various cell references to absolute reference the ranges on Odds Tracker 1. The criteria, i2, does not need to be changed. Apologies for the confusion, I thought you were getting an error on everything!