Extracting data dynamically from one sheet to another

Part 1:

I have been trying to use the INDEX function to pull data from one sheet to another with mixed results. The problem is that the data I need rests in two columns on Sheet1 E32:E41 and K32:K41, the target on Sheet2 will house the data starting at O9:O28. I will then skip a cell on Sheet2 and collect the next set of data starting at O:30 from Sheet1 E45:54 and K45:54. This will continue until I have captured all of the data from COLUMNS E and K from Sheet1, the data set is abbreviated and there are many more customers not in my attached example workbook.

I can copy/paste the ranges but that gets tedious, I can't seem to get INDEX to work with the mismatched origin and target cells by setting up one formula and copying down. Maybe this isn't possible but I hope someone can help.

Part 2:

If I can get Part 1 accomplished I was going to take the data in Sheet2 COLUMN O (first entry would be in O9) and post the first price in the corresponding customers 'Current Price' in H** (first entry would be in H9). And then only populate the next entry in H** (going from O10 to H10) if the value was greater than or less than the prior entry (H9). If there is no change in price I don't want to make a new entry, only if the price has changed. Then I will need to populate the contract dates based on the beginning of the price change. Now, if I can avoid doing Part 1 (creating a table on Sheet2 based on values from Sheet1) and instead just lookup the values from Sheet1 to fill the changes in price if they are different from the previous price then that would be great. Price from Sheet 1 COLUMN E would go to Sheet 2 COLUMN H and Rnw Price from Sheet1 COLUMN K would go to Sheet2 COLUMN M.

I can accomplish all of this manually and copy Sheet2 A7:M24 and paste it down and then go in and make the changes by using replace. But I wanted to build the first customer on Sheet2 then be able to copy down without making adjustments.

If you need any clarification feel free to ask, I hope I did a decent job of explaining what I need. Thanks in advance for any help.
example082514.xlsx

See attached file.

I haven't done Part 1, as it can be done directly as described in your Part 2, using this formula in H9:

=INDEX(Sheet1!E:E,MATCH(LOOKUP(1000,A$1:A9),Sheet1!A:A,0)+ROWS($1:2))

This formula can be copied down to H18. It can also be copied to H27, H45, H63 and H81 (and so on), but the final term needs to be reset to ROWS($1:2) each time before copying it down the next 9 rows. A similar formula can be used to get the renewal price in M9, but you will need to change the E:E to K:K in the first term.

I've also applied conditional formatting to the cells H10:H18 (and other blocks of 9 cells) to make the cells look blank if they are the same as the cell above.

Hope this helps.

Pete

That makes sense, did have a few questions.

What is the significance of the 1,000 as the lookup_value when matching the customer number?

If the customer number wasn't always unique, would there be another method to lookup the entries if the arrangement of the data was always the same on the origin and target areas?

Thanks again.

In your example file you had customer numbers up to 5 - that number (i.e. 1000) is just a larger number than you might expect to have for your customers, but if you have bigger numbers then you can increase its value.

You will need to post another file with examples of non-unique customer numbers and an explanation of how you would like them to be handled.

Hope this helps.

Pete

For example, if a contract fully expired then a customer number could get repeated and there wouldn't be a unique way to identify the price via customer number. Is there a way instead to use the fixed interval on Sheet1, price data will always start at E32 and run for 10 CELLS then the next set of price data will always start 13 ROWS down from the previous set. Same with the target on Sheet2, price data will always start at H9 and run for 10 CELLS then the next set will always start 18 ROWS down from the previous set.

thanks

I went with the original solution posted, so disregard the last request. thanks