• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter Table by Length of Text...is it possible?

PP3321

PP3321

Active Member
I need to automatically show rows,
which contain the longest length of text in each group.

<my VBA solution>
Longest string always comes on top row.
So I can do VBA to extract that.

<request for suggestion>
Can you suggest simpler ways to achieve this?

*I can not manually delete because the data contains thousands of rows.

screenshot.png
 
VBA logic is like this:

If next cell is different from previous, ( meaning the 1st row)
then copy the text...

Code: 
Option Explicit

Sub GetLongestLength()

Dim i As Integer
Dim n As Integer
n = 2

For i = 2 To 100
If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
Cells(i, 2).Copy Cells(n, 3)
n = n + 1
End If

Next i

End Sub
 
How do you identify Start and End of Group? e.g. It will precede by a blank row like row 14 in the screen shot. Will the length always be constant for topmost row in each group?
 
@shrivallabha

Sorry I can not upload file. Please bear with me.

1. Group is determined by Column A (Category Column)
It will go from 1 to 12.

2. There is no empty row between Categories.

3. I am just concatenating rows.
The length will depend on the number of rows in that category.

screenshot.png The length will depend on the number of rows in that category.
 
See as per attached file.

Different approach, in un-sorting of Category column.

Regards
Bosco
 

Attachments

  • longest length of text .xlsx
    18.9 KB · Views: 11
NARAYANK991 said:
Hi ,

Do you need VBA ? See the attached file for a formula solution.

Narayan
Click to expand...

Hi @NARAYANK991

I looked at your file.
If you do not mind, can you help me with 2 questions when you have time?

1. If I do not use MAX() function, I get FALSE.
But it does the job.
Is it OK without MAX function.

2. This worked without Array Formula(CTL+Shi+Enter)...
Do I have to do Array Formula?

Thank you...
 
PP3321 said:
Hi @NARAYANK991

I looked at your file.
If you do not mind, can you help me with 2 questions when you have time?

1. If I do not use MAX() function, I get FALSE.
But it does the job.
Is it OK without MAX function.

2. This worked without Array Formula(CTL+Shi+Enter)...
Do I have to do Array Formula?

Thank you...
Click to expand...
Hi ,

In this case it may not matter , but as such , the formula is an array formula , and therefore it is best if it is entered using CTRL SHIFT ENTER.

If it is entered without the MAX function , then it should not be entered as an array formula.

If it is entered as an array formula , using CTRL SHIFT ENTER , then the MAX function also has to be included.

Narayan
 
You must log in or register to reply here.
Back
Top