Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2404504%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404504%22%20slang%3D%22en-US%22%3E%3CP%3EWorking%20on%20a%20spreadsheet%2C%20trying%20to%20pull%20information%20from%20one%20tab%20to%20another.%20I%20have%20a%20bunch%20of%20raw%20data%20with%20part%20%23's%20as%20headings%20in%20column%20D%2C%20and%20the%20rest%20of%20the%20column%20is%20empty%20until%20the%20next%20part%20%23%20heading.%20I%20want%20a%20formula%20that%20will%20recognize%20each%20line%20of%20data%20based%20on%20which%20part%20%23%20it%20is%20under%2C%20there%20are%20140%2C000%20lines%20of%20data%20so%20this%20will%20speed%20up%20the%20process%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2404504%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2404900%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1068457%22%20target%3D%22_blank%22%3E%40sebastianhebert%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20easier%20(I%20think)%20solution.%20Add%20a%20column%20(E%20perhaps)%2C%20what%20we%20often%20call%20a%20%22helper%20column%22%20and%20populate%20each%20row%20with%20the%20part%20number%20using%20this%20formula%2C%20copied%20down.%20(This%20is%20assuming%20that%20D2%20contains%20your%20first%20part%20number).%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(ISBLANK(D2)%2CE1%2CD2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThat%20will%20populate%20the%20row%20with%20the%20applicable%20part%20number%20and%20make%20looking%20things%20up%20a%20lot%20easier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Working on a spreadsheet, trying to pull information from one tab to another. I have a bunch of raw data with part #'s as headings in column D, and the rest of the column is empty until the next part # heading. I want a formula that will recognize each line of data based on which part # it is under, there are 140,000 lines of data so this will speed up the process

1 Reply

@sebastianhebert 

 

Here's an easier (I think) solution. Add a column (E perhaps), what we often call a "helper column" and populate each row with the part number using this formula, copied down. (This is assuming that D2 contains your first part number).

=IF(ISBLANK(D2),E1,D2)

That will populate the row with the applicable part number and make looking things up a lot easier.