Solved: Inserting a Carriage Return / Line Feed

[rrtts]

Despite using Excel (Excel 2003 / VB 6.3 I think) at my jobsite for a number of different projects, I still consider myself a novice Excel user, so please go easy on me. Also, I did search the forums in an attempt to locate someone with a similar question, but turned up no useful results.

Anyways...here is my problem that I would be so very thankful if anyone can help me with. I will do my best to explain it the best I can.

I have two columns of text - like so. (A5:B9 for example)

RHHIabc - Ship Name 1
RHHIdef - Ship Name 2
RHHIghi - Ship Name 3
RHHIabc - Ship Name 4
RHHIghi - Ship Name 5
RHHIabc - Ship Name 6
etc

What I'm trying to do is sort the ships by the first column (routing indicator) but display it by ship name. I have a formula that I found on Google, but I am unable to contact the person who posted it and ask for help because the forum is no longer in use.

The formula is:

[VBA]
Public Function FindSeries(TRange As Range, MatchWith As String)

For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", "
End If

Next cell

FindSeries = Left(x, (Len(x) - 2))

End Function
[/VBA]

Which when I code it into my worksheet using the below formula will return the indicated results:

Formula: =findseries(A5:B9,"RHHIabc")
Results: Ship Name 1, Ship Name 4, Ship Name 6, etc.

I really need the information to be in a column, not a row. I thought it would be as simple as changing the ", " to a & vbCrLF or perhaps & Chr(13) & Chr(10) or even a & _ but none will turn it into a column. I thought maybe I could use vertical alignment, but can't get that to work either.

Any help would be greatly appreciated. Thanks a bunch.

Edited 27-Sep-06 by geekgirlau. Reason: insert vba tags

[mdmackillop]

Welcome vto VBAX
I'm not clear how you want results displayed. Can you put your data and desired result on a spreadsheet and post it. Use Manage Attachments in the Go Advanced section.
Regards
MD

[Bob Phillips]

No need for code.

Select C1:C9.
In the formula bar add
=IF(ISERROR(SMALL(IF($A$1:$A$9=A1,ROW($A1:$A9),""),ROW($A1:$A9))),"",
INDEX($B$1:$B$9,SMALL(IF($A$1:$A$9=A1,ROW($A1:$A9),""),ROW($A1:$A9))))

and enter as an array formula, with Ctrl-Shift-Enter, not just Enter.

[rrtts]

Wow...thanks for the quick replies...I have attached a test file of what I'm working with...the data in rows 17, 20 and 23 I would like to appear in columns vice rows.

@xld - I tried your suggestion but I only get one return (I'm assuming the smallest value or the first one in the list?) - Perhaps I did something wrong?

Thanks for the help!

[geekgirlau]

Welcome to the Board!

Just a quick note - I've taken the liberty of editing your post to include VBA tags. When you're posting code, select the code text and click on the "VBA" button to insert the tags - the end result is much easier to read.

[Bob Phillips]

I have attached an example in the spreadsheet.

What I did was put the values in C2, D2, E2, and added the formulae below.

[rrtts]

You are a lifesaver! That is exactly what I needed...THANKS A BUNCH!

[rrtts]

@xld - I'm still tweaking the worksheet you created for me...and it is going well...I do appreciate the help...so perhaps you can assist a bit more, if it's no trouble...

[vba]=IF(ISERROR(SMALL(IF($A$1:$A$13=D$2,ROW($A1:$A13),""),ROW($A1:$A13))),"",
INDEX($B$1:$B$13,SMALL(IF($A$1:$A$13=D$2,ROW($A1:$A13),""),ROW($A1:$A13))))[/vba]

Referring back to the worksheet you posted for me...instead of using $A$1:$A$13=D$2, I'd like to make the D$2 search for RHHI and a wild card...

I can get just the RHHI to work...but can't get it to work with a wild card...I tried the following using * ? ~* and ~?.

[vba]=IF(ISERROR(SMALL(IF($A$1:$A$13="RHHI" & "*",ROW($A1:$A13),""),ROW($A1:$A13))),"",
INDEX($B$1:$B$13,SMALL(IF($A$1:$A$13="RHHI" & "*",ROW($A1:$A13),""),ROW($A1:$A13))))[/vba]

Any ideas...thanks again for all your help.

[Bob Phillips]

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND(C$2,$A$1:$A$13)),ROW($A1:$A13),""),ROW($A1:$A13))),"",
INDEX($B$1:$B$13,SMALL(IF(ISNUMBER(FIND(C$2,$A$1:$A$13)),ROW($A1:$A13),""),ROW($A1:$A13))))

[rrtts]

BRILLIANT!

I'm adding you to my Christmas card list.

[rrtts]

@xld or anyone else who can help,

I've tweaked the code that was provided in this thread to:

[vba]
=IF(ISERROR(SMALL(IF(ISNUMBER(FIND("RHVS",'DB Input'!
$A$1:$A$104)),ROW('DB Input'!$A1:$A104),""),ROW('DB Input'!
$A1:$A104))),"", INDEX('DB Input'!$B$1:$B$104,SMALL(IF(ISNUMBER(FIND
("RHVS",'DB Input'!$A$1:$A$104)),ROW('DB Input'!$A1:$A104),""),ROW('DB
Input'!$A1:$A104))))
[/vba]

This code works perfect...but can I have it search for multiple values (no more than four) instead of just the "RHVS". I've tried editing it multiple ways and I have got it where I don't get any errors, but I also don't get any results...so obviously I am doing something wrong.

Any help would be greatly appreciated! (I put some carriage returns in the code because it was stretching the post out)

[Bob Phillips]

[vba]

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND({"RHHY","RHHX"}, 'DB Input'!$A$1:$A$104)),ROW( 'DB Input'!$A1:$A104),""),ROW('DB Input'!$A1:$A104))),"",
INDEX( 'DB Input'!$B$1:$B$104,SMALL(IF(ISNUMBER(FIND({"RHHY","RHHX"}, 'DB Input'!$A$1:$A$104)),ROW('DB Input'!$A1:$A104),""),ROW('DB Input'!$A1:$A104))))
[/vba]

[rrtts]

Again I say, brilliant!

Perfect!