Excel 2007 B2 - Selecting every cell that starts with a certain word

J

joshcampfield

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
 
N

Nick Hodge

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
[email protected]
 
D

Dave Peterson

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
 
N

Nick Hodge

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
[email protected]
 
N

Nick Hodge

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
[email protected]


Nick Hodge said:
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
[email protected]
Click to expand...
 
D

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 said:
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
[email protected]
Click to expand...
 
P

paul

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.
 
N

Nick Hodge

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
[email protected]
 
D

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 said:
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
[email protected]
Click to expand...
<<snipped>>
 
D

Dave Peterson

(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!
 
Top