I have a street database that contains 9 columns. Not all of them will be
used on each record. Here's my problem. I have a column named ST_NAME and a
column named OID that have all my information in them. It's set up like this:
ST_NAME OID STREET_2 STREET_3 STREET_4
Leigh cv 22
Mays ln 22
Stoneridge 22
Russell 54
Makala 54

Where the OID are all the same, each of those streets need to be placed in a
seperate street column, instead of all being under ST_NAME. Leigh cv, Mays
Ln, and Stoneridge should occupy ST_NAME, STREET_2, and STREET_3 all in the
same row. I'm not sure how to set up functions for this.

On Oct 16, 7:16 am, Jennifer Andrews <Jennifer
wrote:
Where the OID are all the same, each of those streets need to
be placed in a seperate street column, instead of all being under
ST_NAME. Leigh cv, Mays Ln, and Stoneridge should occupy
ST_NAME, STREET_2, and STREET_3 all in the same row.
I'm not sure how to set up functions for this.

I am not sure I understand the assumptions you can make and the result
that you want, but I wonder if the following helps.

Assume ST_NAME, OID, STREET_2, STREET_3 and STREET_4 are columns A, B,
C, D and E, and assume "Leigh cv" is in row 2. Insert the following
formulas and copy them down their columns:

C2: if(and(B1<B2,B2=B3), A3, "")
D2: if(and(B1<B2,B2=B4), A4, "")
E2: if(and(B1<B2,B2=B5), A5, "")

Thus, in the first row with a new OID, STREET_2, STREET_3 and STREET_4
will contain up to the next 3 adjacent street names with the same
OID. Those columns will remain blank in the non-first rows with the
same OID.


----- complete original posting -----

On Oct 16, 7:16 am, Jennifer Andrews <Jennifer
wrote:
I have a street database that contains 9 columns. Not all of them will be
used on each record. Here's my problem. I have a column named ST_NAME and a
column named OID that have all my information in them. It's set up like this:
ST_NAME OID STREET_2 STREET_3 STREET_4
Leigh cv 22
Mays ln 22
Stoneridge 22
Russell 54
Makala 54

Where the OID are all the same, each of those streets need to be placed in a
seperate street column, instead of all being under ST_NAME. Leigh cv, Mays
Ln, and Stoneridge should occupy ST_NAME, STREET_2, and STREET_3 all in the
same row. I'm not sure how to set up functions for this.

Don't know about functions, but it is simple with a macro

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If .Cells(i, "B").Value = .Cells(i - 1, "B").Value Then
.Cells(i, "C").Resize(, 2).Copy .Cells(i, "D")
.Cells(i, "A").Copy .Cells(i, "C")
.Cells(i, "C").Resize(, 3).Copy .Cells(i - 1, "C")
.Rows(i).Delete
End If
Next i

End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jennifer Andrews" <Jennifer wrote in
message ...
I have a street database that contains 9 columns. Not all of them will be
used on each record. Here's my problem. I have a column named ST_NAME
and a
column named OID that have all my information in them. It's set up like
this:
ST_NAME OID STREET_2 STREET_3 STREET_4
Leigh cv 22
Mays ln 22
Stoneridge 22
Russell 54
Makala 54

Where the OID are all the same, each of those streets need to be placed in
a
seperate street column, instead of all being under ST_NAME. Leigh cv,
Mays
Ln, and Stoneridge should occupy ST_NAME, STREET_2, and STREET_3 all in
the
same row. I'm not sure how to set up functions for this.

Vacation?<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
Don't know about functions, but it is simple with a macro

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If .Cells(i, "B").Value = .Cells(i - 1, "B").Value Then
.Cells(i, "C").Resize(, 2).Copy .Cells(i, "D")
.Cells(i, "A").Copy .Cells(i, "C")
.Cells(i, "C").Resize(, 3).Copy .Cells(i - 1, "C")
.Rows(i).Delete
End If
Next i

End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jennifer Andrews" <Jennifer wrote in
message ...
I have a street database that contains 9 columns. Not all of them will be
used on each record. Here's my problem. I have a column named ST_NAME
and a
column named OID that have all my information in them. It's set up like
this:
ST_NAME OID STREET_2 STREET_3 STREET_4
Leigh cv 22
Mays ln 22
Stoneridge 22
Russell 54
Makala 54

Where the OID are all the same, each of those streets need to be placed in
a
seperate street column, instead of all being under ST_NAME. Leigh cv,
Mays
Ln, and Stoneridge should occupy ST_NAME, STREET_2, and STREET_3 all in
the
same row. I'm not sure how to set up functions for this.

And one solution with formulas, for the fun...
Assume ST_NAME, OID in row 1, A1:B1, streets start on A2, OID's on B2.

Place all the OID's successively. E.g. 22 in F2, 54 in F3. Start in G2
with this *array* formula:

=IF(COUNTIF($B$2:$B$6,$F2)=COLUMNS($G$1:G1),INDEX ($A$2:$A
$6,SMALL(IF($B$2:$B$6=$F2,ROW($A$2:$A$6)-ROW($A$2)+1),COLUMNS($G
$1:G1))),"")

As an array formula it must be committed with Shift+Ctrl+Enter. Now,
copy down and across to suit.

HTH
Kostis Vezerides

On Oct 16, 5:16 pm, Jennifer Andrews <Jennifer
wrote:
I have a street database that contains 9 columns. Not all of them will be
used on each record. Here's my problem. I have a column named ST_NAME and a
column named OID that have all my information in them. It's set up like this:
ST_NAME OID STREET_2 STREET_3 STREET_4
Leigh cv 22
Mays ln 22
Stoneridge 22
Russell 54
Makala 54

Where the OID are all the same, each of those streets need to be placed in a
seperate street column, instead of all being under ST_NAME. Leigh cv, Mays
Ln, and Stoneridge should occupy ST_NAME, STREET_2, and STREET_3 all in the
same row. I'm not sure how to set up functions for this.