INDEX MATCH help

[jwilson7]

I am trying to write a formula to copy the Item Number Column values to fill next to the Project Number Values all the way until a new Item Number is reached.

My example: 20100000 needs to be automatically filled until 20100005, then 20100005 needs to be filled until next item number is reached. I have over 400,000 rows of data so I can't just copy and drag.

Thanks

[#2]

@jwilson7 Try this:

1. Select the entire column B

2. F5

3. Special

4. Select - Blanks

Now all blanks in column B until the last used row are selected with the active cell should in B3.

Type the formula =B2 and confirm by Ctrl-Enter. This should fill all the blank cells with a formula that takes whatever is in the cell directly above it. Not sure how this will perform on 400K rows, but once done you could copy column B and paste as values on top of itself to get rid of the formulae.

Otherwise, PowerQuery could be useful. Works great on large data sets and it has a neat "Fill Down" function that does the same, but without formulae. Though you would have to copy/paste the "filled column" back to your original data if you want to continue working in that sheet.