Chandoo, I have a 70,000 row deep exam results worksheet(Excel 2007). I want to copy every 9th row from this sheet to a difference sheet. Is there any formula to work this out. I can send the workbook if somebody is ready to solve. It's fairly easy with excel seemingly but I have no flashes.
How to copy cell contents of every nth row from sheet to sheet in Excel 2007?
- Thread starter Ramirez
- Start date
SirJB7
Excel Rōnin
Hi, Carlos!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
Assuming Sheet1 is the original and has a title row with data from row 2 in advance and Sheet2 is the target sheet, then:
a) copy row 1 from Sheet1 to Sheet2
b) type in cell A2 of Sheet2:
=INDICE(Hoja1!$A:$Z;(FILA()-1)*9+1;COLUMNA()) -----> in english: =INDEX(Hoja1!$A:$Z,(ROW()-1)*9+1,COLUMN())
c) adjust range $A:$Z as needed
d) copy across and down as needed
e) first "1" (-1) is target title rows number and second "1" (+1) is source title rows number, adjust them as needed
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
Assuming Sheet1 is the original and has a title row with data from row 2 in advance and Sheet2 is the target sheet, then:
a) copy row 1 from Sheet1 to Sheet2
b) type in cell A2 of Sheet2:
=INDICE(Hoja1!$A:$Z;(FILA()-1)*9+1;COLUMNA()) -----> in english: =INDEX(Hoja1!$A:$Z,(ROW()-1)*9+1,COLUMN())
c) adjust range $A:$Z as needed
d) copy across and down as needed
e) first "1" (-1) is target title rows number and second "1" (+1) is source title rows number, adjust them as needed
Regards!
Hello SirJB7, First, thanks a lott for the quick answer for my query. I could not implement that formula in my worksheet. Probably it is because of the incorrect placement of the "1s" in the formula. I could not judge correctly where to place the "1s". Anyways, I am going to give the actual workbook link here. In the sheet2, I have the sample of desired result format where I am facing the problem. Here is the Link: https://hotfile.com/dl/161708782/ab2e0f2/Result.xlsx.html . Just guide me please by giving formula to one column. Thanks a lott.
Hui, I appreciate your effort. Seemingly,I might not have asked for the right solution. But somehow it is not working the way I think. Anyways, This is the requirement: I want to post the marks for each subject , both for internal marks and external marks Roll no wise. How can I drag all these from the sheet1. Actually I have a 70,000 rows deep. You can see some of the marks I typed manually. In the same way I want for all the subjects. And this is a recurring task it occurs every two months. So what is my best option in this case? Thank you very much for your kind help.
NARAYANK991
Excel Ninja
Hi Carlos ,
If you can remove the duplicates in the Roll Nos. and have the unique set of roll numbers in column A on Sheet 2 , then the following formula , copied across and down should do the job :
[pre]
[/pre]
This should be entered as an array formula , using CTRL SHIFT ENTER.
The 4 is because there are 4 columns in the range D:G.
Taken from this link : http://www.cpearson.com/excel/MatrixToVector.aspx
Narayan
If you can remove the duplicates in the Roll Nos. and have the unique set of roll numbers in column A on Sheet 2 , then the following formula , copied across and down should do the job :
[pre]
Code:
=OFFSET(OFFSET(Sheet1!$D$2:$G$865,MATCH($A4,Sheet1!$A$2:$A$865,0)-1,,COUNTIF(Sheet1!$A$2:$A$865,$A4),4),TRUNC((COLUMN()-COLUMN($B$4))/4),MOD((COLUMN()-COLUMN($B$4)),4),1,1)This should be entered as an array formula , using CTRL SHIFT ENTER.
The 4 is because there are 4 columns in the range D:G.
Taken from this link : http://www.cpearson.com/excel/MatrixToVector.aspx
Narayan