Home

Excell formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-1014106%22%20slang%3D%22en-US%22%3EExcell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014106%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20would%20like%20help%20to%20merge%20multiple%20rows%20and%20columns%20into%20one%20continuous%20row.%20Eg%20-%20all%20data%20to%20be%20spread%20across%20A1%2CB1%2CC1%2CD1%2CE1%2CF1%2CG1%2CH1%2CI1%2CJ1%2CK1%20etc%20FROM%3A%20B4%2CC4%2CA6%2CB6%2CA7%2CB7%2CB8%2CA9%2CB9%2CA11%2CB11%20-%20Is%20there%20an%20easy%20formula%20to%20do%20this%3F%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1014106%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-1014133%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStart%20this%20formula%20in%20cell%20A1%20then%20drag%20it%20to%20the%20right%3A%3C%2FP%3E%3CPRE%3E%3DCHOOSE(COLUMN()%2C%24B%244%2C%24C%244%2C%24A%246%2C%24B%246%2C%24A%247%2C%24B%247%2C%24B%248%2C%24A%249%2C%24A%2411%2C%24B%2411)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157603iB1E3B97C29CE4D5C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Align%20multiple%20non-adjacent%20cells%20into%20one%20row.png%22%20title%3D%22Align%20multiple%20non-adjacent%20cells%20into%20one%20row.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014134%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014134%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B-%20What%20do%20you%20mean%20%22drag%20it%20to%20the%20right%22%3F%20I%20have%2012%2C000%20lines%20of%20data%20I%20am%20trying%20to%20format%20across%20the%20rows.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014140%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20mean%20copying%20the%20formula%20to%20the%20next%20right%20cells%20by%20dragging%20the%20first%20cell%20using%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DkgGF136-kIg%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFill%20Handle%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014142%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014142%22%20slang%3D%22en-US%22%3EThank%20you.%20Is%20there%20to%20do%20it%20for%20all%2012%2C000%20lines%20or%20do%20I%20need%20to%20re-write%20the%20formula%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014239%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014239%22%20slang%3D%22en-US%22%3EThank%20you%20anyway.%20Long%20story%20short%20is%20I%20have%20an%20account%20list%20of%20clients%20who%20have%20purchased%20from%20us.%20Name%2C%20Address%2C%20Suburb%2C%20Post%20Code%2C%20Phone%20Number%20etc%20etc%20and%20want%20to%20get%20them%20as%20mentioned%20above%20on%20one%20line%20across%20the%20sheet%20so%20I%20can%20then%20sort%20them%20by%20suburb.%20Eg%20-%20A1%20%3D%20Name%2C%20B1%20Address%2C%20C1%20Suburb%2C%20D1%20Post%20Code%2C%20E1%20Phone%20Number.%20Too%20many%20clients%20to%20go%20through%20and%20cut%20%26amp%3B%20paste%20unfortunately.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015094%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20provide%20a%20sample%20of%20the%20clients'%20data%20in%20an%20attached%20workbook%2C%20to%20see%20if%20that's%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015755%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015755%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%20-%20Each%20black%20line%20is%20a%20new%20client.%20I%20have%20over%2015%2C000%20lines%20in%20total%20which%20is%20roughly%201800%20%2B%20clients.%20Really%20appreciate%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20409px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157779i6BBF5D687C864864%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-11-19%20at%207.32.24%20am.png%22%20title%3D%22Screen%20Shot%202019-11-19%20at%207.32.24%20am.png%22%20%2F%3E%3C%2FSPAN%3E%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015837%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015837%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20IF%20your%20spreadsheet%20continues%20with%20exactly%20those%20same%20gaps%20(12%20rows%20between%20identical%20items)%20then%20you%20can%20copy%20the%20formulas%20in%20the%20second%20sheet%20in%20this%20workbook%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20the%20INDIRECT%20function%20to%20increase%20the%20row%20references%20by%2012%20in%20each%20new%20row.%26nbsp%3B%20To%20the%20right%20and%20across%20the%20top%20I've%20created%20a%20%22helper%20column%22%20and%20a%20%22helper%20row%22%20to%20assist%20in%20creating%20the%20address%20you%20are%20directing%20the%20formula%20to%20in%20the%20INDIRECT%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20crucial%2C%20though%2C%20that%20you%20have%20these%20new%20records%2012%20rows%20apart.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015844%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015844%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much.%20Excuse%20my%20novice%20question%20but%20how%20do%20I%20%22paste%22%20your%20formula%20in%20to%20grab%20my%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015851%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015851%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20409px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157786i2B0FCFD2628AA63F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-11-19%20at%207.32.24%20am.png%22%20title%3D%22Screen%20Shot%202019-11-19%20at%207.32.24%20am.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20an%20example%20of%20what%20I%20am%20working%20with%20and%20have%20over%2015000%20similar%20lines.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015852%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015852%22%20slang%3D%22en-US%22%3EAn%20unsolicited%20editorial%20comment%3A%20your%20original%20sheet%20is%20almost%20a%20textbook%20example%20of%20how%20NOT%20to%20use%20Excel.%20Whoever%20created%20it%20(it%20would%20appear)%20was%20thinking%20%223x5%20card%22%20layout%2C%20and%20using%20Excel%20because%20it%20had%20rows%20and%20columns%2C%20I%20guess.%20It%20would%20have%20made%20just%20as%20much%20sense%20to%20use%20Word.%20Or%203x5%20cards.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20say%20this%20not%20for%20your%20sake%2C%20ttlj2%20--%20you%20obviously%20are%20realizing%20you%20need%20to%20get%20records%20into%20a%20single%20row%20so%20you%20can%20sort%2C%20sift%2C%20summarize%20by%20zip%20code%20(all%20sorts%20of%20purposes)--but%20I'm%20writing%20this%20more%20to%20the%20beginners%20who%20might%20drop%20by%20and%20look%20at%20this%20string%20of%20notes.%20Beginners%20with%20Excel%20often%20come%20to%20the%20software%20with%20the%20previous%20paper%20method%20in%20mind%2C%20just%20using%20the%20software%20because%20it's%20cool%2C%20or%20easy%20to%20print%20out%20the%20records%2C%20easy%20to%20update%20them....but%20in%20the%20process%20make%20it%20next%20to%20impossible%20to%20really%20take%20advantage%20of%20all%20that%20Excel%20offers.%3CBR%20%2F%3E%3CBR%20%2F%3EYou're%20to%20be%20commended%20for%20addressing%20it.%20I%20hope%20my%20solution%20(above)%20makes%20sense%20to%20you.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015862%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015862%22%20slang%3D%22en-US%22%3E%3CP%3Eunfortunately%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bmy%20data%20is%20extracted%20from%20our%20business%20program%20that%20can%20%22convert%22%20it%20to%20Excell.%20Clearly%20this%20has%20not%20helped%20me%20to%20be%20able%20to%20sort%20into%20some%20workable%20format...%20Very%20frustrating.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015864%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015864%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3EHmmm...that%20IS%20a%20novice%20question.%20I%20guess%20the%20answer%20for%20the%20novice%20is%3C%2FP%3E%3COL%3E%3CLI%3EHighlight%20the%20items%20you%20want%20to%20copy%3C%2FLI%3E%3CLI%3EUse%20the%20Edit....Copy%20menu%3C%2FLI%3E%3CLI%3Eselect%20Edit...Copy%20from%20the%20menu%3C%2FLI%3E%3CLI%3Ego%20to%20where%20you%20want%20to%20paste%3C%2FLI%3E%3CLI%3Eselect%20Edit...Paste%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20keyboard%20shortcuts.%20If%20you%20use%20Word%2C%20they're%20the%20same.%20Ctrl%20C%20to%20copy%2C%20Ctrl%20V%26nbsp%3B%20to%20paste.%20In%20Mac%20environment%2C%20Command-C%2C%20Command-V.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20might%20be%20easier%20for%20you%20to%20post%20an%20edited%20copy%20of%20your%20spreadsheet%20(not%20just%20an%20image%2C%20an%20actual%20spreadsheet)...include%20about%20five%20to%20ten%20of%20the%20records%2C%20falsifying%20the%20names%20and%20any%20other%20identifiable%20info.%20Then%20I%20can%20put%20the%20formulas%20into%20place%20and%20get%20it%20started.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015871%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%26nbsp%3B%20That%20might%20actually%20be%20a%20blessing.%20If%20it's%20extracted%20(exported)%20from%20another%20program%2C%20there%20may%20be%20a%20way--I'd%20be%20surprised%20if%20there%20isn't--to%20export%20specifically%20in%20an%20excel%20table%20format.%26nbsp%3B%20See%20if%20there's%20an%20option%20for%20exporting%20to%20XLS%20or%20CSV%20files.......%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you've%20got%20is%20more%20of%20an%20export%20to%20print%2C%20and%20it%20does%20a%20good%20job%20of%20that%3B%20it%20just%20so%20happens%20that%20Excel%20can%20open%20it%2C%20but%20it%20(as%20you're%20finding)%20ain't%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20look%20for%20an%20export%20option%20to%20XLS%2C%26nbsp%3B%20XLSX%2C%20or%20CSV.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20that's%20not%20available%2C%20if%20you%20can%20post%20a%20section%20of%20it%20(cleaned%20so%20as%20not%20to%20reveal%20confidential%20or%20personal%20info)%26nbsp%3B%20I%20can%20get%20the%20formulas%20started%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015903%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015935%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015935%22%20slang%3D%22en-US%22%3E%3CP%3EOK%2C%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%26nbsp%3B%20I've%20done%20the%20first%20ten%20records%20and%20copied%20the%20formula%20that%20can%20(or%20should%20be%20able%20to)%20handle%20up%20to%20nearly%202%2C000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20suggest%20you%20do%20is%20this.%20First%2C%20make%20a%20copy%20of%20your%20original%20file.%3C%2FP%3E%3CP%3ESecond%2C%20in%20one%20of%20your%20copies%2C%20rename%20the%20tab%20that%20contains%20the%20records%20in%20your%20original%20workbook...rename%20it%20to%20%22RawData%22%3C%2FP%3E%3CP%3EThird%2C%20copy%20my%20%22Converter%22%20tab%20to%20that%20workbook.%3C%2FP%3E%3CP%3ETo%20do%20this%20last%2C%3C%2FP%3E%3COL%3E%3CLI%3Ehave%20both%20workbooks%20open%3C%2FLI%3E%3CLI%3Eleft%20click%20on%20%22Converter%22%3C%2FLI%3E%3CLI%3Eselect%20%22Move%20or%20Copy%22%3C%2FLI%3E%3CLI%3Epick%20the%20name%20of%20the%20OTHER%20workbook%20(your%20original)%3C%2FLI%3E%3CLI%3Edown%20at%20the%20bottom%20of%20the%20dialog%20box%20click%20on%20%22Create%20a%20copy%22%26nbsp%3B%20%5Bthis%20saves%20the%20original%2C%20in%20case%20something%20goes%20wrong%5D%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIf%20all%20goes%20as%20I%20think%20it%20will%2C%20that%20will%20be%20the%20solution.%20If%20it%20doesn't%20work%2C%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%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-1014153%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20have%20cells%20scattered%20in%2012%2C000%20rows%2C%20and%20you%20want%20to%20make%20them%20arranged%20in%20one%20line!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EThis%20formula%20will%20not%20help!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI'm%20not%20sure%20if%20there%20is%20a%20solution%20to%20this%20case!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015953%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015953%22%20slang%3D%22en-US%22%3E%3CP%3EBy%20the%20way%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%2C%26nbsp%3B%20I%20do%20still%20think%20that%20you%20should%20be%20able%20to%20get%20that%20other%20program%20your%20company%20uses%20to%20export%20the%20data%20in%20a%20true%20Excel%20format%2C%20rows%20with%20a%20header%20just%20as%20we're%20trying%20to%20create%20here.%20Most%20programs%20DO%20have%20that%20capability.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20the%20formulas%20I%20created%20will%20do%20the%20job%2C%20but%20even%20though%20I%20had%20fun%20creating%20those%20formulas%2C%20that%20IS%20doing%20it%20the%20hard%20way.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016003%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460453%22%20target%3D%22_blank%22%3E%40ttlj2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20similar%20solution%20but%20I%20happened%20to%20choose%20to%20use%20INDEX%20to%20return%20data%20from%20column%20B.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20INDEX(data%2C%2012*(SNum-1)%20%2B%20%7B1%2C4%2C5%2C6%2C8%7D%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20result%20is%20in%20the%20form%20of%20a%20single%20array%20formula.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EUsing%20Office%20365%20it%20was%20a%20single%20dynamic%20array%20that%20spilt%20from%20the%20formula%20in%20B2.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3ESNum%3C%2FSTRONG%3E%20is%20a%20serial%20number%2C%20I%20calculated%20using%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SEQUENCE(%20ROWS(data)%2F12%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ebut%20then%20reduced%20it%20to%20values.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016118%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016118%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20That's%20a%20great%20solution%20too%2C%20Peter.%20I%20love%20how%20Excel%20has%20multiple%20ways%20to%20solve%20a%20problem.%20Your%20use%20of%20INDEX%20is%20a%20bit%20more%20elegant%20than%20mine%20with%20INDIRECT%2C%20in%20that%20you%20needed%20no%20%22helper%22%20columns%20or%20rows.%20I%20could%20have%20written%20that%20as%20a%20single%20formula%20(in%20retrospect)%2C%20but%20was%20also%20trying%20to%20keep%20it%20understandable%20(as%20much%20as%20possible)%20for%20the%20original%20poster.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20thanks%20for%20sharing%20that.%20I%20am%20also%20loving%20the%20learning%20that%20goes%20on%20in%20sharing%20solutions%20to%20problems%20people%20bring%20to%20these%20boards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016895%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20comments.%26nbsp%3B%20I%20have%20a%20nasty%20feeling%20that%20much%20of%20the%20material%20I%20post%20is%20unintelligible%20for%20the%20end%20user%20who%20will%20approach%20problems%20bottom-up%20in%20the%20time-honoured%20spreadsheet%20manner.%26nbsp%3B%20Something%20that%20I%20find%20interesting%20is%20that%20the%20new%20dynamic%20array%20functionality%20requires%20top-down%20thinking.%26nbsp%3B%20Instead%20of%20array%20methods%20being%20methods%20of%20last%20resort%2C%20they%20can%20come%20to%20the%20fore%20and%20offer%20a%20far%20more%20structured%20approach%20to%20problem%20solving.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20drew%20attention%20to%20the%20fact%20that%20Excel%20offers%20multiple%20ways%20to%20solve%20many%20problems.%26nbsp%3B%20Since%20the%20current%20problem%20is%20essentially%20one%20of%20ETL%2C%20I%20have%20also%20included%20a%20Power%20Query%20solution%20in%20the%20attached%20workbook.%26nbsp%3B%20I%20feel%20it%20should%20be%20possible%20to%20pivot%20the%20first%20table%20without%20aggregation%20but%20my%20limited%20knowledge%20of%20PQ%20and%20the%20M%20language%20probably%20resulted%20in%20more%20steps%20than%20were%20strictly%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017003%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20here%20is%20one%20more%20Power%20Query%20variant.%20Assuming%20source%20file%20is%20in%20C%3A%5CTest%20folder%20(perhaps%20it's%20possible%20to%20connect%20directly%20to%20database%20from%20which%20source%20file%20is%20generated).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20assumption%20is%20empty%20rows%20are%20always%20empty%2C%20i.e.%20we%20don't%20have%20two%20or%20more%20phone%20numbers%20or%20email%20addresses%2C%20etc.%20for%20the%20same%20unit.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20give%20names%20to%20fields%20which%20are%20not%20specified%2C%20add%20index%20for%20each%20group%20and%20pivot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1018874%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1018874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that.%26nbsp%3B%20I%20got%20close%20but%20then%20pivoted%20on%20the%20wrong%20column%20(I%20think%20I%20selected%20the%20ID%20rather%20than%20the%20attribute%20column%20I%20wished%20to%20split%20into%20fields).%26nbsp%3B%20The%20outturn%20was%20an%20error%20so%20I%20reverted%20to%20plan%20B%20and%20used%20a%20sequence%20of%20outer%20joins%20to%20build%20the%20output%20table.%26nbsp%3B%20Data%20analysis%20is%20definitely%20not%20my%20strong%20suit%3B%20I%20can't%20remember%20ever%20having%20a%20significant%20data%20set%20to%20slice%20and%20dice%20so%20the%20skills%20come%20slowly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019143%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeter%2C%20I%20published%20it%20since%20started%20doing%20the%20task%20before%20seen%20your%20variant.%20At%20the%20same%20I%20think%20more%20variants%20in%20discussions%20the%20better%2C%20it's%20always%20useful%20to%20check%20an%20issue%20from%20different%20points%20of%20view.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20strong%20point%20is%20analysis.%20That's%20only%20bit%20more%20practice%20to%20add%20the%20word%20%22data%22%20in%20front.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ttlj2
Occasional Contributor

Hi, I would like help to merge multiple rows and columns into one continuous row. Eg - all data to be spread across A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1 etc FROM: B4,C4,A6,B6,A7,B7,B8,A9,B9,A11,B11 - Is there an easy formula to do this? Thank you

24 Replies
Highlighted

@ttlj2

 

Hi,

 

Start this formula in cell A1 then drag it to the right:

=CHOOSE(COLUMN(),$B$4,$C$4,$A$6,$B$6,$A$7,$B$7,$B$8,$A$9,$A$11,$B$11)

Align multiple non-adjacent cells into one row.png

 

Hope that helps

Thank you @Haytham Amairah - What do you mean "drag it to the right"? I have 12,000 lines of data I am trying to format across the rows. 

@ttlj2

 

I mean copying the formula to the next right cells by dragging the first cell using the Fill Handle.

Thank you. Is there to do it for all 12,000 lines or do I need to re-write the formula?

@ttlj2

 

If you have cells scattered in 12,000 rows, and you want to make them arranged in one line!
This formula will not help!

 

I'm not sure if there is a solution to this case!

Thank you anyway. Long story short is I have an account list of clients who have purchased from us. Name, Address, Suburb, Post Code, Phone Number etc etc and want to get them as mentioned above on one line across the sheet so I can then sort them by suburb. Eg - A1 = Name, B1 Address, C1 Suburb, D1 Post Code, E1 Phone Number. Too many clients to go through and cut & paste unfortunately.

@ttlj2

 

Can you provide a sample of the clients' data in an attached workbook, to see if that's possible?

Thanks @Haytham Amairah - Each black line is a new client. I have over 15,000 lines in total which is roughly 1800 + clients. Really appreciateScreen Shot 2019-11-19 at 7.32.24 am.png your help.

 

@ttlj2   IF your spreadsheet continues with exactly those same gaps (12 rows between identical items) then you can copy the formulas in the second sheet in this workbook attached.

 

I've used the INDIRECT function to increase the row references by 12 in each new row.  To the right and across the top I've created a "helper column" and a "helper row" to assist in creating the address you are directing the formula to in the INDIRECT function.

 

It's crucial, though, that you have these new records 12 rows apart.

Thank you so much. Excuse my novice question but how do I "paste" your formula in to grab my data?

Screen Shot 2019-11-19 at 7.32.24 am.png 

This is an example of what I am working with and have over 15000 similar lines.

An unsolicited editorial comment: your original sheet is almost a textbook example of how NOT to use Excel. Whoever created it (it would appear) was thinking "3x5 card" layout, and using Excel because it had rows and columns, I guess. It would have made just as much sense to use Word. Or 3x5 cards.

I say this not for your sake, ttlj2 -- you obviously are realizing you need to get records into a single row so you can sort, sift, summarize by zip code (all sorts of purposes)--but I'm writing this more to the beginners who might drop by and look at this string of notes. Beginners with Excel often come to the software with the previous paper method in mind, just using the software because it's cool, or easy to print out the records, easy to update them....but in the process make it next to impossible to really take advantage of all that Excel offers.

You're to be commended for addressing it. I hope my solution (above) makes sense to you.

unfortunately @mathetes my data is extracted from our business program that can "convert" it to Excell. Clearly this has not helped me to be able to sort into some workable format... Very frustrating.

@ttlj2Hmmm...that IS a novice question. I guess the answer for the novice is

  1. Highlight the items you want to copy
  2. Use the Edit....Copy menu
  3. select Edit...Copy from the menu
  4. go to where you want to paste
  5. select Edit...Paste

 

There are keyboard shortcuts. If you use Word, they're the same. Ctrl C to copy, Ctrl V  to paste. In Mac environment, Command-C, Command-V.  

 

It might be easier for you to post an edited copy of your spreadsheet (not just an image, an actual spreadsheet)...include about five to ten of the records, falsifying the names and any other identifiable info. Then I can put the formulas into place and get it started.

@ttlj2  That might actually be a blessing. If it's extracted (exported) from another program, there may be a way--I'd be surprised if there isn't--to export specifically in an excel table format.  See if there's an option for exporting to XLS or CSV files.......

 

What you've got is more of an export to print, and it does a good job of that; it just so happens that Excel can open it, but it (as you're finding) ain't useful.

 

So look for an export option to XLS,  XLSX, or CSV.

 

And if that's not available, if you can post a section of it (cleaned so as not to reveal confidential or personal info)  I can get the formulas started for you.

@mathetes Thank you

OK,@ttlj2  I've done the first ten records and copied the formula that can (or should be able to) handle up to nearly 2,000.

 

What I suggest you do is this. First, make a copy of your original file.

Second, in one of your copies, rename the tab that contains the records in your original workbook...rename it to "RawData"

Third, copy my "Converter" tab to that workbook.

To do this last,

  1. have both workbooks open
  2. left click on "Converter"
  3. select "Move or Copy"
  4. pick the name of the OTHER workbook (your original)
  5. down at the bottom of the dialog box click on "Create a copy"  [this saves the original, in case something goes wrong]

If all goes as I think it will, that will be the solution. If it doesn't work, let me know.

 

 

 

By the way, @ttlj2,  I do still think that you should be able to get that other program your company uses to export the data in a true Excel format, rows with a header just as we're trying to create here. Most programs DO have that capability.

 

I know the formulas I created will do the job, but even though I had fun creating those formulas, that IS doing it the hard way.

@ttlj2 

A similar solution but I happened to choose to use INDEX to return data from column B.

= INDEX(data, 12*(SNum-1) + {1,4,5,6,8} )

 

The result is in the form of a single array formula.

Using Office 365 it was a single dynamic array that spilt from the formula in B2.

SNum is a serial number, I calculated using 

= SEQUENCE( ROWS(data)/12 )

but then reduced it to values.

@Peter Bartholomew   That's a great solution too, Peter. I love how Excel has multiple ways to solve a problem. Your use of INDEX is a bit more elegant than mine with INDIRECT, in that you needed no "helper" columns or rows. I could have written that as a single formula (in retrospect), but was also trying to keep it understandable (as much as possible) for the original poster.....

 

Anyway, thanks for sharing that. I am also loving the learning that goes on in sharing solutions to problems people bring to these boards.

@mathetes 

Thank you for your comments.  I have a nasty feeling that much of the material I post is unintelligible for the end user who will approach problems bottom-up in the time-honoured spreadsheet manner.  Something that I find interesting is that the new dynamic array functionality requires top-down thinking.  Instead of array methods being methods of last resort, they can come to the fore and offer a far more structured approach to problem solving.

 

You drew attention to the fact that Excel offers multiple ways to solve many problems.  Since the current problem is essentially one of ETL, I have also included a Power Query solution in the attached workbook.  I feel it should be possible to pivot the first table without aggregation but my limited knowledge of PQ and the M language probably resulted in more steps than were strictly needed.

@Peter Bartholomew 

And here is one more Power Query variant. Assuming source file is in C:\Test folder (perhaps it's possible to connect directly to database from which source file is generated).

 

Another assumption is empty rows are always empty, i.e. we don't have two or more phone numbers or email addresses, etc. for the same unit.

 

After that give names to fields which are not specified, add index for each group and pivot.

@Sergei Baklan 

Thanks for that.  I got close but then pivoted on the wrong column (I think I selected the ID rather than the attribute column I wished to split into fields).  The outturn was an error so I reverted to plan B and used a sequence of outer joins to build the output table.  Data analysis is definitely not my strong suit; I can't remember ever having a significant data set to slice and dice so the skills come slowly.

@Peter Bartholomew 

Peter, I published it since started doing the task before seen your variant. At the same I think more variants in discussions the better, it's always useful to check an issue from different points of view.

 

Your strong point is analysis. That's only bit more practice to add the word "data" in front.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies