Of all the hundreds of formulas & thousands of features in Excel, INDEX() would rank somewhere in the top 5 for me. It is a versatile, powerful, simple & smart formula. Although it looks plain, it can make huge changes to the way you analyze data, calculate numbers and present them. It is so important that, whenever I teach (live or online), I usually dedicate 25% of teaching time to INDEX().
Today lets get cozy. Lets start a fling (a very long one). Lets do something that will make you smart, happy and relaxed.
Understanding INDEX formula
In simple terms, INDEX formula gives us value or the reference to a value from within a table or range.
While this may sound trivial, once you realize what INDEX can do, you would be madly in love with it.
Few sample uses of INDEX
1. Lets say you are the star fleet commander of planet zorg. And you are looking at a list of your fleet in Excel (even in other planets they use Excel to manage data). And you want to get the name of 8th item in the list.
INDEX to rescue. Write =INDEX(list, 8)
2. Now, you want to know the captain of this 8th ship, which is in 3rd column. You guessed right, again we can use INDEX,
=INDEX(list, 8,3)
Syntax of INDEX formula
INDEX has 2 syntaxes.
1. INDEX(range or table, row number, column number)
This will give you the value or reference from given range at given row & column numbers.
2. INDEX(range, row number, column number, area number)
This will give you the value or reference from specified area at given row & column numbers.
It may be difficult to understand how these work from the syntax definition. Read on and everything will be clear.
7 reasons why INDEX is an awesome companion
Whether you are in planet zorg managing dozens of star fleet or you are in planet earth managing a list of vendors, chances are you are wrestling everyday with data, pleasing a handful of managers (and clients), delivering like a rock star all while having fun. That is why you should partner with INDEX. It can make you look smart, resourceful and fast, without compromising your existing relationship with another human being.
Data used in these examples
For all these examples (except #6), we will use below data. It is in the table named sf.
Reason 1: Get nth item from a list
You already saw this in action. INDEX formula is great for getting nth item from a list of values. You simply write =INDEX(list, n)
Reason 2: Get the value at intersection of given row & column
Again, you saw this example. INDEX formula can take a table (or range) and give you the value at nth row, mth column. Like this =INDEX(table, n, m)
Reason 3: Get entire row or column from a table
For some reason you want to have the entire or column from a table. A good example is you are analyzing star fleet ages and you want to calculate average age of all ships.
You can write =AVERAGE(age column)
or you can also use INDEX to generate the age column for you. Assuming the fleet table is named sf and age is in column 7
write =AVERAGE(INDEX(sf, ,7))
Notice empty value for ROW number. When you pass empty or 0 value to either row or column, INDEX will return entire row or column.
Likewise, if you want an entire row, you can pass either empty or 0 value for column parameter.
Reason 4: Use it to lookup left
By now you know that VLOOKUP() cannot fetch values from columns to left. It does not matter if the person looking up is the star fleet commander.
But INDEX along with MATCH can fix this problem.
Lets say you want to know which ship has maximum capacity.
- First you find what is the maximum capacity =MAX(sf[Capacity (000s tons)])
- Then you find position of of this capacity in all values =MATCH(max_capacity, sf[Capacity (000s tons)],0)
- Now, extract the corresponding ship name =INDEX(sf[Ship Name], max_capacity_position)
Or in one line, the formula becomes
=INDEX(sf[Ship Name], MATCH( MAX(sf[Capacity (000s tons)]), sf[Capacity (000s tons)], 0))
For more tips read using INDEX + MATCH combination
Reason 5: Create dynamic ranges
So far, your reaction to INDEX’s prowess might be ‘meh!’. And that is understandable. You are of course star fleet commander and it is difficult to please you. But don’t break-up with INDEX yet.
You see, the true power of INDEX lies in its nature. While you may think INDEX is returning a value, the reality is, INDEX returns a reference to the cell containing value.
So this means, a formula like =INDEX(list, 8) looks like it is giving 8th value in list.
But it is really giving a reference to 8th cell.
Since the result of INDEX is a reference, we can use INDEX in any place where we need to have a reference.
Sounds confusing?
For example, to sum up a list of values in range A1:A10, we write =SUM(A1:A10)
Now, in that formula, both A1 and A10 are references.
Since INDEX gives a reference, we can replace either (or both) A1 & A10 with INDEX formula and it still works.
so =SUM(A1 : INDEX(A1:A50,10))
will give the same result as =SUM(A1:A10)
Although the INDEX route appears overly complicated, it has other applications.
Example 1: SUM of staff in first x ships
Lets say you want to sum up staff in first ‘x’ ships in the sf table.
Since ‘x’ changes from time to time, you want a dynamic range that starts from first ship and goes up to xth ship.
Assuming ‘x’ value is in cell M1 and first ship’s staff is in cell G3,
=SUM(G3:INDEX(sf[Staff count], M1))
will give the desired result.
Example 2: A named range that refers to all ship names in column A
Many times you do not know how much data you have. Even star fleet commanders are left in dark. Lets say you are building a new ship tracking spreadsheet. Since your fleet is ever growing, you do not want to constantly update all formulas to refer to correct ranges.
For example, the ship names are in column A, from A1 to An. And you want to create a named range that points to all ships so that you can use this name elsewhere.
If you define the lstShips =A1:A10, then after you add 11th ship, you must edit this name. And you hate repetitive work.
One solution is to use OFFSET formula to define the dynamic range,
like =OFFSET(A1, 0,0, COUNTA(A:A),1)
While this works ok, since OFFSET is volatile function, it will recalculate every time something changes in your workbook. Even when someone replaces a bolt on landing gear of USS Enterprise.
This will eventually make your workbook slow.
That is where INDEX comes.
You see, INDEX is a non-volatile function*.
So you can create lstShips that points to,
=A1: INDEX(A:A, COUNTA(A:A))
*Even though INDEX is non-volatile, since we are using it in defining a range reference, Excel recalculates the lstShips every time you open the file. (reference).
Reason 6: Get any 1 range from a list of ranges
INDEX has another powerful use. You can get any one range from many ranges using INDEX.
Since you are a successful, smart & resourceful star fleet commander, you got promoted. Now you manage fleet of several planets.
And you have similar ship detail tables for each planet in a workbook. And you want to calculate average age of any planet’s ships with just one formula.
Again INDEX to rescue.
Assuming you have 3 different tables – planet1, planet2, planet3
and selected planet number is in cell C1,
write =AVERAGE(INDEX((planet1,planet2,planet3),,,C1))
The reference (planet1,planet2,planet3) will point to all data and C1 will tell INDEX which planet’s data to use.
Pretty nifty eh?!?
Reason 7: INDEX can process arrays
INDEX can naturally process arrays of data (without entering CTRL+Shift+Enter).
For example you want to find out how much staff is in the ships whose captain’s name starts with “R”.
write =SUM(INDEX((LEFT(sf[Captain],1)=“r”)*(sf[Staff count]),0))
Although LEFT(sf[Captain],1)=”r” and sf[Staff count] produce arrays, since INDEX can process arrays automatically, the result comes without CTRL+Shift+Enter
Where as if you use SUM alone =SUM((LEFT(sf[Captain],1)=”r”)*(sf[Staff count])) you have to press CTRL+Shift+Enter to get correct results.
Other formulas: SUMPRODUCT & MATCH too can process arrays automatically.
Download Example Workbook & Get close with INDEX
Since you are going to ask, “I want to spend sometime alone with INDEX in my cubicle right now!”, I made an example workbook. It explains all these powerful uses of INDEX. Go ahead and download it.
Get busy with INDEX.
How to use INDEX in Excel – Video
In this video, learn how to use INDEX formula in Excel with many real-world examples. You can also watch it here.
Why do you love INDEX?
I love INDEX(). If we get a dog, I am going to call her INDEX.
Updated on Feb 2024: We did get a dog, but we call her Excel!
That is how much I love the formula. Almost all my dashboards, complex workbooks and anything that seems magical will have a fair dose of INDEX formulas.
What about you? Do you use INDEX formula often? What are the reasons you love it? Please share your tips, usages and ideas on INDEX using comments.
Learn more about INDEX & other such lovely things in Excel
If you are whistling uncontrollably after reading so far, you are in for a real treat. Check out below articles to become awesome.
- INDEX + MATCH Combination
- Introduction to SUMIFS formula
- Dynamic Array formulas in Excel (especially FILTER) is a good alternative to INDEX
- XLOOKUP formula can do much more than INDEX
- More examples of advanced Excel formulas & INDEX
83 Responses to “Merge Cells without Losing Data [Quick Tip]”
Yes, but with your VBA sub, you end up with a delim which should be trimmed.
Unfortunately, the company I work for is using Excel 2003 and the function will not work.
@Gerdami... I know the mistake in code. I left in on as when you concatenate such text, 99% of time you are doing it for cosmetic reasons, so an extra space wouldnt hurt + I wanted to keep the code easy to understand for our readers.
@Steve... I am not sure if that is the case. I just tested the code in Excel 2003 and it works fine. May be when you copied it, all the quotation marks ' " are replaced by wrong characters?
Chandoo, why do you provide screenshots with Excel 2007 and still continue to use Excel 2003 when Excel 2010 is out 🙂
I saw the article about you on msn. It was very inspiring. Thats how I came to know about your blog. You should also check out my blog sometime though I'm just a beginner.
Thanks.
Don't worry Chandoo, thanks for sharing this.
One more gem from Chandoo! Thanks for sharing it 🙂
Wow, I can't remember coming across this Fill>Justify tip before. Nice one. I checked Excel 2003, 2007, 2010, and Mac versions 2008, and 2011 to see if it works and it did. Amazing!
I did have to replace ‘ with ' to designate comments, and
” “ with " " in the Const declaration before my compiler would okay the code.
I did not of Fill/justify before. I noticed it could also split wrapped up text in a cell in multiple cells.
It's much different than setting horizontal text alignment to "Justify" in the format cell dialog.
Personnaly, assuming the text is in column 1, I would use =A1&" "&A2. Copy the formula down to the last row than copy/paste value the result.
Sebastien
This is a wonderful tip! Too bad it only works in Excel 2007! Keep them coming.
Don't recollect the Fill->Justify earlier and to have wasted all that time..........grrrrrrrrrrrrrr
Thanks!
Face this problem many a times.
Many of us face 'another' problem. It's actually lose and not loose. Funny how often this mistake is committed.
cheers,
PSL
Hi Chandoo
Would you consider covering the general topic of converting 2003 VBA code to 2007/10 in a future newsletter - ie what is the process of converting 2003 macros and situations like this?
Cheers
John
@PSL: Oops, I didnt realize the mistake in spelling. Fixed it now. Sadly, the url will retain an extra o.
@Godwin: Because I have all 3 versions installed on my comp!
@Sebastian: I used to the same thing (write =a1&" "&a2 and drag) a while ago. Then I ended up writing a small UDF called as CONCAT that accepts ranges as input and concatenates text in that. It is such a timesaver. Get it here: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
@Ninad, Prakash: Thank you. I am happy you like this.
@Kamarrah: It works in Excel 2003 too.
@John: I think all macros written in 2003 work in 2007 without any changes in behavior. I may be wrong. I am not an expert in macros, but I will try to put-together an article on what you asked.
@Chandoo,
Below is your code modified to remove the loop which concatenates the output text together (note that this method does not produce a trailing delimiter in the output string like your code does)...
Sub JoinAndMerge()
' Joins all the content in selected cells
' and puts the resulting text in top most cell
' then merges all cells
Const Delimiter = " "
On Error Resume Next
With Selection
.Item(1).Value = Join(WorksheetFunction.Transpose(Selection), Delimiter)
.Item(2).Resize(Selection.Count - 1).Clear
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub
I have a question though... I left it in (because you included it), but why are you setting the WrapText property to True?
@Rick... Good modification. I tried to use Join but failed several times. It didnt occur to me that I need to transpose the data. Thanks for sharing it.
I used the WrapText option so that if the merged text becomes too large, it would wrap nicely inside the cell. I am not sure if without that option the merged content would be visible completely. What do you think?
Oops, it does work in 2003
Don't worry Steve, the problem is with the strange quotes ‘ ” “ displayed on this page.
@Chandoo,
The reason I asked why were you setting the the WrapText property to True was because of this instruction you gave above...
2. Adjust the column width so that you can fit all
contents in one cell. (basically make it wide enough)
If this instruction is followed, then there would be no need to wrap the text. By the way, we can modify this code to handle merging across a single row instead of down a column...
Sub JoinAndMerge()
Const Delimiter = " "
On Error Resume Next
With Selection
.Item(1).Value = Join(WorksheetFunction.Index(Selection.Value, 1, 0), Delimiter)
.Item(2).Resize(1, Selection.Count - 1).Clear
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlGeneral
.WrapText = True
End With
End Sub
And, if we want to generalize the code to handle either a selection down a column or across a row automatically, then this code will do that...
Sub JoinAndMerge()
Const Delimiter = " "
On Error Resume Next
With Selection
If Selection.Rows.Count > 1 Then
.Item(1).Value = Join(WorksheetFunction.Transpose(Selection), Delimiter)
.Item(2).Resize(Selection.Count - 1).Clear
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
Else
.Item(1).Value = Join(WorksheetFunction.Index(Selection.Value, 1, 0), Delimiter)
.Item(2).Resize(1, Selection.Count - 1).Clear
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlGeneral
End If
.Merge
.WrapText = True
End With
End Sub
Note that I still retained the WrapText property setting statement (in both of these routines).
Hi Chandoo,
In your latest comment on merging cells, what are the pros and cons of using concatenate command instead of the VBA?
Also, I found a relatively simple link to merge text in cells--check this out
http://www.contextures.com/xlCombine01.html
I got to work! Yeah Chandoo thanks.
Hello Chandoo.
There is a old trick to do that.
If you have the data in a1:a5, select b1:b5 and merge the cells, copy b1:b5, select a1:a5 and Paste Special - Format then you have a1:a5 merged but the individual data in a1:a5 still remains there. If you split the cells a1:a5 you see the individual data again, even with the cells merged you can refers one of them individually.
Kind Regards. César.
Hi Chandoo,
Thank you and I need opposite action to this
If a cell contains multiople data (abc123, def456) seperated by coma/space needs to be splited into new rows below (a new row should be inserted below and the data should be populated) could you suggest...
Cheers, Chandru...
This is the macro I use.
It accounts for columns and rows and will work with normal formulas. Selection must be contiguous. (but that's a given, since we're merging the cells)
Sub MergeCells()
Dim result As String
For Each cell In Selection.Cells
If Not cell.Value = vbNullString Then
result = result & Trim(cell.Value) & " "
End If
Next
Application.CutCopyMode = False
With Selection
.Clear
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
End With
Selection.Cells(1, 1).Value = result
End Sub
Hi chandoo,
I know one formual to merge the cell in excel woth out losing data, but dnt know how to update here, Pls guide me.
And thanks a lot beca i am very curious to know abt xcel and here ia m learning so many new things.
Merge Cells without Losing Data :
I make macros for this :
Sub lastrow()
Dim lastrow
lastrow = Range("b" & Rows.Count).End(xlUp).Row + 1
Range("B" & lastrow) = Range("A6") + Range("A7")============= Range Can be multiple like range ("A5"), Range("A8") Etc.
End Sub
its very simple to merge cells you can watch it on the next tutrial:
http://www.thebestdata.com/zoom.aspx?menutype=1&auto=2189
Merge cells in Excel 2010
Here is one for you!! I'm still trying to figure out how to make it work.
Just take say 400 rows and 14 to 16 narrow columns as if you were making a bar chart, then merge every second column vertically and fill it in with color. Some of the verticals can be split two or three times vertically. The entire page will probably freeze before you get the columns complete.
Now try and work out a solution the page freeze.
Help! I've used Fill/Justify for years. Just "upgraded" to Excel 2010 for Windows. Cant find Fill Justify. Help!! Microsoft Help is worthless...
Thanks!
@Tim... It is still there. In home riboon, under Fill Option. Press ALT +hfij to access it.
Chandoo, How did you get this ? what made you increase width and select all cells and click fill > justify?? Do you have tie up with Microsoft developers 😉
[...] Merge Several Cells without Loosing Data [macros] Spread some love,It makes you awesome! Tweet [...]
Dear Chandoo,
Many Thanks for all the tips & tricks... i'm learning a lot about excel through this..
just one quick question:
how do you show the steps as a gif animation image? do you use any software? if yes, which one? even i'm curious to create some gif animations which i can show in my ppt 🙂
thanks a ton mate... & wish you & ur family a very happy diwali... 😀
regards
raghu
@Raghu
The aimated images are Animated GIF files
I believe that Chandoo uses TechSmith's, Camtasia Pro screen capture software, although there are a number of screen capture utilities that do the same thing.
What if I want to merge the cells but keep the paragraph formating so instead of one cell with "big fat cell with lots of text"
"big
fat
cell
with
lots
of
text"
Thanks
@Megan
a very simple modification to Chandoo's code will do the trick
.
Sub JoinAndMerge()Dim outputText As String
delim = Chr(10) 'This is the only change
On Error Resume Next
For Each cell In Selection
outputText = outputText & cell.Value & delim
Next cell
With Selection
.Clear
.Cells(1).Value = outputText
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub
I cannot get that to work. Is ther some kinda code for inserting a new paragraph kinda like using alt enter when your typing in a cell?
gotta use delim = vbLf
The macro you provide is great and will save me a lot of frustration, but for some reason it seems to strip all of the formatting from the text (font, font size, font color) in the newly merged cell. Is it possible to modify it so that it maintains the original formatting of the cell?
Hi folks,
Pls dont worry all about this...
just omit all the above comments..
-----------------
Just copy all the cells content whichever you need and paste it in notepad,wordpad,or msword and copy all those data from the wordfile and click inside the required cell in excel and paste it...
___________________________
thats it.... All the best
Great help! Keep Rocking (;
Hi chandoo,
I have doubt in excel VBA macro code. can u help me with it.My problem is:
I have multiple vertical cells with values in alternate (leaving 1 cell gap between 2 values)positions eg:
Date
A1: 02/Nov/2011
A2:
A3: 04/Oct/2011
A4:
A5: 12/Oct/2011
A6:
A7: 25/May/2011
21/Oct/2011
now please let me know how do I copy it to the other workbook vertically(continuously) without a gap of 1 cell inbetween 2 values.
The result is supposed to be this:
Date
A1: 02/Nov/2011
A2: 04/Oct/2011
A3: 12/Oct/2011
A4: 25/May/2011
A5: 21/Oct/2011
In sheet 1, select all the cells A1:A10,
press F5
Select Special
Choose Constants
Click ok
Press CTRL+C
Now go to Sheet 2 and Paste
Why not just a simple formula
in Date!A1 put
`=OFFSET(Sheet1!$A$1,2*ROW()-2,)`
thanks for the suggession,
but im trying it with vba. Can u please help me in this regard
hi chandoo,
this is my new problem, i solved the old one.
first i want to search for a string in an existing workbook, if it is found then i need to copy the range below it(till the data is present) into a new workbook using VBA.
Hi, I have a question related to this thread. I have a need to merge columns of data into one cell, with no data loss, but need two additional features: first is to comma seprate the contents of each of the merged cells once they are in the merged cell. second is to do this for individual rows, but whilst selecting multiplw rows - I mean only merge per row into one cell. for example, i want to be able to run the macro by selecting all rows in my worksheet, but have columns merged per row, not all rows and columns merged into one cell in teh top left of the sheet. ie I want a finished sheet of one column with the same number of rows but the columns from each row meged into the first cell of each row.
the closest i have come is with a previous post:
1. Nikki says:
December 20, 2010 at 8:36 pm
This is the macro I use.
It accounts for columns and rows and will work with normal formulas. Selection must be contiguous. (but that’s a given, since we’re merging the cells)
Sub MergeCells()
Dim result As String
For Each cell In Selection.Cells
If Not cell.Value = vbNullString Then
result = result & Trim(cell.Value) & ” ”
End If
Next
Application.CutCopyMode = False
With Selection
.Clear
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
End With
Selection.Cells(1, 1).Value = result
End Sub
this macro merges all selected columns and rows into the one cell, I want to be able to select multiple columns and rows, but only have row by row merged.
Additionally, ideally as the merge is completed i would like to insert a comma between each of the merged cells contents, once it is merged.
hope I have explained this ok?
any help is much appreciated!
Did you ever solve this?? I need to do the same thing and am lost.
@Frustratedguy
Have you tried this: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Also check the comments as there are lots of extensions to the basic post in the comments
If that doesn't help can you post or email me a sample file?
Hi Chandoo,
Can you tell me how to merge columns without losing the data in the format given below:
column A column b
row 1: abcd xyz
required format:
column A
row 1: abcd xyz
It would save a great amount of my time if i could get a solution to this!
Hi Jo Saave,
you can get the data in col a & col b concatenated in col c. if you need to get the merged data back in col a then you may have to copy paste.
the formula (in col A1) would be =A1&B1"
if you need the space between char in A1 & B1 then it would be
=A1&" "&B1"
hope this helps
regards
raghu
Brilliant!
The Macro works fine and good once the wrong characters are replaced with the correct quotation marks. Thanks, but when the length of the cell is high then values in the two cells will merge in single line. For this I have move to the end of each value and give alt+ enter to move the second value to the next line. Then how to resolve this?
Hi Chandoo -
Thanks for this!
Quick question: I have one column with a list of about 1000 names. Each row is a different name but some rows belong to one family. I am trying to separate each family. In order to do this, I am using your JoinAndMerge() macro. Essentially, I am merging the rows that belong to one family so that they become one cell. I will then use this and use Avery wizard (is that the easiest way to do it?) to print off the names on a avery sticker sheet.
However, when I merge the rows of names, I still need them to be in separate lines. I could do it manually with the char(10) function, but I imagine I could edit your macro a little.
What would you advise here?
Thanks a lot!
Mana
@Mana
I definitely would advise not to use Merge
I would add a new helper/ assistant column called Family
The add a formula to that to add family as appropriate
Hi All,
Any body can sujjest a VBA code for this: use logical condition in other words if column 1 with same information in different rows then join the column 3 with all rows can be joined with a comma delimiter. Here is the Example:
Input
No
Year
Text
A-1
2012
AB
A-1
2012
CD
A-1
2012
EF
B-2
2011
AB
B-2
2011
CD
B-2
2011
EF
Output should be
No
Year
Text
A-1
2012
AB, CD, EF
B-2
2011
AB, CD, EF
Hi All
There is a simple and easy way to merge celles without losing the entire data
=CONCATENATE(cell1,cell2)
thats it
hope it's helpfull
forget something that you add this formula in the next column
regards,
yasser
I was planning to write an email to following addresses:
sdkjfhds@msn.com
kjdafhk@gmail.com
jfh@gmail.com
jhdfjah@djsldf.com
in excel, then I added A1 & ", " to B1 and then I noticed here. Result is perfect.
I have never noticed that fill button before 🙂
You may have already answered this question, but I am such a newbie to excel I am not sure.
I have two columns, A & B.
Column A is the Family Number. (1k family numbers)
Column B is the Unique Name. (50k unique names)
All the Unique Names in Column B need to be merged into 1 single cell according to the Family Number in Column A. I tried using "Justify" but it wraps the names onto multiple rows because I can't make the column wide enough.
I am using excel 2007.
Thank you!
Whitney
Hi Whitney,
See this: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Thank you Mr. Chandoo, it appears you have the answer I need and it will save me many hours of work. Unfortunately my excel skills are so weak that I don't understand where to begin. I will try to find a class on excel programming basics so that I can learn how to implement your suggestion.
Thank you
Whitney
i keep getting a syntax error. and how do i use this?
is there any other fuction that can be used that has the same effect but there will be spce between them?
eg. cat dog and not catdog
@NaaG
See this: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Nice trick
Hi sir i just want a clarification from you that im preparing a travel schedule that consist of onward details and return details .. i just sorted the list based on onward date and time and now i just wanted to insert in the records in word using directory technique. everythng is ok but what the problem is im getting the list in order one below the other but i need 1 2 3 in this way not as
1
2
3
please help me in this regard
great work.. Thanx.. 🙂
We can use conconate function....to merge any cells,,,with text
This is easily possible if the data is in 2 columns. You can use this tool http://www.anotherwaytodothis.com/excel-merge/combine-join-cells.php to merge cells even with data in them.
Very Easy One Thanks,,,:)
[...] This code has been written by Chandoo, you read more ways to tackle this issue on his blog here. [...]
Is it possible to apply this macro to all rows of a 5 x 50 set of data? Sorry, I'm very inexperienced with macros.
Good.
The best solution for my need.
Rgds,
Sumit
@Sumit
Have a read of the posts here:
http://dailydoseofexcel.com/?s=closed+workbook
They have discussed this issue several times
Hello
In a sheet where each column has different conditional formatting, is it possible, in VBA, to merge cells vertically without loosing the conditionnal formatting?
Thank you
Hi thank you very much for the macro, that is a gem!!!
I wanted to know if instead of using selection but if i would like to add a preset range, how do i rewrite the code for this?
For example?
instead of "For Each cell In Selection"
i would like to merge data in a predetermined cell that will not moved.
A1: Apple
A2: orange
A3: banana
A4: Chocolate
A5: Coffee
A6: Tea
A7: Red
A8: Pink
A9: yellow
to become:
apple orange banana <-- as one cell
chocolate coffee tea <-- as one cell
red pink yellow <-- as one cell
Can you pleaseee help me? thank youuuu
how do i do this?
Works Perfectly for me 🙂
Hello Chandoo, this macro works great for my spreadsheet needs, however, a column with several ranges [+5,000] of rows that are needed to apply this VBA takes a lot of time and effort, so deciding to use a colored and alternated background for each range in order to visualize which range needs to be joined and merged easily, is there a way on the VBA to grab each range with the same background to run the VBA and continue with the next range on the different background color until it runs to the end of the last row/range? Thank you.
I have conditional formatting relying on a date where the cell is merged because I had to add another row in order to have the sub contractors listed separately. For example, I have rows 2 and 3 for a task and columns A, B, C, D, E, F, G, H, I, J, K, L, M, N, O and P are all merged however once you get to column Q, R, S, T, U they are no longer merged to show the distinction between the 2 different sub contractors on the task. Column V is merged again. My problem is Column I which is the expiration date of the task is the condition to turn all the cells to the color requested. Because column I is merged the first subcontractor in Row 2, Column Q, R, S, T, U will turn the color but the second sub contractor in Row 3, Column Q, R, S, T and U will not turn the color requested. It will only happen if I don't merge the Column I which has the expiration date and I put the date in both cells (2 and 3). PLEASE HELP IVE BEEN WORKING ON THIS FOR 3 DAYS!!!!!!
Reply
Dear all
how I repeat the word with simple shortcut how I create a macro in it. Like "Pakistani is great" this line is use many time in my sheet how can I make shortcut for it??
thank you so much , fill and justify function helped me and saved lot of time..
thank you .....??
I get in the above cases, the content is being merged from multiple cells into one cell, but how can you merge the content of multiple cells WITHOUT losing the format of the text in those multiple cells into one cell, especially if the final result will result in having more than 255 characters, thus the TEXT property of the CHARACTERS object on the final cell will NOT be available.