As i extract report from MIS, It has multiple row merged together.
When un-merged, It creates additional spaces vertically
Need Output in this one liner format using the Actual extract
This Ref # spans over 7 rows, all records have different number of rows merged together.
excel.jpg
kindly help, thanks !
Select entire sheet. Click on column width "A".
Go to "Alignment > click on "Middle Align".
Sorry, didnt work.
My requirement is to concatenate contents of cells in one cell for one reference number, count of cells varies for each reference number.
I am unable to upload excel file here, can you please guide how to attach excel file ?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
+ Attachment
I'm using Excel 2016 and when I un-merge cell B3, it places the entire cell contents onto one line.
ALiGw, i am trying to use "IF"formula as well but cant seem to build one, as the count of rows varies for each reference.
Kindly guide how to concatenate contents from different cells vertically (without a pattern)..
Do not un-merge the cells. Type this into C3 and copy down:
=IF(ISBLANK(B3),"",B3)
Then select column C and copy paste values over the top. Finally delete column B.
this will help me in explaining the requirement.. please see. thanks !
to rephrase it, its a Concatenation problem..
That is a completely different scenario! Presumably you are looking for the alternative solution listed?
i guess these are same things, i presume the problem can be either be solved using merge and un-merge or preferably IF+Concatenate ...
cant seem to be formulating the second option... :/ please helpthanks !
Please answer the question: which of the two outputs you have given is the one you want? Are you likely to shift the goalposts again? In other words, is there anything else about this scenario that we ought to know now? Remember the solution offered will be tailored to the information and data you provide.
Sorry, the goal is the same, i couldn't explain the exact requirement.
Basically, i need a concatenated output (in 3rd column) for the same reference/serial numbers (1st column) using the data in cells of 2nd column.
Hope this will help.
anyone please .....
Go to "B3" Cell > Right Click > select "Format Cell" > Untick merge Cell & Tick Wrap Text (Refer Attacment) > Click "OK" > Click on Top of the "B" Column.Originally Posted by TheDtox
Refer attach file. Hope your problem resolved.Sorry, the goal is the same, i couldn't explain the exact requirement.
Basically, i need a concatenated output (in 3rd column) for the same reference/serial numbers (1st column) using the data in cells of 2nd column.
Hope this will help.
I don't think it is. The OP is looking for an automated solution: with yours, each concatenation statement has to be set up manually.
As per "TheDtox" Posting 10-19-2016, 09:24 AM : Concatenated Output is same which i have using formula >
c3 =CONCATENATE(B3," ",B4," ",B5," ",B6)
c7 =CONCATENATE(B7," ",B8," ",B9," ",B10," ",B11)
c12 =CONCATENATE(B12," ",B13," ",B14)
c15 =CONCATENATE(B15," ",B16," ",B17," ",B18)
Yes, the results are correct, but it is NOT the process that TheDtox has requested. This is a MANUAL solution, not an AUTOMATED one: you need to provide a formula that can be entered in C3 and dragged down as far as is required.
From post #7:
ALiGw, i am trying to use "IF"formula as well but cant seem to build one, as the count of rows varies for each reference.
Last edited by AliGW; 10-26-2016 at 08:12 AM.
Hello
If you're willing to use VBA, a UDF (user defined function) could do this with the help of COUNTIF. For example, the UDF would be something like:
And the sheet formula:
Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
Does this help?
DBY
Last edited by DBY; 10-26-2016 at 08:26 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks