Formula to look X cells to right then up to keyword - Step 1

[malvdh]

Hello all, (Excel 2007 user)

I have a spread sheet that has data dumped into one column. I need to assign each row of each page the corresponding Fund and Account number.

There are keywords that can be used to search and capture.

Each page has a keyword, lets say TopOfPage.
The Fund number is in a fixed location under the TopOfPage, lets say two rows down
The Account number is also fixed under the Fund number, three rows down from TopOfPage

The Fund number should be in column A.
The Account number should be in column B.
Leaving the data in column C.

Is there a formula that will take the Fund number (first five digits of that row) and copy it in column A.
The Account number is anywhere from the first four digits to six digits, and would need to be copied into column B.

Is there a formula that I can paste into column A and B that will accomplish this? I am looking for one formula that can be changed to fit both the Fund number and the Account number. The x-factors that would need to be changed are; which column to look up for the 'TopOfPage', and the number of rows down from the 'TopOfPage'. This is ofcourse only possible if the formula can pull the first digits from the string. There are spaces after the Fund and Account numbers.

Please note that once the formulas have been established they will not need to change, I just need five of this same formula changed to suit the data.

Any help or nudging in the right direction would be great.
-marc

 

[malvdh]

Happy that I could assist you.

For the color change, yes conditional formatting will be able to mark the first of each group. Select B2:B whatever then under conditional formatting select formula is and use B2<>B1.

I have adapted your formula to the report and it works lovely, but now trying to do the Conditional Formatting for color. It doesn't want to do any colors. Can you tell what I've done wrong by the screen shot below? Note: this is the small report, the other is over 14k rows and grows each month.