Home

How to auto-insert multiple rows of data based on a lookup or index

%3CLINGO-SUB%20id%3D%22lingo-sub-327442%22%20slang%3D%22en-US%22%3EHow%20to%20auto-insert%20multiple%20rows%20of%20data%20based%20on%20a%20lookup%20or%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327442%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20pretty%20new%20to%20the%20higher%20functions%20in%20Excel%20and%20need%20to%20solve%20an%20issue.%20We%20are%20working%20with%20equipment%20lists%20for%20massive%26nbsp%3Bprojects.%20The%20final%20list%20might%20have%2010's%20of%20thousands%20of%20lines.%20The%20typical%20workflow%20is%20to%20manually%20copy%2Fpaste%20rows%20of%20data%20per%20item%20from%20the%20Master%20equipment%26nbsp%3Blist%20to%20the%20project%20list%20as%20we%20discover%20we%20will%20need%20that%20equipment%20for%20a%20particular%20project.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20figured%20a%20way%20to%26nbsp%3Buse%20Index%20and%20Match%20to%20pull%20data%20from%20one%20sheet%20to%20another%20after%20just%20typing%20in%20the%20Equipment%20ID%20value.%20Match%20looks%20for%20what%20has%20been%20typed%20into%20the%20ID%20field%2C%20searches%20for%20the%20same%20value%20in%20the%20Master%20list%20and%20pulls%20data%20from%20other%20columns%20in%20the%20same%20row.%20Not%20all%20the%20columns%2C%20as%20some%20items%26nbsp%3Bneed%20to%20be%20manually%20entered.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20problem%20is%2C%20some%20of%20the%20equipment%20is%20described%20in%203-6%20rows.%20For%20instance%20a%20'computer%20bundle'%20is%20actually%20made%20up%20of%20four%20separate%20items%2C%20which%20each%20having%20a%20different%20model%20number%2C%20vendor%2C%20price%2C%20etc.%20So%20I%20need%20to%20be%20able%20to%20type%20in%20the%20ID%20and%20have%20it%20populate%20multiple%20rows%20of%20data%20if%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%20Would%20I%20be%20using%20an%20%22IF%22%20statement%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20is%20not%20possible%2C%20is%20there%20a%20way%20find%20an%20item%20and%20replace%20with%20multiple%20rows%20of%20data%2C%20NOT%20touching%20certain%20fields%20in%20the%20row%3F%20For%20instance%2C%20the%20row%20may%20have%20fields%20populated%20with%20a%20Room%20number%20and%20an%20architectural%20identifier%20specific%20to%20that%20unit.%20Those%20need%20to%20stay.%20Ideally%20those%20could%20be%20populated%20in%20the%20rows%20that%20are%20pasted%20in.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20so%20much%20and%20please%20let%20me%20know%20if%20clarification%20is%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-327442%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
JKSTONE5
New Contributor

Hello!

 

I am pretty new to the higher functions in Excel and need to solve an issue. We are working with equipment lists for massive projects. The final list might have 10's of thousands of lines. The typical workflow is to manually copy/paste rows of data per item from the Master equipment list to the project list as we discover we will need that equipment for a particular project.

 

I have figured a way to use Index and Match to pull data from one sheet to another after just typing in the Equipment ID value. Match looks for what has been typed into the ID field, searches for the same value in the Master list and pulls data from other columns in the same row. Not all the columns, as some items need to be manually entered.


The problem is, some of the equipment is described in 3-6 rows. For instance a 'computer bundle' is actually made up of four separate items, which each having a different model number, vendor, price, etc. So I need to be able to type in the ID and have it populate multiple rows of data if needed.

 

Is this possible? Would I be using an "IF" statement?

 

If it is not possible, is there a way find an item and replace with multiple rows of data, NOT touching certain fields in the row? For instance, the row may have fields populated with a Room number and an architectural identifier specific to that unit. Those need to stay. Ideally those could be populated in the rows that are pasted in.

Thanks so much and please let me know if clarification is needed.

 

Best,

 

Jen

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies