SOLVED
Home

multiple cells into 1

%3CLINGO-SUB%20id%3D%22lingo-sub-900741%22%20slang%3D%22en-US%22%3Emultiple%20cells%20into%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900741%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20how%20much%20dirt%20we%20are%20going%20to%20have%20to%20remove%20per%20structure.%20A%20structure%20consists%20of%202%20holes%20with%20different%20amounts%20of%20soil%20being%20removed%20from%20each%20hole.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%20I%20need%20structure%203%20that%20has%20a%20right%20hole%20with%204.5%20cy%20of%20soil%20to%20be%20removed%20and%20a%20left%20hole%20with%203%20cy%20of%20soil%20to%20be%20removed.%20I%20want%20it%20so%20that%20a%20formula%20that%20I%20can%20easily%20transfer%20will%20combine%20the%202%20lines%20into%20one%20quantity%20of%20soil%20that%20needs%20to%20be%20removed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20adding%20them%2C%20using%20summation%2C%20breaking%20up%20the%20numbers%2C%20and%20using%20the%20fill%20handle%20but%20it%20doesn't%20pick%20up%20on%20the%20pattern.%20Any%20ideas%20on%20how%20to%20do%20this%20efficiently%3F%20Thank%20you%20very%20much.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-900741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ecombining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFill%20handle%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900934%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20cells%20into%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422466%22%20target%3D%22_blank%22%3E%40rwb20141380%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20copy%20a%20formula%20down%2C%20a%20relative%20cell%20reference%20like%20%3DA1%20will%20be%20incremented%20by%20one%20row%20only%2C%20to%20return%20%3DA2.%20Excel%20does%20not%20recognize%20a%20pattern%20and%20will%20not%20apply%20a%20pattern.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20several%20other%20things%20you%20could%20do.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESplit%20the%20structure%20number%20into%20two%20columns%2C%20so%20that%20one%20column%20has%20only%20the%20number%20and%20the%20other%20one%20has%20the%20text.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20now%20you%20can%20build%20a%20pivot%20table%20that%20totals%20all%20values%20for%20the%20same%20structure%20number%3C%2FP%3E%0A%3CP%3E-%20or%20use%20Sumif%20functions%20or%20the%20new%20Dynamic%20Array%20formulas%20currently%20only%20available%20in%20Office%20365%20Insider.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902188%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20cells%20into%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%20You%20are%20a%20saint.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
rwb20141380
New Contributor

I am trying to figure out how much dirt we are going to have to remove per structure. A structure consists of 2 holes with different amounts of soil being removed from each hole.

 

For example: I need structure 3 that has a right hole with 4.5 cy of soil to be removed and a left hole with 3 cy of soil to be removed. I want it so that a formula that I can easily transfer will combine the 2 lines into one quantity of soil that needs to be removed.

 

I have tried adding them, using summation, breaking up the numbers, and using the fill handle but it doesn't pick up on the pattern. Any ideas on how to do this efficiently? Thank you very much.

 
 
2 Replies
Solution

@rwb20141380 

 

When you copy a formula down, a relative cell reference like =A1 will be incremented by one row only, to return =A2. Excel does not recognize a pattern and will not apply a pattern.

 

There are several other things you could do.

 

Split the structure number into two columns, so that one column has only the number and the other one has the text. 

- now you can build a pivot table that totals all values for the same structure number

- or use Sumif functions or the new Dynamic Array formulas currently only available in Office 365 Insider.

@Ingeborg Hawighorst  You are a saint. Thank you

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies