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

[PP3321]

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.

 

[PP3321]

VBA logic is like this:

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

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

 

[shrivallabha]

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?

 

[PP3321]

@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.

The length will depend on the number of rows in that category.

 

[NARAYANK991]

Hi ,

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

Narayan

 

[PP3321]

@NARAYANK991
No. I was looking for ways other than VBA.
WOW!!!!!It looks amazing!!!!!!!
Let me study this...Thank you so much!!!

 

[bosco_yip]

See as per attached file.

Different approach, in un-sorting of Category column.

Regards
Bosco

 

[PP3321]

@bosco_yip
Thank you!!!!!I will have a look later...

 

[PP3321]

Hi ,

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

Narayan

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]

See as per attached file.

Different approach, in un-sorting of Category column.

Regards
Bosco

@bosco_yip
Thank you. I now understand your approach of identifying the first row using countingifs.

 

[NARAYANK991]

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...

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

 

[PP3321]

OK!
Thank you! I really appreciate your support!!!!