# Copy Data from Criteria

I got this formula =INDEX(Sheet4!\$E\$1:\$Y\$16,MATCH(\$A\$1,Sheet4!\$Z\$1:\$Z\$16,1),1) Where I have a value in Sheet6.(A1). I need to search Sheet4 Column ("Z") for that number or as close to that number as possible, then take the data on the corresponding row in column "E to Y" and copy it to sheet6 Column Z. However I need to copy the entire column of data, just not that cell only.  I have attached a workbook to reference.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Quick question i have looking at your data...

if you have more then 1 entry matching your criteria..Do you want to pick both of them or the one which is earlier??

For instance value -15 exists in sheet-4 in row -10 and row-18..

Also do you always want to go for values lesser or exact then that or it can be greater as well like value-16 and you have value 15 and value-17 which one you want to pick??

Similarly value -16 but you have value -12 and value 17..now which one you will pick??
Incident ResponderAuthor Commented:
The first instance is preferred.  Here is my sample book I forgot to attach
Book3.xlsx
Commented:
Their you go this is what you are looking for...

I added 3 more columns which you were missing...

Saurabh...
Book3-1.xlsx
Finance AnalystCommented:
@macone1976 - so what was wrong with the solution I provided last night, ie the INDEX formula you have in the question?

In your previous question you stated that you only needed to look at the first block of times, ie down to row 16, hence the reason for the rows 1:16 in the formula.

Previous question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28628327.html

In my original post, I gave the formulas for the 8 columns (BE to BL) of data that you requested. Saurabh's adjustment to my formula just implements the adjustment for the column reference in the INDEX function, he has used the current column number less 12 whereas I was suggesting referencing a cell containing the column number.

Thanks
Rob H
Incident ResponderAuthor Commented:
Rob, sorry, I knew you were off to bed and was trying to get additional help.  Still looking for a resolution.  In my sample book I need all the data not just in that row, but everything below it to.  So in the example I would need row 10 all the way down to row 53.  the formula only provides the first row.
Finance AnalystCommented:
Right, OK. The original question suggested that you needed only one row of data copied for the timeslot that matched.

Thanks
Rob
Incident ResponderAuthor Commented:
Do you know how I can get all the data copied.
Finance AnalystCommented:
OK, how about an alternative approach?

In column AA, put this formula starting at row 5 and copy down:

=OR(Z5>=MROUND(Sheet6!\$A\$1,3),COUNTIF(AA\$4:AA4,TRUE)>0)

The MROUND function syntax is:

=MROUND(Value,Factor)

and rounds value to the nearest multiple of the factor; ie in your case specifying 3 as the factor. 16 will get rounded down to 15, 17 will get rounded up to 18.

This will give a TRUE or FALSE result based on the value in in column Z being greater than or equal to the rounded value of Sheet6!A1. Once one TRUE has been found, the rest will also become TRUE.

You can then use a choice of Filter options (AutoFilter or Advanced Filter) to copy the TRUE values to the other sheet, either manually or as part of a function.

Thanks
Rob H
Incident ResponderAuthor Commented:
How would I get a function setup to copy all the data that is true.
Finance AnalystCommented:
Will the true data has headers?

If so you can use the Advanced Filter function to copy to the other sheet.
Incident ResponderAuthor Commented:
No, no true headers as the data can vary.
Finance AnalystCommented:
Just a row with Header1, Header2, Header3 etc would suffice, so long as each column is different and all columns included in the range have a header.
Finance AnalystCommented:

Note also addition of TRUE/FALSE formula in column AA and small table in AF1:AF2.

With this file go to Sheet6 and clear all of the data from M3:AF46 (all the yellow highlighted area).

Select a cell in this now clear area, anywhere will do so long as it somewhere where all surrounding cells are blank, eg M5.

Then select the Data tab and choose the advanced button, small one next to the big Filter button. In the popup that appears fill in the 1 tick box and 3 range boxes as per the screen shot and click OK.

The three entries are:
List Range:          Sheet4!\$B\$1:\$AA\$53
Criteria Range:   Sheet4!\$AG\$1:\$AG\$2
Copy to:               Sheet6!\$M\$2:\$AF\$2

This will then repopulate the data that you earlier cleared, I left it there only to show the result after running the routine; the routine would have overwritten it anyway.

If so required, this can be automated via a VBA routine and the Data, Criteria and Copy to ranges can be given range names, with the data range being dynamic as it will obviously change over time.

Thanks,
Rob H
Book3-1-1.xlsx

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Finance AnalystCommented:
If you don't want the headers, as basic as they are, you will have to do this manually instead.

Select the data on Sheet4 including the TRUE/FALSE formula column. Select the Data ribbon and select the big Filter button. This will add dropdowns against each of the columns in the first row, without headers unfortunately this is the first row of data.

With the drop down for the TRUE/FALSE formula column deselect the FALSE option tick box, leaving the TRUE ticked. Click OK and the FALSE entries will be hidden (except for the first row as it thinks it is a header). You can now copy the resulting rows to the clipboard and manually paste them onto Sheet6 as required. When pasting onto Sheet6 only those rows that were visible will be pasted.

With the TRUE/FALSE formula as I have written it all entries below the first TRUE will also be TRUE. Therefore using the Auto Filter is probably not required, highlight and use Find function or scan down the column to manually find the first TRUE. You can then highlight from there down to copy and paste to Sheet6.

Thanks
Rob H
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.