Copying to another sheet and missing out lines

[rireland]

My main sheet has the results of a competition in rows 1-100. 75% of the results won't count for the jumpoff ( its a horsey competition) and I want to automatically copy the successful details to another sheet. I've worked out how to to omit the unsuccessful results by using an IF statement ( IF cellx is > x then copy ) but I still end up with 100 rows - with 25 results and 75 blank rows. How can I copy but compact it down to 25 rows.

As a second question I need to sort the results. I know how to do a manual sort & I even worked out a macro so one keystroke can do the sort. It woruld be better if the sort would be automatic whenever there is a change in the scores.

 

[Sal Paradise]

Post the sort code and your copying code and we can combine the two to give you 25 sorted rows in one macro.

 

[rireland]

I've put the "input" and the "output" on one sheet to make it simpler to read. I want the "output" - the bottom bit - to be in place order and without the blank lines. Ideally to automatically change as results come in.

score test 1.xls
	B	C	D	E	F	G	H	I	J	K
1		GRADE3JUNIOR	tosuppressplacingsinsert"X"inyellowbox				DRESS.	SHOWJUMPING
2	No	HORSE	RIDER	PENALTY	TEAM/CLUB	FINALPLACE	SCORE	JUMP	TIME	TOTALFAULT
3	30	KarshnaCrystalPrincess	OliviaWhatley		DDPC	4	55.2	0	0.0	0.0
4	31	AlexanderBeetle	TaylorClark		WTPC		58.8	0	0.0	0.0
5	32	FernAyreSaintNic	HayleyAtkins		BDPC	1	49.2	0	0.0	0.0
6	33	Ollie	TaylorClark		WTPC		43.8	r		R
7	34	Chevarna	KathrynFairbairn		WTPC	3	49.2	0	0.0	0.0
8	35	Fife	AshtonBrown		GTPC		63.6	R		R
9	36	B.P.ChristmasMint	DeanneEarley		BDPC		33.6			0.0
10	37	Rabbit	PaigeLudbey		GTPC					0.0
11	38	PineRowSandStorm	MorganGrubb		LPRC	2	49.2	4	0.0	4.0
12
13	30	KarshnaCrystalPrincess	OliviaWhatley	0	DDPC	4	55.2	0	0	0
14
15	32	FernAyreSaintNic	HayleyAtkins	0	BDPC	1	49.2	0	0	0
16
17	34	Chevarna	KathrynFairbairn	0	WTPC	3	49.2	0	0	0
18
19
20
21	38	PineRowSandStorm	MorganGrubb	0	LPRC	2	49.2	4	0	4
GRADE3 JUNIOR

 

[Sal Paradise]

Could you please give us your code for creating the output? That will make it a lot easier.

 

[rireland]

=IF($G3="","",B3) is in cell B3 and all the way across to O3. Then down to B11. If G3 is blank it means they don't figure in the jump off. So all the records that are not blank in
G3 are copied. Then I do a manual sort using Data, sort , column G. Hope this helps.
R

 

[Sal Paradise]

Oh!

I see what you're doing now. Boy I feel dim.

Try this in your output sheet:
=INDEX(InputSheetName!$A:$K,MATCH(ROW(),InputSheetName!$H:$H,0),COLUMN())

Change InputSheetName! for whatever your input sheet name actually is.

Put the formula in A1:K5 (or however many places you have -- one row for each)

 

[rireland]

Thanks a lot

 

[rireland]

Hi Again

I've named the input sheet INPUT and put in the formula

=INDEX(INPUT!$A:$K,MATCH(ROW(),INPUT!$H:$H,0),COLUMN())


in cells A1:K14 of OUTPUT and I get #N/A in all the cells. Can you check if I've made an error.

Richard

 

[Sal Paradise]

I don't know why it isn't working for you. Here's an example using my method:

Excel Workbook
	A	B	C	D	E	F	G
