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
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
Hi.
Try this formula:
Please Login or Register to view this content.
Click *, if my suggestion helps you. Have a good day!!
Hi,
In B1 copied down
=IF(MOD(ROW(),3)=1,A1&" "&A2,"")
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
for your example in B1
=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
Last edited by martindwilson; 03-15-2013 at 12:11 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
yall are awesome!
yall are awesome!
@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
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.
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.
Last edited by martindwilson; 03-18-2013 at 05:31 AM.
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
Refer the below link:-
http://www.excelforum.com/tips-and-t...geravatar.html
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
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??
Sheet Attached for your reference.
Thank You,
Deep
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"))
@martindwilson
Thats Amazing.. Thank You.
Can you please break it down slightly so that I can understand it a bit.
Thank You,
Deep
Hi, msexcelathome.
Please read forum rule:
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
Click (*) if you received helpful response.
Regards,
David
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks