This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

Josh

That doesn't matter what version you have, non-uniform data is always
difficult to handle. Certainly it will need code, but the code is made more
difficult as there is nothing much to 'key' on, being that there is three or
possibly two rows.

It's certainly not impossible, but complicated, so I would bite the bullet
and start manually

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


wrote in message
oups.com...
This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

This worked for me in xl2003:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson

Dave

For some reason this doesn't work for me. I tried debugging and the code
that picks up the $* doesn't seem to pick it up, even though you are using
the text property which in the immediate window clearly shows the leading $.

Any thoughts?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
This worked for me in xl2003:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson

Dave

Update...

It appears the accounting format I use has some padding around the $. I'll
take another look.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Nick Hodge" wrote in message
...
Dave

For some reason this doesn't work for me. I tried debugging and the code
that picks up the $* doesn't seem to pick it up, even though you are using
the text property which in the immediate window clearly shows the leading
$.

Any thoughts?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
This worked for me in xl2003:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson

Maybe better:

ElseIf LCase(trim(.Cells(iRow, "A").Text)) Like "$*" Then

to eliminate that extra space.

===
I'm betting that that currency/accounting style does that to align stuff in the
cell.

(I don't have any money, so it's not something I usually deal with <vbg.)

Nick Hodge wrote:

Dave

Update...

It appears the accounting format I use has some padding around the $. I'll
take another look.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

"Nick Hodge" wrote in message
...
Dave

For some reason this doesn't work for me. I tried debugging and the code
that picks up the $* doesn't seem to pick it up, even though you are using
the text property which in the immediate window clearly shows the leading
$.

Any thoughts?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
This worked for me in xl2003:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson



--

Dave Peterson

if for some reason you dont want to do code try this
in a column next to your data copy this formula next to your first dollar cell
=IF(NOT(ISNUMBER(A3)),"",IF(LEFT(OFFSET(A3,-2,0),4)="item",CONCATENATE(OFFSET(A3,-2,0)," ",OFFSET(A3,-1,0)," ",A3),CONCATENATE(,OFFSET(A3,-1,0)," ",A3)))
adjust the reference A3 to whatever your first dollar number(reference)
is(and i sure hope the dollar figure IS a number not text)
copy down
copy the whole column and paste back onto itself as a paste special paste
values
use the data/text to columns to put into your columns.Instead of the " " in
the concatenate formulas you could use"," or ";" as a delimiter.
--
paul

remove nospam for email addy!



" wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

Dave

I tried that and it's fine down to the missing Item# and then goes haywire
again. (Put's the $ in position 2).bizarre...

Sure it works for most though as OP hasn't returned with an issue

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
Maybe better:

ElseIf LCase(trim(.Cells(iRow, "A").Text)) Like "$*" Then

to eliminate that extra space.

===
I'm betting that that currency/accounting style does that to align stuff
in the
cell.

(I don't have any money, so it's not something I usually deal with <vbg.)

Nick Hodge wrote:

Dave

Update...

It appears the accounting format I use has some padding around the $.
I'll
take another look.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

"Nick Hodge" wrote in message
...
Dave

For some reason this doesn't work for me. I tried debugging and the
code
that picks up the $* doesn't seem to pick it up, even though you are
using
the text property which in the immediate window clearly shows the
leading
$.

Any thoughts?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
This worked for me in xl2003:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin
with
the word "Item" in column A, the item names in column B, and the
prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson



--

Dave Peterson

You're right.

I misread the original post. I thought that the description was the the thing
that could move.

Maybe something like:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long
Dim FoundPrice As Boolean

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
FoundPrice = False
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oCol = 1
ElseIf LCase(Trim(.Cells(iRow, "A").Text)) Like "$*" Then
oCol = 3
FoundPrice = True
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
If FoundPrice = True Then
oRow = oRow + 1
FoundPrice = False
End If
Next iRow
End With
End Sub


Nick Hodge wrote:

Dave

I tried that and it's fine down to the missing Item# and then goes haywire
again. (Put's the $ in position 2).bizarre...

Sure it works for most though as OP hasn't returned with an issue

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

<<snipped

Dave

That fixes it...
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
You're right.

I misread the original post. I thought that the description was the the
thing
that could move.

Maybe something like:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim oCol As Long
Dim oRow As Long
Dim FoundPrice As Boolean

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
FoundPrice = False
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oCol = 1
ElseIf LCase(Trim(.Cells(iRow, "A").Text)) Like "$*" Then
oCol = 3
FoundPrice = True
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
If FoundPrice = True Then
oRow = oRow + 1
FoundPrice = False
End If
Next iRow
End With
End Sub


Nick Hodge wrote:

Dave

I tried that and it's fine down to the missing Item# and then goes
haywire
again. (Put's the $ in position 2).bizarre...

Sure it works for most though as OP hasn't returned with an issue

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

<<snipped

Someday I'll learn to read.

(or maybe not)

Nick Hodge wrote:

Dave

That fixes it...
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

<<snipped

If you don't want to mess around with VBA, try this.

In the first column, use

=IF(ISERROR(FIND("Item",A1)),"",A1)


In the second column, use

=IF(LEFT(A1,4)="Item",OFFSET(A1,1,0),IF(LEFT(TEXT( A1,"$##.##"),1)="$","",IF(LEFT(OFFSET(A1,-1,0),4)="Item","",A1)))


In the third column, use

=IF(ISERROR(FIND("$",TEXT(A1,"$##.##"))),IF(ISERRO R(FIND("Item",A1)),IF(ISERROR(FIND("$",TEXT(OFFSET (A1,-1,0),"$##.##"))),"",OFFSET(A1,1,0)),OFFSET(A1,2,0) ),"")


This should give you what you want, but you will have some blank rows.
The following Help topic from office.microsoft.com will help you to
delete the blank rows.

Delete duplicate rows from a list in Excel Help

A duplicate row (also called a record) in a list is one where all
values in the row are an exact match of all the values in another row.
To delete duplicate rows, you filter a list for unique rows, delete the
original list, and then replace it with the filtered list. The original
list must have column headers.

Caution Because you are permanently deleting data, it's a good idea
to copy the original list to another worksheet or workbook before using
the following procedure.

Select all the rows, including the column headers, in the list you want
to filter.
Tip

Click the top left cell of the range, and then drag to the bottom right
cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the
selection appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.



Is this what you're looking for?

-Ryan




wrote:
This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

Someday I'll take the time you did over the reply ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
Someday I'll learn to read.

(or maybe not)

Nick Hodge wrote:

Dave

That fixes it...
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

<<snipped

(or over several (wrong headed) replies...)

But between the two of us, we solved the problem--well, until the OP comes back
and says that it still doesn't work!

Nick Hodge wrote:

Someday I'll take the time you did over the reply ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

"Dave Peterson" wrote in message
...
Someday I'll learn to read.

(or maybe not)

Nick Hodge wrote:

Dave

That fixes it...
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

<<snipped

--

Dave Peterson