Highlighted
New Contributor

# Need help! Merging date from two VERY DIFFERENT spreadsheets!

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

# Re: Need help! Merging date from two VERY DIFFERENT spreadsheets!

@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

# Re: Need help! Merging date from two VERY DIFFERENT spreadsheets!

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