rrtts
09-26-2006, 03:37 PM
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:
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
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
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:
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
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