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.
macone1976Incident ResponderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Saurabh Singh TeotiaCommented:
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??
macone1976Incident ResponderAuthor Commented:
The first instance is preferred.  Here is my sample book I forgot to attach
Book3.xlsx
Saurabh Singh TeotiaCommented:
Their you go this is what you are looking for...

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

Saurabh...
Book3-1.xlsx
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance 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
macone1976Incident 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.
Rob HensonFinance AnalystCommented:
Right, OK. The original question suggested that you needed only one row of data copied for the timeslot that matched.

Thanks
Rob
macone1976Incident ResponderAuthor Commented:
Do you know how I can get all the data copied.
Rob HensonFinance 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
macone1976Incident ResponderAuthor Commented:
How would I get a function setup to copy all the data that is true.
Rob HensonFinance AnalystCommented:
Will the true data has headers?

If so you can use the Advanced Filter function to copy to the other sheet.
macone1976Incident ResponderAuthor Commented:
No, no true headers as the data can vary.
Rob HensonFinance 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.
Rob HensonFinance AnalystCommented:
See attached file with basic headers added to each of the columns on Sheet4 and relevant headers added to data on Sheet6.

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.

Filter Settings
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

Your issues matter to us.

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

Start your 7-day free trial
Rob HensonFinance 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.