move data

%3CLINGO-SUB%20id%3D%22lingo-sub-1610523%22%20slang%3D%22en-US%22%3Emove%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610523%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3B%20Any%20help%20will%20be%20appreciated.%26nbsp%3B%20I%20need%20to%20move%20data%20on%20multiple%20rows%20into%20one%20row%20to%20create%20a%20mail%20merge.%26nbsp%3B%20Each%20customer%20has%20anywhere%20from%202%20to%206%20rows%20of%20data%20with%205%20columns%20being%20the%20data%20I%20need.%26nbsp%3B%20The%20rest%20is%20repeated%20identification%20data.%26nbsp%3B%20I%20need%20to%20move%20the%20data%20from%205%20of%20the%20cells%20in%20each%20row%20into%20the%20first%20row%20with%20that%20has%20customers%20id.%20So%20I%20need%20excel%20to%20identify%20if%20the%20next%20row%20is%20the%20same%20customer%2C%20move%20the%20data%20if%20true%2C%20then%20delete%20the%20row%20it%20moved%20the%20data%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20move%20it%20with%20%22if%22%20and%20%22offset%22%2C%20but%20I%20don't%20know%20how%20to%20make%20the%20formula%20move%20to%20the%20next%20row%20after%20its%20captured%20the%205%20cells%20I%20need%20unless%20I%20manually%20change%20it%20which%20will%20take%20forever.%20For%20example%2C%20once%20a%20drag%20the%20formula%205%20cells%2C%20I%20need%20the%20%22if%22%20command%20to%20change%20from%20comparing%20A2%3DA3%20to%20A2%3DA4.%26nbsp%3B%20%26nbsp%3BI%20would%20also%20like%20to%20delete%20the%20row%20once%20the%20data%20has%20been%20moved%20which%20I%20don't%20know%20how%20to%20do%20unless%20I%20search%20for%20a%20VBA%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20if%20I'm%20on%20the%20right%20track%20or%20if%20I%20am%20making%20it%20too%20complex.%3C%2FP%3E%3CP%3EI%20am%20not%20great%20with%20pivot%20tables%20but%20could%20figure%20that%20out%20as%20long%20as%20it%20works%20with%20the%20mail%20merge.%26nbsp%3B%20I%20have%20not%20worked%20with%20them%20very%20much%20and%20don't%20want%20to%20try%20it%20unless%20I%20am%20sure%20it%20would%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1610523%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610569%22%20slang%3D%22en-US%22%3ERe%3A%20move%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770295%22%20target%3D%22_blank%22%3E%40robscheerger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20difficult%20to%20envisage%20what%20you%20are%20trying%20to%20achieve%20without%20a%20visual%20representation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20able%20to%20share%20sample%20data%20to%20facilitate%20an%20easier%20solution%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610600%22%20slang%3D%22en-US%22%3ERe%3A%20move%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610600%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BI%20will%20post%20an%20example%20shortly%20after%20I%20disguise%20the%20data.%20Thank%20for%20the%20suggestion%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610614%22%20slang%3D%22en-US%22%3ERe%3A%20move%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770295%22%20target%3D%22_blank%22%3E%40robscheerger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eok%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1611336%22%20slang%3D%22en-US%22%3ERe%3A%20move%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1611336%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20send%20about%20600%20letters%20based%20on%20this%20data%20with%20about%205000%20rows%20in%20total.%26nbsp%3B%20Within%20the%20letter%20is%20a%20table%20for%20me%20to%20enter%20the%20service%20data%20and%20explain%20how%20its%20being%20provided%20in%20the%20pandemic.%26nbsp%3B%20The%20database%20produced%20one%20row%20per%20service%20so%20most%20of%20the%20customer%20data%20is%20repeated%20as%20you%20see%20but%20I%20need%20to%20service%20data.%26nbsp%3B%20For%20me%20to%20do%20the%20mail%20merge%20for%20each%20individual%20I%20want%20to%20take%20the%20service%20data%20(service%2Cfrequency%2C%20duration%2C%20explanation%20of%20services)%20put%20it%20in%20one%20row%20per%20individual%20so%20I%20can%20do%20the%20mail%20merger%20in%20word%2C%26nbsp%3B%20THanks%20for%20your%20help.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi.  Any help will be appreciated.  I need to move data on multiple rows into one row to create a mail merge.  Each customer has anywhere from 2 to 6 rows of data with 5 columns being the data I need.  The rest is repeated identification data.  I need to move the data from 5 of the cells in each row into the first row with that has customers id. So I need excel to identify if the next row is the same customer, move the data if true, then delete the row it moved the data from.

 

I can move it with "if" and "offset", but I don't know how to make the formula move to the next row after its captured the 5 cells I need unless I manually change it which will take forever. For example, once a drag the formula 5 cells, I need the "if" command to change from comparing A2=A3 to A2=A4.   I would also like to delete the row once the data has been moved which I don't know how to do unless I search for a VBA command.

 

Not sure if I'm on the right track or if I am making it too complex.

I am not great with pivot tables but could figure that out as long as it works with the mail merge.  I have not worked with them very much and don't want to try it unless I am sure it would work.

 

Thanks for any help.

4 Replies

@robscheerger 

 

It's difficult to envisage what you are trying to achieve without a visual representation.

 

Are you able to share sample data to facilitate an easier solution?

 

Cheers

 

 

 I will post an example shortly after I disguise the data. Thank for the suggestion 

@wumolad 

I need to send about 600 letters based on this data with about 5000 rows in total.  Within the letter is a table for me to enter the service data and explain how its being provided in the pandemic.  The database produced one row per service so most of the customer data is repeated as you see but I need to service data.  For me to do the mail merge for each individual I want to take the service data (service,frequency, duration, explanation of services) put it in one row per individual so I can do the mail merger in word,  THanks for your help.

@wumolad