I have 2 columns of about 100 records.

First record is a vendor name, second record is either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns. One
for "I" one for "II" and the last for "III". Of course
under these header would be the vendors names.

I was able to do it but I have three columns with blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance

Gilbert,
If you want to do this with formulae:

Assuming:
Column A contains the vendor name starting at row 2, B contains ""I, II, or
III" starting at row 2

Create:
Column C with header "I" in row 1
Column D with header "II" in row 1
Column E with header "III" in row 1

In cell C2 type the formula:
=IF($B2=C$1,$A2,"")

and copy the formula to all cells in columns C to E which have row data.

That will, I think, give you a tabular layout.
AlexJ

"Gilbert" wrote in message
...
I have 2 columns of about 100 records.

First record is a vendor name, second record is either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns. One
for "I" one for "II" and the last for "III". Of course
under these header would be the vendors names.

I was able to do it but I have three columns with blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance

I tried that as I pointed out in my post, but I get blank
rows in each column.
I 'd like to have three consistent columns with no blanks
between the rows...

Thanks anyway.

-----Original Message-----
Gilbert,
If you want to do this with formulae:

Assuming:
Column A contains the vendor name starting at row 2, B
contains ""I, II, or
III" starting at row 2

Create:
Column C with header "I" in row 1
Column D with header "II" in row 1
Column E with header "III" in row 1

In cell C2 type the formula:
=IF($B2=C$1,$A2,"")

and copy the formula to all cells in columns C to E which
have row data.

That will, I think, give you a tabular layout.
AlexJ

"Gilbert" wrote in
message
...
I have 2 columns of about 100 records.

First record is a vendor name, second record is either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns.
One
for "I" one for "II" and the last for "III". Of course
under these header would be the vendors names.

I was able to do it but I have three columns with blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance


.

Sorry if I tried to tell you something you know already.
I don't understand what you mean when you say "no blanks between the rows"
Are there gaps in the records, or do you wish to have the output sorted?

AlexJ

"Gilbert" wrote in message
...
I tried that as I pointed out in my post, but I get blank
rows in each column.
I 'd like to have three consistent columns with no blanks
between the rows...

Thanks anyway.

-----Original Message-----
Gilbert,
If you want to do this with formulae:

Assuming:
Column A contains the vendor name starting at row 2, B
contains ""I, II, or
III" starting at row 2

Create:
Column C with header "I" in row 1
Column D with header "II" in row 1
Column E with header "III" in row 1

In cell C2 type the formula:
=IF($B2=C$1,$A2,"")

and copy the formula to all cells in columns C to E which
have row data.

That will, I think, give you a tabular layout.
AlexJ

"Gilbert" wrote in
message
...
I have 2 columns of about 100 records.

First record is a vendor name, second record is either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns.
One
for "I" one for "II" and the last for "III". Of course
under these header would be the vendors names.

I was able to do it but I have three columns with blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance


.

Alex,
No, the source column has no gaps in it but since the I's
II's and III's are allocated to a vendor in a non-
predictable way, when I try to scan all the column for
instances of I's when the formula finds one, it placesit
in the same relative row in the target column (under the I
header) same for II and III therefore I get an accurate
result but not usable for presentation.

If I sort my source column (I.II,III), before running the
formula I get a better result for I only get one block of
blank rows in each target column

I guess I just need to sort each target column nder I,
II,III.

I didn't mean to offend you or anyone by saying that I had
already tried that solution.

Thanks anyway for any past and future help.

G




-----Original Message-----
Sorry if I tried to tell you something you know already.
I don't understand what you mean when you say "no blanks
between the rows"
Are there gaps in the records, or do you wish to have the
output sorted?

AlexJ

"Gilbert" wrote in
message
...
I tried that as I pointed out in my post, but I get
blank
rows in each column.
I 'd like to have three consistent columns with no
blanks
between the rows...

Thanks anyway.

-----Original Message-----
Gilbert,
If you want to do this with formulae:

Assuming:
Column A contains the vendor name starting at row 2, B
contains ""I, II, or
III" starting at row 2

Create:
Column C with header "I" in row 1
Column D with header "II" in row 1
Column E with header "III" in row 1

In cell C2 type the formula:
=IF($B2=C$1,$A2,"")

and copy the formula to all cells in columns C to E
which
have row data.

That will, I think, give you a tabular layout.
AlexJ

"Gilbert" wrote
in
message
...
I have 2 columns of about 100 records.

First record is a vendor name, second record is
either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns.
One
for "I" one for "II" and the last for "III". Of
course
under these header would be the vendors names.

I was able to do it but I have three columns with
blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance


.



.

No offense taken. Sorry I coldn't help
Alex
"Gilbert" wrote in message
...
Alex,
No, the source column has no gaps in it but since the I's
II's and III's are allocated to a vendor in a non-
predictable way, when I try to scan all the column for
instances of I's when the formula finds one, it placesit
in the same relative row in the target column (under the I
header) same for II and III therefore I get an accurate
result but not usable for presentation.