1	Input				Output
2	Name	Score	Rank		Name	Score	Rank
3	Andy Alfa	0			Freddy Foxtrot	7	1
4	Bernie Bravo	1			Danny Delta	6	2
5	Chris Charlie	4	4		Ernie Echo	5	3
6	Danny Delta	6	2		Chris Charlie	4	4
7	Ernie Echo	5	3		#N/A	#N/A	#N/A
8	Freddy Foxtrot	7	1		#N/A	#N/A	#N/A
9	Greg Golf	3			#N/A	#N/A	#N/A
Sheet1

It seems to work fine for me. I don't know what's wrong with yours. Perhaps change $H:$H with $H$1:$H$50 or whatever range, and $A:$K with $A$1:$K$50 or whatever?

 

[JB_Scotland]

Have you considered using auto filter? Data / Filter / Auto Filter.

Place your data in a table, apply an auto filter, then either choose "Top 10..." or "Sort Ascending" in your Final Place column. You can adjust top 10 to top 4 or whatever else you may want.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 103px"><COL style="WIDTH: 221px"><COL style="WIDTH: 123px"><COL style="WIDTH: 89px"><COL style="WIDTH: 118px"><COL style="WIDTH: 108px"><COL style="WIDTH: 112px"><COL style="WIDTH: 70px"><COL style="WIDTH: 66px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">No</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt">HORSE</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt">RIDER</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; TEXT-ALIGN: center">PENALTY</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; TEXT-ALIGN: center">TEAM/CLUB</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">FINAL PLACE</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">SCORE</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">JUMP</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; TEXT-ALIGN: center">TIME </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; TEXT-ALIGN: center">TOTAL FAULT</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">32</TD><TD>Fern Ayre Saint Nic</TD><TD>Hayley Atkins</TD><TD> </TD><TD style="TEXT-ALIGN: center">BDPC</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">49.2</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">38</TD><TD>Pine Row Sand Storm</TD><TD>Morgan Grubb</TD><TD> </TD><TD style="TEXT-ALIGN: center">LPRC</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">49.2</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">34</TD><TD>Chevarna</TD><TD>Kathryn Fairbairn</TD><TD> </TD><TD style="TEXT-ALIGN: center">WTPC</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">49.2</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">30</TD><TD style="FONT-SIZE: 8pt">Karshna Crystal Princess</TD><TD>Olivia Whatley</TD><TD> </TD><TD style="TEXT-ALIGN: center">DDPC</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">55.2</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">31</TD><TD>Alexander Beetle</TD><TD>Taylor Clark</TD><TD> </TD><TD style="TEXT-ALIGN: center">WTPC</TD><TD style="COLOR: #0000ff"> </TD><TD style="TEXT-ALIGN: center">58.8</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">33</TD><TD>Ollie</TD><TD>Taylor Clark</TD><TD> </TD><TD style="TEXT-ALIGN: center">WTPC</TD><TD style="COLOR: #0000ff"> </TD><TD style="TEXT-ALIGN: center">43.8</TD><TD style="TEXT-ALIGN: center">r</TD><TD> </TD><TD style="TEXT-ALIGN: center">R</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">35</TD><TD>Fife</TD><TD>Ashton Brown</TD><TD> </TD><TD style="TEXT-ALIGN: center">GTPC</TD><TD style="COLOR: #0000ff"> </TD><TD style="TEXT-ALIGN: center">63.6</TD><TD style="TEXT-ALIGN: center">R</TD><TD> </TD><TD style="TEXT-ALIGN: center">R</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">36</TD><TD>B.P. Christmas Mint</TD><TD>Deanne Earley</TD><TD> </TD><TD style="TEXT-ALIGN: center">BDPC</TD><TD style="COLOR: #0000ff"> </TD><TD style="TEXT-ALIGN: center">33.6</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">37</TD><TD>Rabbit</TD><TD>Paige Ludbey</TD><TD> </TD><TD style="TEXT-ALIGN: center">GTPC</TD><TD style="COLOR: #0000ff"> </TD><TD style="COLOR: #0000ff"> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4