Dear all,
I got 2 worksheets, sheet1 has an array from B2:K11. While sheet2 extract data from cells C3:I11 of sheet1 of every 2 rows and every 3 columns. What will be the formula in sheet2 to get this result?
Thanks
Kent
ScreenHunter_002.jpg
ScreenHunter_003.jpg
Hi,
Please upload the actual workbook. This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.
Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results. Also indicate what determines the starting position,
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
TRY
=INDEX(Sheet1!$A$2:$K$11,ROWS($1:1)*2,COLUMNS($A:A)*3)
Dear Richard,
Actually I had tried but didn't know how to upload my excel table, but insert image is OK.
Dear John,Originally Posted by JohnTopley
Your formula fails when sheet1 contains string.
Thank you.
Kent
Dear Richard,
My table is attached for your reference please.
Kent
I don't understand: If you mean TEXT (string) , then it works OK. The data format should have no bearing on the output: it is simply a "Lookup".
Post a file (Click "Go advanced" then scroll down to "Manage "Attachments")
I have just put the formula in your file and it works fine.
Last edited by JohnTopley; 05-19-2016 at 11:09 AM.
Dear John,
Yeah~ It's my fault. Your formula works on both text and numeric.
Thank you very much.
Kent
Copy the following formula into sheet2 cell B2 and then drag formula across and down . The solution assumes data source is on "sheet1" in the specified range.
=INDIRECT(ADDRESS((COUNT(B$1:B1)*2)+3,(COUNT($A2:A2) + 1) *3,,,"Sheet1"))
... oops ... you got some great answers already ... sorry .
Last edited by nimrod; 05-19-2016 at 11:30 AM.
Dear John,
My attached table becomes more complicated this time. What is the formula in sheet2 this time please.
ScreenHunter_004.jpgScreenHunter_005.jpg
Kent
Dear nimrod,
Thanks anyway.
Any idea on my question on thread #11 please?
Kent
Hi,
Before looking at this are there any more complications on the horizon?
I suspect the answer is no single formula because it is a "random" (irregular) pattern.
Yeah~
I think so.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks