I'm not an advanced excel user but I have a query about it. I have a set of values in cells column B and there are random empty cells between one value in column B to the next value in a cell in column B. What I'd like to do is for every value present in a cell in column B say B1, I'd like to merge H2 and H3, two two further cells in column H into a cell G1 and delete the contents of H2 and H3. How can I use a formula to do this? P.S, I need to do this for about 50 pages in an excel document. Thanks.

  • Can you clarify how the empty cells in B figure in? Are they always single empty cells or can you have several in a row? What's the relationship between the cells you want from col H and the cell you identify in col B? Is it always H2 & H3, or the next two H cells following the B cell (or more than two if there are multiple empty cells in B)? How do the G cells relate to the B cells (always the one G cell on the same row)? Are the G cells initially empty or are you adding H content to what's already there? (cont'd) – fixer1234 Sep 12 '15 at 20:54
  • Does "merging" the H cells mean concatenating the contents, adding their numerical values, or something else? Will all H values end up in col G or just ones related to specific B cells (are you talking about deleting the column or just selected cells)? This would be easier to understand with a screenshot (post a link to it at an image sharing site like imgur.com). Is it necessary to actually delete the H cells or can you just hide them? Deletion will require a VBA solution, hiding could be done with conditional formatting)? – fixer1234 Sep 12 '15 at 20:54
  • 2
    Essentially a duplicate of How can I execute this function using VBA in Excel? (whose author was clearer and tried something). – Scott Sep 12 '15 at 22:06
  • 2
    OK, something strange is going on. What are the odds that two people, both new members for 2 days, will ask almost identical oddball questions an hour apart? Both questions are apparently trying to solve the same problem. Is this some kind of contest? Homework? It will be easier for people to help if they understand the problem this seeks to solve. – fixer1234 Sep 13 '15 at 2:51

use a formula in col G along the lines of


then, after checking the values in G are correct, copy the Column and paste values; then delete col H

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.