If I sort my source column (I.II,III), before running the
formula I get a better result for I only get one block of
blank rows in each target column

I guess I just need to sort each target column nder I,
II,III.

I didn't mean to offend you or anyone by saying that I had
already tried that solution.

Thanks anyway for any past and future help.

G




-----Original Message-----
Sorry if I tried to tell you something you know already.
I don't understand what you mean when you say "no blanks
between the rows"
Are there gaps in the records, or do you wish to have the
output sorted?

AlexJ

"Gilbert" wrote in
message
...
I tried that as I pointed out in my post, but I get
blank
rows in each column.
I 'd like to have three consistent columns with no
blanks
between the rows...

Thanks anyway.

-----Original Message-----
Gilbert,
If you want to do this with formulae:

Assuming:
Column A contains the vendor name starting at row 2, B
contains ""I, II, or
III" starting at row 2

Create:
Column C with header "I" in row 1
Column D with header "II" in row 1
Column E with header "III" in row 1

In cell C2 type the formula:
=IF($B2=C$1,$A2,"")

and copy the formula to all cells in columns C to E
which
have row data.

That will, I think, give you a tabular layout.
AlexJ

"Gilbert" wrote
in
message
...
I have 2 columns of about 100 records.

First record is a vendor name, second record is
either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns.
One
for "I" one for "II" and the last for "III". Of
course
under these header would be the vendors names.

I was able to do it but I have three columns with
blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance


.



.

Sub ChangeData()

'Assumptions
'Column headings in row 1
'Data starts in row 2
'VendorName is in column A
'Type (I,II,III) is in column B

Dim ws As Worksheet, wsNew As Worksheet
Dim c As Range
Dim lngVendorI As Long
Dim lngVendorII As Long
Dim lngVendorIII As Long

Set ws = ActiveSheet
ActiveWorkbook.Worksheets.Add
Set wsNew = ActiveSheet

With wsNew
.Range("A1").Value = "I"
.Range("B1").Value = "II"
.Range("C1").Value = "III"
End With

lngVendorI = 2
lngVendorII = 2
lngVendorIII = 2

For Each c In ws.Range("B2:B" & ws.Range("B65536").End(xlUp).Row)
Select Case c.Value
Case "I"
wsNew.Cells(lngVendorI, 1).Value = c.Offset(0, -1).Value
lngVendorI = lngVendorI + 1
Case "II"
wsNew.Cells(lngVendorII, 2).Value =
c.Offset(0, -1).Value
lngVendorII = lngVendorII + 1
Case "III"
wsNew.Cells(lngVendorIII, 3).Value =
c.Offset(0, -1).Value
lngVendorIII = lngVendorIII + 1
End Select
Next c

Set c = Nothing
Set ws = Nothing
Set wsNew = Nothing

End Sub

--
Dianne

In ,
Gilbert typed:
I have 2 columns of about 100 records.

First record is a vendor name, second record is either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns. One
for "I" one for "II" and the last for "III". Of course
under these header would be the vendors names.

I was able to do it but I have three columns with blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance

This looks great!!

I'm going to try it right away

I'll keep you posted..


Thanks a million....

G
-----Original Message-----
Sub ChangeData()

'Assumptions
'Column headings in row 1
'Data starts in row 2
'VendorName is in column A
'Type (I,II,III) is in column B

Dim ws As Worksheet, wsNew As Worksheet
Dim c As Range
Dim lngVendorI As Long
Dim lngVendorII As Long
Dim lngVendorIII As Long

Set ws = ActiveSheet
ActiveWorkbook.Worksheets.Add
Set wsNew = ActiveSheet

With wsNew
.Range("A1").Value = "I"
.Range("B1").Value = "II"
.Range("C1").Value = "III"
End With

lngVendorI = 2
lngVendorII = 2
lngVendorIII = 2

For Each c In ws.Range("B2:B" & ws.Range("B65536").End
(xlUp).Row)
Select Case c.Value
Case "I"
wsNew.Cells(lngVendorI, 1).Value =
c.Offset(0, -1).Value
lngVendorI = lngVendorI + 1
Case "II"
wsNew.Cells(lngVendorII, 2).Value =
c.Offset(0, -1).Value
lngVendorII = lngVendorII + 1
Case "III"
wsNew.Cells(lngVendorIII, 3).Value =
c.Offset(0, -1).Value
lngVendorIII = lngVendorIII + 1
End Select
Next c

Set c = Nothing
Set ws = Nothing
Set wsNew = Nothing

End Sub

--
Dianne

In ,
Gilbert typed:
I have 2 columns of about 100 records.

First record is a vendor name, second record is either
I,II, or III. All vendor names are unique.

Out of this data I would like to build three columns.
One
for "I" one for "II" and the last for "III". Of course
under these header would be the vendors names.

I was able to do it but I have three columns with blank
rows scattered all over.

I would appreciate any help or hint.

Thanks in advance


.