Re-enable cell merging, Excel table

Question

Hello,

In an Excel spreadsheet, I would like to merge cells in a row; impossible because I see that the merging of all the cells in this table is inactive.

Question, how can I reactivate merging?
Configuration: Windows XP / Firefox 3.6.8

chector10 · Accepted Answer

Thank you for your response. But no, my sheet is not protected.  For me, it involves putting a title (a label) above 7 columns, that is to say merging the 7 cells in the top row.

Vaucluse · Answer

Hello merging cells is never a good idea, but still... isn't your sheet protected? best regards -- Let's ask ourselves if we are not the only ones who understand what we are explaining?

chector10 · Answer

No, it doesn't work. When I write a text in the first cell and then select the others to center the text, when I hit enter, the previous text "column 13," "column 12," etc. always appears.

chector10 · Answer

Yes, it is indeed an Excel table copied from Sheet1 to Sheet2. And normally, in this kind of copy, the cells are mergeable.

Raymond PENTIER · Answer

We'll never get there without seeing your file!  Use https://www.cjoint.com/ or http://cijoint.fr/ and in your next message paste the link you obtained. -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

chector10 · Answer

Here it is, I think I have sent the incriminating file and I hope you will receive it. This email is to inform you that the Excel file "doublehistorique pass2" contains several sheets. The sheets that are problematic are 2010 and at the other end sheet 1 (a copy) May I ask you a second question: How to edit a sort or filter on multiple columns in a single table? It's about sorting birth years in columns J to O: 98 to 07 Thank you for everything.  For the identification, it didn't work, I didn't understand. I had two with you: chector10 and before, bhector8. Currently, I am with Free.

chector10 · Answer

Dear Sir, I notice that there is a problem: If I use the link http://cjoint.com, I follow the instructions, and in the end, when I click on copy the link, the response is impossible.

Vaucluse · Answer

Hello
not normal!
save the file
right click on the link, copy
open a message here
paste the link. It should work
best regards
--
Let’s ask ourselves if we are not the only ones who understand what we are explaining?

chector10 · Answer

Thank you very much,
What you are teaching me is that the table format prevents merging cells.
I had indeed worked around the problem as you indicated; but I wanted to know why it was blocked on the 2010 sheet.

May I ask you a second question?
Is it possible to edit a sort on multiple columns at once or in a single table?
I need to edit the children who are spread across 6 columns, only those who were born in 98, 99, 00, 01, 02, 03, 04, 05, 06, 07.
I managed to do it by sorting each column; but I think we should be able to sort them in a single table. I then display their address and I do a mailing, after hiding all the other columns.

chector10 · Answer

Ok, I think I'll try in the evening?

Raymond PENTIER · Answer

Yes, friend Vaucluse, it is Wisdom that expresses itself through you!  I am sorry to inform chector10 that his file is hardly usable: * On line 1 of the different sheets, explicit headings should be placed, such as No., Name, Children... but never useless headings like Column1, Column2... * We do not have a real Excel file, as there are no formulas anywhere! We would have exactly the same thing with Word tables... * In the "2010" sheet, it is line 289 that should have been in line 1. * How should line 292 be interpreted? There are no children in columns I to O, but there are in columns P to W! * Similarly, in line 301, we see 2 children in columns J and K for 2007, but there is the number 3 in Q301 and R301: what does that mean? -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

Raymond PENTIER · Answer

OK ! You complicate the processing of this file with your columns J to O: they are not identified in row 1 (you merged a unique title across the 6 columns), and these columns do not correspond to a year of birth, but to the successive children of the same family. It would be much easier for you (and for us) if you used 10 columns with the titles 1998, 1999 ... 2007 and in the cells just an X or the number 1. -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

chector10 · Answer

I understand, but there are families taking care of older children, 13, 14 years old and older, which would create many columns. But for next year, I do think I will proceed this way, by putting in a single column those who are no longer children. In any case, I did it manually for the 24 remaining families, marking with an I those that were concerned for their children. But I wanted to know if there was a faster way to sort a table on multiple columns. It's not a big deal, thank you for the work I gave you. Yes, I really like this site "How it works." I would ask you, if possible, to delete my two files which should remain confidential.

