I have a bunch of addresses that are listed. my problem is they are listed with the street on one row and the city state and zip on the one below it, how can I combine these two the same row? sample attached

2. ## Re: Combine two rows into one - Address (help please)

Hi.

Try this formula:

``Please Login or Register  to view this content.``

3. ## Re: Combine two rows into one - Address (help please)

Hi,

In B1 copied down

=IF(MOD(ROW(),3)=1,A1&" "&A2,"")

4. ## Re: Combine two rows into one - Address (help please)

=IF(MOD(ROW(),3)=1,A1&" "&A2,"") filled down
snap i win
mind you
=IF(MOD(ROWS(\$A\$1:A1),3)=1,A1&" "&A2,"") maybe better

5. ## Re: Combine two rows into one - Address (help please)

yall are awesome!

6. ## Re: Combine two rows into one - Address (help please)

yall are awesome!

7. ## Re: Combine two rows into one - Address (help please)

@martindwilson

Hi!!

The formulas work perfectly.

I am just trying to understand why did you not just use the concatenate function??

Please clarify if there is any specific reason?

Thanking you,

Deep

8. ## Re: Combine two rows into one - Address (help please)

Hi,

I've never understood why anyone would want to write the word 'Concatenate' every time when you can just use the & instead. Now if Concatenate did really concatenate a range of cells, e.g if Concatenate(A1:D1) really did result in say "ABCD") where A1=A, B1=B, C1=C and D1=D..etc, then that would be a different matter.

9. ## Re: Combine two rows into one - Address (help please)

i second that!
but i suppose if you use insert function rather than type it in its then just mouse clicks on each cell and could be easier.

10. ## Re: Combine two rows into one - Address (help please)

Just 1 More Query Regarding Concatenation -

Assume if I have 500 Rows the "A" Column (I,e. A1 - A500) with Numbers in all of them and I need to concatenate all the cells in B2, is there a simpler way to do the concatenation rather than using concatenate and click every single cell.

Thanking You,

Deep

12. ## Re: Combine two rows into one - Address (help please)

Thank You,

The Above Code Helped.

I am attaching herewith a Sheet where we are trying to create New Part Codes in our company.

Column A - Main Code
Column B - Sub Code
Column C - Serial Number
Column D - Concatenation of Above 3

The Problem here is, every time there is a change in the "Sub Code" the Serial Number should Start from 0001 and further.

Is there a way I can achieve that??

Thank You,

Deep

13. ## Re: Combine two rows into one - Address (help please)

in c5 filled down
=TEXT(SUMPRODUCT((\$A\$5:A5=A5)*(\$B\$5:B5=B5)),"0000")
or you could do away with col c altogether
=CONCATENATE(A5,B5,TEXT(SUMPRODUCT((\$A\$5:A5=A5)*(\$B\$5:B5=B5)),"0000"))

14. ## Re: Combine two rows into one - Address (help please)

@martindwilson

Thats Amazing.. Thank You.

Can you please break it down slightly so that I can understand it a bit.

Thank You,

Deep

15. ## Re: Combine two rows into one - Address (help please)

Hi, msexcelathome.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1