Need help! Merging date from two VERY DIFFERENT spreadsheets!

%3CLINGO-SUB%20id%3D%22lingo-sub-1326195%22%20slang%3D%22en-US%22%3ENeed%20help!%20Merging%20date%20from%20two%20VERY%20DIFFERENT%20spreadsheets!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326195%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all!%20I%20need%20to%20merge%20data%20from%202%20very%20different%20spreadsheets.%26nbsp%3B%201%20spreadsheet%20has%20registration%20information%20for%20people%20attending%20an%20event.%26nbsp%3B%201%20spreadsheet%20has%20commerce%20information%20for%20people%20attending%20that%20event.%26nbsp%3B%20Each%20primary%20registrant%20(but%20not%20their%20guests)%20have%20a%20submission%20ID%20that%20is%20present%20on%20both%20spreadsheets.%26nbsp%3B%20I%20need%20the%20information%20in%20the%20commerce%20spreadsheet%20to%20migrate%20to%20the%20row%20in%20the%20registration%20spreadsheet%20with%20the%20corresponding%20submission%20ID.%3C%2FP%3E%3CP%3EI%20am%20a%20beginner%20in%20excel%2C%20approaching%20intermediate%20so%20I%20would%20very%20much%20appreciate%20any%20guidance%20anyone%20can%20give!%20Thanks!%3C%2FP%3E%3CP%3EKForry%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1326195%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1326548%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help!%20Merging%20date%20from%20two%20VERY%20DIFFERENT%20spreadsheets!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631412%22%20target%3D%22_blank%22%3E%40kforry%3C%2FA%3E%26nbsp%3B%20I%20don't%20know%20for%20sure%20about%20your%20set%20up%20but%20it%20sure%20sounds%20like%20a%20perfect%20example%20for%20match-index.%26nbsp%3B%20I%20would%20recommend%20adding%20a%20column%20called%20IDindex%20and%20use%20the%20'match'%20formula%20to%20find%20the%20index%20of%20the%20corresponding%20submission%20ID%20on%20the%20other%20sheet.%26nbsp%3B%20Then%20use%20'index'%20formula%20get%20the%20correct%20value%20(located%20at%20IDindex)%20from%20each%20column%20you%20want%20from%20the%20other%20sheet.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330126%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help!%20Merging%20date%20from%20two%20VERY%20DIFFERENT%20spreadsheets!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BMy%20boss%20is%20working%20on%20this%20also%20and%20figured%20out%20that%20step%20with%20VLookup.%26nbsp%3B%20The%20next%20hurdle%20we%20have%20is%20performing%20an%20addition%20calculation%20based%20on%20event%20registrant's%20id%20numbers.%3C%2FP%3E%3CP%3ESo%3A%3C%2FP%3E%3CP%3EIf%20Tom%20(primary%20registrant)%20purchased%20a%20%2410%20shirt%26nbsp%3B%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3EJoan%20(his%20guest)%20purchased%20a%20%2410%20shirt-%3C%2FP%3E%3CP%3EI%20need%20our%20spreadsheet%20to%20calculate%20this%2C%20based%20on%20there%20being%20a%20matching%20ID%20number%20that%20both%20Tom%20and%20Joan%20share.%26nbsp%3B%20I%20need%20the%20spreadsheet%20to%20do%20this%20for%20each%20primary%20(there%20are%20700%20of%20them)%20so%20that%20I%20have%20a%20cell%20with%20this%20total%20merchandise%20purchase%20number%20in%20it%20so%20I%20can%20include%20it%20in%20a%20mail%20merge%20being%20sent%20to%20each%20primary.%20Do%20you%20happen%20to%20know%20of%20a%20formula%20that%20basically%20says%3A%20If%20this%20person's%20ID%20matches%20this%20other%20person's%20ID%2C%20then%20add%20all%20these%20merchandise%20values%20in%20their%20rows%20into%20a%20cell%20in%20only%20the%20primary's%20row%3F%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20help!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330361%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help!%20Merging%20date%20from%20two%20VERY%20DIFFERENT%20spreadsheets!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631412%22%20target%3D%22_blank%22%3E%40kforry%3C%2FA%3E%26nbsp%3B%20sounds%20like%20you%20want%20to%20use%20the%20sumif%20command.%20%26nbsp%3B%20%3Dsumif(%20%5BID%20range%5D%2C%20ID%2C%20%5BMerch%20%24%5D%20)%20%2Cwhere%20%5BID%20range%5D%20is%20the%20column%20on%20the%20purchase%20ledger%20sheet%20that%20has%20the%20primary%20ID%20and%20the%20%5BMerch%20%24%5D%20is%20the%20column%20on%20that%20same%20sheet%20that%20has%20the%20purchase%20price%2C%20and%20'ID'%20is%20the%20cell%20on%20%22this%22%20sheet%20that%20refers%20to%20the%20primary%20ID%20on%20this%20row.%26nbsp%3B%20Hope%20that%20makes%20sense.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello all! I need to merge data from 2 very different spreadsheets.  1 spreadsheet has registration information for people attending an event.  1 spreadsheet has commerce information for people attending that event.  Each primary registrant (but not their guests) have a submission ID that is present on both spreadsheets.  I need the information in the commerce spreadsheet to migrate to the row in the registration spreadsheet with the corresponding submission ID.

I am a beginner in excel, approaching intermediate so I would very much appreciate any guidance anyone can give! Thanks!

KForry

3 Replies
Highlighted

@kforry  I don't know for sure about your set up but it sure sounds like a perfect example for match-index.  I would recommend adding a column called IDindex and use the 'match' formula to find the index of the corresponding submission ID on the other sheet.  Then use 'index' formula get the correct value (located at IDindex) from each column you want from the other sheet.  

 

Highlighted

@mtarler My boss is working on this also and figured out that step with VLookup.  The next hurdle we have is performing an addition calculation based on event registrant's id numbers.

So:

If Tom (primary registrant) purchased a $10 shirt 

and

Joan (his guest) purchased a $10 shirt-

I need our spreadsheet to calculate this, based on there being a matching ID number that both Tom and Joan share.  I need the spreadsheet to do this for each primary (there are 700 of them) so that I have a cell with this total merchandise purchase number in it so I can include it in a mail merge being sent to each primary. Do you happen to know of a formula that basically says: If this person's ID matches this other person's ID, then add all these merchandise values in their rows into a cell in only the primary's row?

Many thanks for your help!!!!

Highlighted

@kforry  sounds like you want to use the sumif command.   =sumif( [ID range], ID, [Merch $] ) ,where [ID range] is the column on the purchase ledger sheet that has the primary ID and the [Merch $] is the column on that same sheet that has the purchase price, and 'ID' is the cell on "this" sheet that refers to the primary ID on this row.  Hope that makes sense.