In this article, we will learn how to draw lines between sorted groups in Microsoft Excel 2010.
To draw lines between sorted groups of data, follow the below mentioned steps:
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Is it possible to get Excel to view ""2.10"" as ""section 2 part 10""? I'm trying to produce a log, and would like Excel to be able to sort 2.10 after 2.9...
Can this be extended so that a list could sort itself:
1
1.1
1.2
1.2.1
....1.2.9
1.2.10
....1.9
1.10
etc"
"Just FYI - I came up with additional steps that will get me 100% of my desired solution. In other words, if I have varying numbers of lines between the data, I can now add just one row between each record. Here's how: Assume the data is in column A. In column B I write a simple IF/THEN statement that if the cell to the immediately left is NOT blank, then display column A. If the cell to the immediate lefts is blank and the cell above it is NOT blank, then print an identifying code like ""xx"". I haven't tested it, but should look like assuming the cell pointer is in Cell B2:
=IF(A2"""",A2,IF(AND(A2="""",A1""""),""xx"",""""))
I would copy this formula all the way down to the end of the data. I would then copy and paste the results of Column B in Column A using Paste Values and delete Column B.
There should now be a ""xx"" on what you use to be the first blank row after each record and nothing added to the additional blank rows between records.
Using you AutoFilter suggestion, I could filter for ""xx"" rather than filter for blank lines and then go through the rest of your steps.
At the end of the process, I would also have to highlight Column A and replace all ""xx"" with """".
Thanks again,
David"
"That's is a 100% improvement over how I am doing it now.
THANKS!!!"
"It is hard to give a definite answer as I am provided data by my employer and sometimes it is a fixed number or rows and sometimes not. Sometimes it is orderly so that row5 is always the ""city"" for example and sometime the ""city"" is in row 4 and sometimes it is in another row all within the same column. I've gotten pretty creative in addressing these other issues. I always have at least one blank row between the records and there are never blank rows within the record. In my previous example I posted to you, there were 8 fields per record. My goal is to add a single row each time a blank row is reached. It would be an even better goal if it only inserted a blank row the first time a blank row appears below a non-blank row. In other words, if there were two blank rows between each record, I really only to add one more additional row and not two.
Thanks again."
"Hi David,
I think I am beginning to understand now.
So, is there a standard number of total fields that you want to have 'space' for?
From your above example, it seems that the maximum total fields is 9, and some records have less than that, and for those records, you want to insert additional rows so that you can, later, add the missing fields up to 9.
Is that correct?
If that is the case, then it is a bit more complext thanI originally indicated, since we cannot just search or a single blank row, and insert a new blank row above or below that, else the fully populated records will have two blank rows after them, and those with, say, 2 missing fields will still not work.
Thanks,
Alan."
"Your first guess what correct. I do not want to remove rows. My data looks like:
field data1
field data2
field data3
field data4
field data5
field data6
field data7
field data8
{blank}
field data1
field data2
field data3
field data4
field data5
field data6
field data7
field data8
DESIRED RESULT AFTER MACRO RUN:
field data1
field data2
field data3
field data4
field data5
field data6
field data7
field data8
{blank}
{blank}
field data1
field data2
field data3
field data4
field data5
field data6
field data7
field data8
Thanks,
David
"
"Alan,
Thanks, but I bearly know what a macro is much less program one. I've got about 6,000 lines of data so I won't fill up the sheet if I only knew how. Do you have instructions on how to write this particular macro. Most of my data is separated by one blank row though there may be a few exceptions where there are 2 or 3 blank lines separating the data. I guess once the macro runs into say 4 or 5 blank rows in a row then it would stop the process otherwise insert a blank row if the current row is blank AND the row above it is not blank. Is that how it would work? It would be a big help if you could provide the steps in the writing this particular macro as I will have use for it many times in the future for the same purpose on large data sets.
Regards,
David"
"Hi David,
I think you just need to write a simple macro to do that. Essentially, just create a LOOP or a WITH statement to roll through the clumn of data, check the cell to see if it is blank, and if so, insert two rows (above or below as you require). Just be carful not to add so many rows that you end up hitting the bottom of the sheet (row 65536), but you'd need a fair sized data set for that to happen!
Alan."
"Anand did you ever find an answer outside of this posting? Please share if you did.
I too was hoping to use a blank row as a separator as my data is in a single column. Each record is separated by a single row.
I was hoping there was a symbol for inserting a row so I simply highlight the column and do a find/replace: find blank cell and replace with 2 insert row symbols."
"Hi Anand,
You could apply conditional formatting to change the background colour (for example) when a certain field switches from one value to another.
Would that meet your requirements?
If so, then the *easiest* way to do that might be to add a column away on the right of the data (say), and use a formula to simply check whether the row you are working on is the same as the one above (for the given field). If there has been a change, then change from, say, 0 to 1 as the result, or back from 1 to 0 at the next change. Then use conditional formatting to change the background colour based on that flag.
Hope that helps!
Alan"
"Hi Alan ,
Thanks for reply,
I needed to insert a blank row on same lines of inserting a coloured line using a formula & conditional formating.This happens automatically and if we have too many groups (for eg. I have about 1000 groups and will be changing very often), inserting blank lines manually will be a tedious work. SO using conditional formating or some other technic, Can we insert a balnk row which can act as seperator ?
Please help ASAP
Thanks"
"Hi Anand,
I may be missing something here, but you can certainly sort data and then insert a new row in the middle.
For example, if you have a list of names:
Bob
AnandCharlie
You could first sort that data alphabetically, and then insert a blank row after Anand:
Anand
{Blank}
Bob
Charlie
If the new name entered was, say, 'Zac', then you might need to re-sort again.
Hope that helps,
Alan."
Can we insert a blank row in between data after Sorting in Microsoft Excel. Please reply ASAP. Thanks in aniticipation
Great tip! I had been looking for some time to to this - works perfectly. MS should include all these tips in their online help...