PDA

View Full Version : Solved: If/Then that only grabs first 5 text entries



Kindly_Kaela
03-03-2008, 09:08 AM
Hello Everyone!

I have a very large database that I am pulling data from and then inputting it into Excel. One of the columns has multiple data entries with the following...

ABC - NY
ABC - Chicago
ABC - Dallas
ABC - LA
etc... etc...

There's also several other possibilities that do not start with "ABC - "

With that said, I would like to find all cells that start with "ABC - " regardless of what city follows.

Please advise on what code would only read the first 5 text entries and ignore the rest. End result would be grouping all data cells that start with the "SAME" first 5 text entries.

Thank you!!

:cloud9:

Bob Phillips
03-03-2008, 09:52 AM
Put this array formula ain the first of the target cells and copy down the 5 cells

=IF(ISERROR(SMALL(IF($A$1:$A$20="ABC",ROW($A$1:$A$20),""),ROW($A1))),"",
INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20="ABC",ROW($A$1:$A$20),""),ROW($A1))))