Problem that is driving me crazy!

[Wizariwas]

The Problem is the way the spread sheet was designed see sample (A):
VENDOR------DESCRIPTION [/B]
------------------*GATE*
VENDOR 1-------32' GATE
VENDOR 2-------SAFETY GATE
VENDOR 3-------6 X 3 BLACK
------------------*GEA
vendor a-------Gear xxx
vendor b-------Gear xxx


MY IDEA SO I CAN IMPORT IT INTO ACCESS


Type-----Vendor-----Description*

*GATE*-----Vendor 1-----32' gate
*GATE*-----Vendor 2-----Safety gate
*GATE*-----Vendor 3-----6 x 3 Black
*GEAR*-----vendor a-----Gear xxx
*GEAR*-----vendor b-----Gear xxx

They also created a full range of worksheets using the alphabet, so yo
could look up description alphabetically just by clicking on th
worksheet. So if you were looking for a Gate you would click on the
worksheet, then scrolling down the list, under description you woul
find Gate or Gear etc. with nothing else on the row as you see in th
sample above. Now I’m not sure how this came about but the perso
using it seems to have no problem with it. I on the other hand hav
had a lot of problems with it. Some of the work sheets are between 100
to 3500 rows deep. I want to put this in an access data base to make i
more manageable. The thing that I have been trying to solve is a way t
copy the description “GATE” from the row to a new column Type one ro
down and then continue copying the description until the nex
Description. If anyone has any ideas as how to fix this it would b
greatly appreciated. Note that the person uses the global find to ge
her answers

 

[Debra Dalgleish]

You can try the following on a copy of your data. Assuming vendor names
are in column A, and descriptions are in column B:

Insert a new column to the left of the vendor column
Add the heading, Type
In cell A2, enter: =IF(B2="",C2,A1)
Copy the formula down to the last row of data
Select column A, and copy it
Choose Edit>Paste Special
Select Value, click OK
Choose Edit>Replace
For 'Find What', enter: ~*
Leave 'Replace with' blank
Click Replace All
Click Close

Select column B
Choose Edit>Go To, click Special
Select Blanks, click OK
Choose Edit>Delete
Select 'Entire Row'
Click OK

The Problem is the way the spread sheet was designed see sample (A):
VENDOR------DESCRIPTION [/B]
------------------*GATE*
VENDOR 1-------32' GATE
VENDOR 2-------SAFETY GATE
VENDOR 3-------6 X 3 BLACK
------------------*GEAR
vendor a-------Gear xxx
vendor b-------Gear xxx


MY IDEA SO I CAN IMPORT IT INTO ACCESS


Type-----Vendor-----Description*

*GATE*-----Vendor 1-----32' gate
*GATE*-----Vendor 2-----Safety gate
*GATE*-----Vendor 3-----6 x 3 Black
*GEAR*-----vendor a-----Gear xxx
*GEAR*-----vendor b-----Gear xxx

They also created a full range of worksheets using the alphabet, so you
could look up description alphabetically just by clicking on the
worksheet. So if you were looking for a Gate you would click on the G
worksheet, then scrolling down the list, under description you would
find Gate or Gear etc. with nothing else on the row as you see in the
sample above. Now I’m not sure how this came about but the person
using it seems to have no problem with it. I on the other hand have
had a lot of problems with it. Some of the work sheets are between 1000
to 3500 rows deep. I want to put this in an access data base to make it
more manageable. The thing that I have been trying to solve is a way to
copy the description “GATE” from the row to a new column Type one row
down and then continue copying the description until the next
Description. If anyone has any ideas as how to fix this it would be
greatly appreciated. Note that the person uses the global find to get
her answers.