Raymond PENTIER · Answer

Chector10, I don't know if I understood correctly what you mean by "sorting on multiple columns at once".  If I consider columns Q to V, you do * Data/Sort/My data has headers/ * Sort by/2006/ * Add a level/Then by/2007/... and so on. -- It's nice, retirement! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

Raymond PENTIER · Answer

I would like to point out that while testing my proposal, there was a blockage, with the reason "contains data in text format". Upon checking all your data, I find that there are indeed anomalies that prevent sorting: * the numbers in Q3 and R3 are in text format * the letter "s" in S89 * the letter "s" in T277 * "#REF!" in V354  Moreover, in a database, you should NEVER give the same title to 2 columns: 2007 for columns D and R, 2008 for columns C and S, 2009 for columns B and T, 2010 for columns A and U. -- Retirement is great! Especially in the West Indies ... :-) &#9787; Raymond &#9794;

chector10 · Answer

Thank you for your advice on creating Excel spreadsheets.
You know I'm 83 years old and I operate by feel.
I used to do it differently.
I tried following your procedure. Indeed, I can add multiple levels. The sorting then takes place across multiple columns (which is the goal), but I lose the extension to the cells on the right and left. The arrangement of rows cannot be the same from one column to another after sorting. That's why I sort by column, meaning at a single level.
But I will take your comments into account.

Raymond PENTIER · Answer

No, no, no!  "But I lose the extension on the cells to the right and left. The arrangement of the rows cannot be the same from one column to another after sorting. That's why I sort by column, meaning on only one level."  Before starting your sort, you need to place yourself in a non-empty cell. That's all! You definitely should not select some columns, otherwise they'll be the only ones sorted, obviously...  Best regards, retired colleague! -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

chector10 · Answer

Dear retired colleague,Having my morning free, I tried to improve my skills in creating Excel spreadsheets using the filter and sort commands.I completely agree with your advice.I know that the table I sent you was filled with anomalies because I am not the one who maintains it primarily, and the mixing of text and numbers is common. On the other hand, the column headers 2010, 2009, 2008, 2007 do not duplicate the latest ones with the same name; but you are right, they need to be titled differently.These are statistics requested by the department federation, and other columns created and used by our committee, particularly for organizing a Christmas party; this is the case with the children’s columns.Next year, I will delete unnecessary columns.I finally managed to sort all 10 children’s columns into a single table using the "add a level" command. What a job!!I will continue to proceed step by step; it might be safer.

Raymond PENTIER · Answer

Hello, chector10.  "I finally managed to sort all 10 columns of children into a single table using the "add a level" command. What a task!"  Great! I see that you have created a column for each birth year, which makes your work easier, don’t you think? When you say "what a task", are you referring to the shift to 10 columns? Because cascading sort with "add a level" is really not tiring...  Can I see your final result? Send it to me by Private Message. -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

chector10 · Answer

Actually, I’m keeping what I started to do manually, that is to say after sorting the last column, to keep only the families who have come more than once to the SPF for help. In this case, the number of rows decreases and it becomes possible to sort the columns manually; which I did and printed. But there are still 5 weeks left, maybe before editing the addresses for the invitations, I’ll proceed differently.
Because I know it’s possible to sort all 6 columns together with the “add a level” command. I did a test with 20 columns and it worked. I didn’t keep it.
“What a job!” I mean by that, if I ultimately use this system, I’ll have to correct a lot of cells, replace the 00 01 02 ... that are text with numbers; and the collective conversion didn’t work; the 00 01 02 remained. And also other anomalies. So I don’t know yet. I’ll see.
So I can’t send you anything.

Raymond PENTIER · Answer

"replace the 00 01 02 ... which are text with numbers; and the collective conversion didn't work"

So try this to see: you enter the number 1 in an empty cell, you do Copy, you select the cells containing numbers formatted as text and you do Special Paste/Multiply.
--
Retirement is nice! Especially in the West Indies ... :-)
☻ Raymond ♂

Raymond PENTIER · Answer

and also other anomalies

