INDEX MATCH help

%3CLINGO-SUB%20id%3D%22lingo-sub-2526324%22%20slang%3D%22en-US%22%3EINDEX%20MATCH%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526324%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20write%20a%20formula%20to%20copy%20the%20Item%20Number%20Column%20values%20to%20fill%20next%20to%20the%20Project%20Number%20Values%20all%20the%20way%20until%20a%20new%20Item%20Number%20is%20reached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20example%3A%2020100000%20needs%20to%20be%20automatically%20filled%20until%2020100005%2C%20then%2020100005%20needs%20to%20be%20filled%20until%20next%20item%20number%20is%20reached.%20I%20have%20over%20400%2C000%20rows%20of%20data%20so%20I%20can't%20just%20copy%20and%20drag.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2526324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2526596%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098179%22%20target%3D%22_blank%22%3E%40jwilson7%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Select%20the%20entire%20column%20B%3C%2FP%3E%3CP%3E2.%20F5%3C%2FP%3E%3CP%3E3.%20Special%3C%2FP%3E%3CP%3E4.%20Select%20-%20Blanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20all%20blanks%20in%20column%20B%20until%20the%20last%20used%20row%20are%20selected%20with%20the%20active%20cell%20should%20in%20B3.%3C%2FP%3E%3CP%3EType%20the%20formula%20%3CSTRONG%3E%3DB2%3C%2FSTRONG%3E%20and%20confirm%20by%20Ctrl-Enter.%20This%20should%20fill%20all%20the%20blank%20cells%20with%20a%20formula%20that%20takes%20whatever%20is%20in%20the%20cell%20directly%20above%20it.%20Not%20sure%20how%20this%20will%20perform%20on%20400K%20rows%2C%20but%20once%20done%20you%20could%20copy%20column%20B%20and%20paste%20as%20values%20on%20top%20of%20itself%20to%20get%20rid%20of%20the%20formulae.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOtherwise%2C%20PowerQuery%20could%20be%20useful.%20Works%20great%20on%20large%20data%20sets%20and%20it%20has%20a%20neat%20%22Fill%20Down%22%20function%20that%20does%20the%20same%2C%20but%20without%20formulae.%20Though%20you%20would%20have%20to%20copy%2Fpaste%20the%20%22filled%20column%22%20back%20to%20your%20original%20data%20if%20you%20want%20to%20continue%20working%20in%20that%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply

@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.