Number list based on a condition

[Enigmafish14]

Hello,

I have a database limit for part descriptions and so I have to trim anything over 100 characters. I format all parts (for other limitations also) in excel before importing to the database. I apply a LEN formula to all the descriptions to let me know if they exceed the limit.

What I can't figure out is how can I create a cell that lists for me all of the cells that are over 100 characters. I'm unable to just sort the list because of other formulas and I am usually working with 1000's of part numbers, so it is hard to spot all of them easily.

I tried using something with MAX, INDEX, MATCH and LARGE but I also had to include IF's and so I was only able to find the top 7 (because of nesting limits) and I couldn't get it to sort based on top to bottom cells.

Id like to have cell B1 list all the cells that go over 100 characters as they appear from top to bottom.

Here's my example.

A B
1 99 A2, A5, A6, A8...
2 101
3 98
4 97
5 111
6 107
7 86
8 106

[Richard Buttrey]

Hi,

Assuming the lengths are in column A, one way would be to enter =IF(A1>100,"A"&ROW(),"ZZZ") in B1 and copy down.

Now copy column B and paste special values to column C. Now Sort column C ascending and delete all the ZZZs at the bottom

Regards

[Enigmafish14]

Hi Richard,

I didn't realize that my pseudo table looked that way. Sorry if its confusing.

Attached is what I use everyday. Its an Excel file that does a bunch of different formatting functions and so I am unable to to sort because it will disrupt some of the functions and macros. In column H, I have the description length for each part. What I would like is for Cell H3 to tell me all of the items that are over 100 starting with the top and working its way down. So that way as I am scrolling, I can stop on each description and modify it. So Cell H3 might say something like H7, H23, H56, H67.... and as I fix each cell it will tell me the remaining cells. Once I fix cell H7, H3 will now say H23, H56, H67...

untitled.jpg

[Richard Buttrey]

Please upload a workbook. Pictures are not much help. (See Forum rules para 13)

Regards