Your remark prompts me to remind you that for a database to be usable, it must absolutely be reliable, meaning verified, with no empty rows or columns, with a header for each column, without duplicates in these headers or in the reference column (the first one, usually), without formatting errors, and without multiple spellings for the same data (Saint-Maur, Saint Maur, and St-Maur).
If several people are working on the file, strict rules must be enforced and systematic checks must be carried out.

We must be aware that anomalies in the data will inevitably lead to false results!

Best regards.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂

chector10 · Answer

http://www.cijoint.fr/cjlink.php?file=cj201010/cijr5ELmHi.xlsx
I don't know if this will work for sending my response and the "historical pass" file
Attached is this file that takes a long time to save and open, on which I've worked: columns J to O. Conversion of text numbers into numeric values. Sorting on these same columns to select children aged 3 to 12 years using the "add a level" command is quick but not perfect, and finally sorting on column U (2010) to select the number of visits by families to us. What do you think?

Apparently it doesn't work, probably the file is too large.

chector10 · Answer

I'm trying to respond to you, but the mail keeps coming back. Moreover, I sent you the file I worked on, but since it's 20 MB, it comes back to me, failure.
I managed to convert text numbers to numeric values. Ok, successfully without any issues.
I sorted the children’s columns J to O, using the "add a level" command. I succeeded halfway, there are mistakes. Finally, I sorted the last column U, number of family visits, to select numbers from 1 to 8.

I notice that the file takes a long time to open and therefore to send. Why?
Do I need to delete some columns and rows, perhaps?

Raymond PENTIER · Answer

It's not possible: It was 80 KB last time; it's unimaginable that it has gone up to 20 MB!
You have a virus or something similar...
Select all the filled cells and copy/paste them into a blank file; save it; let us know what size it is.
--
Retirement is good! Especially in the Caribbean... :-)
☻ Raymond ♂

chector10 · Answer

Dear retired colleague,
After losing connection with you due to an accidental strike, I'm trying this evening to get back in touch to let you know that I managed to create my table with sorting on multiple levels.
I'm sending this file, hoping it will work.

http://www.cijoint.fr/cjlink.php?file=cj201011/cij9CkOtdj.xlsx

On sheet 1, you will find the complete table
On the copy of the sheet, you will find the final table: the selection of children invited to the Christmas party.
Procedure: copy of sheet 1 to do the work
Step 1: filter on columns F / 2 3 4 5 6 7
Then hide row 1
Step 2: sort on columns I to N. Custom sort; 98 99 0 1 2 3 4 5 6 7.
These numbers are text, but Excel treats them as numbers.
You will be able to see the custom sort.

It worked but with some errors because there were a few years not declared in the criteria in the 6th column. But I believe I understood why.

Raymond PENTIER · Answer

Well! I trust you: you seem satisfied with the result obtained, so that's great.
As for me, I didn't understand the meaning of column G (Sheet1) nor what the inserted column in F in the "copy" sheet is for.
And I still don't see the point of doing this custom sort on several columns. But oh well...

Best regards.
--
Retirement is nice! Especially in the Caribbean... :-)
☻ Raymond ♂

chector10 · Answer

A simple word of explanation.
I am in charge of a committee of the SPF (Popular Relief). We receive struggling families every week. And during this time, we are preparing a small Christmas party for the children.
To limit the number of people due to the space and our resources, we need to determine the selection criteria. Hence the column G or F: number of visits from the family, from 1 to 8 or more. I have excluded families that have only come once, the most numerous. The board will decide between two options: families with at least 2 visits or 3 visits.
The benefit of personalized sorting on several columns? Again, to limit the number of children, I have to take into account the age criteria, up to 12.
The problem is to sort this in Excel or manually, given that the ages are arranged in the columns in no particular order. Well, Excel can do it.
Thank you for teaching me a number of things.

Re-enable cell merging, Excel table

28 réponses

Windows spotlight is not working.

Masked destination layer

Samsung j5 unable to answer

Scam of the suvedene highway

File name too long

No sound with ps3 and hdmi

Wake up from sleep mode in windows 10.

Internet connection problem with my ipad

Free box: remote control broken

Suspicious number text message