Group rows with same content together.

[henk400]

I have a very large excel sheet and need to group rows with the same content in a specific column.

Is it possible to create vba code which allows me to group these rows ?

[NeedForExcel]

Have you tried the sub totals feature under the Data tab?

It will help you group similar data from 1 column.

If you need to match multiple columns, you can create a helper column, concatenate the data from different columns and then use Sub Totals..

Give it a try!

[henk400]

this option is not active (grayed out) in the columns where I want to use it.

The content of the column where I want to look for duplicates to combine is based on a formula and does not contain numbers.
It tells me you need headers, but I don't use headers.

Is there any other way to do this ?

[NeedForExcel]

Can you not use temporary headers and then get rid of them once you are done?

[henk400]

I have a header already, but the Subtotal feature is grayed out, except when I select a cell in an unsed column.

The final sheet I have has almost 1 million rows, so I would like to automate as much as possible. are there other ways ?

[NeedForExcel]

Can you please attach your sample sheet?

[henk400]

Here it is.

if you look on the sheet 'copy' you fine the header 'Source dir' in column O. all duplicate lines should be removed, or better group them together.
It is used for copying data, so you can imagine that it is useless if there are 2 or more equal lines.

http://www.filedropper.com/example_1

[NeedForExcel]

Hi, the Subtotal feature is greyed out because your data set is formatted as Table.

Either you do not format your table as a Table and use Sub Totals or we will have to look for an alternate way..

See the file attached to see how it will look.. Also use the 1,2,3 button at the left top corner to check out the functionality..

[henk400]

great,

This look likes how I need it.

Although to make it perfect, I need another sub total for the column 'G' which is named as third section.
Is it possible to have 2 sub totals for different columns in one sheet ?

by the way, I would like to unformat my original document to enable the subtotal function, how did you do that ?

[NeedForExcel]

To un format, use short keys Alt > B > V > Enter..

You cannot have 2 Sub-Totals I am afraid..

[henk400]

This combination doesn't work on my Excel 2013 version.

|s there a menu option I can go to, for disabling it ?


If I cannot use 2 Subtotal options there is something else I would like to do, but it might be a little harder:

all cells with similar content in a specific column should each be moved to a separate worksheet.
So I get as many worksheets as there are different values in a specific column.

Is there a way to achieve this ?

[NeedForExcel]

This combination doesn't work on my Excel 2013 version.

|s there a menu option I can go to, for disabling it ?

I am sorry, its actually the Right Click Button On the Keyboard and not the Alt key.. You can also right click on the table > table > convert to range

If I cannot use 2 Subtotal options there is something else I would like to do, but it might be a little harder:

all cells with similar content in a specific column should each be moved to a separate worksheet.
So I get as many worksheets as there are different values in a specific column.

Is there a way to achieve this ?

That can be done using Pivot Table too... Is that fine with you?

[henk400]

Of course, I am very happy if that does the job, but how do I get it done ?

[NeedForExcel]

Of course, I am very happy if that does the job, but how do I get it done ?

Hi,

Please see the file attached.

Click on the Pivot Table > Click On Analyze Tab > On the Left, click on the little arrow next to the "Options" button > Select Show Report Filter For Pages > Click OK.

Sheets will be created for each Source_dir and would normally be renamed. However, the Text Strings in the Source_dir field cannot be valid sheet names, hence the sheets wont be named!

Try it out..

If it does not help you, we will try using VBA

[LokeshKumar]

Why do not you Concatenate and sort....to group similar rows together.....

[henk400]

It look likes something I need, but it is not easy for me to check because of the dummy content which doesn't make it very clear.
I have uploaded a similar file where the content looks more realistic, so I can check if it is correct.

In this file I have moved the columns source and destination dir to column A and B, these are the most important columns for me.

In column I the project name is listed, the pivot table should use that column to look at and sort by project name.

I have added a pivot table to an additional sheet, this looks quite nice, I am using the project name (column Third Section).
If I collapse the project name I get the source_dir underneath it. I would like to have the destination dir's in the next column (B)

If I select the field destination dir it will be visible in column A.
file: example4.2


example4.2.xlsx

[henk400]

I have these rows already sorted, but the issue is that I want to have only one line of the group with similar values. All others should be made hidden or removed.

I am using this sheet to copy data and it is quite useless to copy twice (or even more times) the same content.

For several folders I have a couple of hundred lines with the same source folder. this should become just one line.

Ideal for me would be a combination of the project name and the source_dir.

In the file 'example4.2.xlsx' the first column has the Source_dir and the column I which says 'Third Section' has the project name.

If the combination of the values in both columns are the same I only need to have one line. all other lines with the same values should be for example hidden or marked with an x in a new column so I can filter them out.