If someone can help you will save my hand from carpal tunnel. I
imported some data from a warehouse program and it gives me item # and
all transactions associated.

blank row
Item # Invoice Date Remaing Items
BKD569 7685 5/5/98 500
6575 5/6/98 453
6756 5/7/98 412
5675 5/8/98 410
Blank row

I would like to be able to fill the item # in-front of all invoices
that correspond there is a blank line at the begining and end of the
invoices relating to a particular item.

If that can't be done, is there a way to move the item # to the last
invoice for the item and delete everything else above, for that item?
Thank you so much for any help!!!!!

Do the following

- Select the Item # column from the first row with data
to the end of your data.
- Press F5
- Press the "Special" button
- Select "Blanks"
- Press the "Ok" button
- Type and equals sign then press the up arrow key
- Press Control+Enter
- Select the Item # column of your data.
- Press Control+C
- Using menus, select Edit -- Past Special
- Select "Values"
- Press the "Ok" button
- Using menus, select Insert -- Columns

Now enter this into the new column, assuming your original
Item # column is now col B

=IF(C:C= ""," ",B:B)

Copy it down to the length of your data and then select that
column.

- Using menus, select Edit -- Past Special
- Select "Values"
- Press the "Ok" button
- Press Control+H
- Enter a space (use the space bar) into the "Find What' box
- Enter nothing (or delete what is there) into the "Replace With" box
Both boxes will look like they are empty.
- Press the "Replace All" button

After the replacement is done on the selected area cancel the Replace dialog.

Delete your original Item # column.

When you get to do this a few times it will take all of about one minute.

Chrissy.



"Daniel" wrote in message om...
If someone can help you will save my hand from carpal tunnel. I
imported some data from a warehouse program and it gives me item # and
all transactions associated.

blank row
Item # Invoice Date Remaing Items
BKD569 7685 5/5/98 500
6575 5/6/98 453
6756 5/7/98 412
5675 5/8/98 410
Blank row

I would like to be able to fill the item # in-front of all invoices
that correspond there is a blank line at the begining and end of the
invoices relating to a particular item.

If that can't be done, is there a way to move the item # to the last
invoice for the item and delete everything else above, for that item?
Thank you so much for any help!!!!!

Hi Daniel,

- select the cells under your first item number (where you
want to copy it).
- run this macro:
Sub fill_empty_cells()
Selection.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
Selection.Copy
Selection.PasteSpecial xlPasteValues
End Sub

Best regards

Wolf


-----Original Message-----
If someone can help you will save my hand from carpal
tunnel. I
imported some data from a warehouse program and it gives
me item # and
all transactions associated.

blank row
Item # Invoice Date Remaing Items
BKD569 7685 5/5/98 500
6575 5/6/98 453
6756 5/7/98 412
5675 5/8/98 410
Blank row

I would like to be able to fill the item # in-front of
all invoices
that correspond there is a blank line at the begining and
end of the
invoices relating to a particular item.

If that can't be done, is there a way to move the item #
to the last
invoice for the item and delete everything else above,
for that item?
Thank you so much for any help!!!!!
.

The other way round getting rid of those item numbers on the blank rows would be to insert a new
column at the beginning of the data, which would become Col A. Now copy Col C to Col A, do Edit /
Go To / Special / Blanks, and then Edit / Delete / Shift cells Left. Then just delete Col A
again.

Depending on his ultimate need though, it may be better to actually delete all the blank rows
anyway, as the OP could then use either the Data / Subtotals option to get a better view of his
data, or indeed a Pivot table. If this was the route to go, then with the data as is, he could
simply select Col B, do Edit / Go To / Special / Blanks, and then do Edit / Delete / Entire Row.

Some nice Pivot table intros':-

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Chrissy" wrote in message ...
Do the following

- Select the Item # column from the first row with data
to the end of your data.
- Press F5
- Press the "Special" button
- Select "Blanks"
- Press the "Ok" button
- Type and equals sign then press the up arrow key
- Press Control+Enter
- Select the Item # column of your data.
- Press Control+C
- Using menus, select Edit -- Past Special
- Select "Values"
- Press the "Ok" button
- Using menus, select Insert -- Columns

Now enter this into the new column, assuming your original
Item # column is now col B

=IF(C:C= ""," ",B:B)

Copy it down to the length of your data and then select that
column.

- Using menus, select Edit -- Past Special
- Select "Values"
- Press the "Ok" button
- Press Control+H
- Enter a space (use the space bar) into the "Find What' box
- Enter nothing (or delete what is there) into the "Replace With" box
Both boxes will look like they are empty.
- Press the "Replace All" button

After the replacement is done on the selected area cancel the Replace dialog.

Delete your original Item # column.

When you get to do this a few times it will take all of about one minute.

Chrissy.



"Daniel" wrote in message
om...
If someone can help you will save my hand from carpal tunnel. I
imported some data from a warehouse program and it gives me item # and
all transactions associated.

blank row
Item # Invoice Date Remaing Items
BKD569 7685 5/5/98 500
6575 5/6/98 453
6756 5/7/98 412
5675 5/8/98 410
Blank row

I would like to be able to fill the item # in-front of all invoices
that correspond there is a blank line at the begining and end of the
invoices relating to a particular item.

If that can't be done, is there a way to move the item # to the last
invoice for the item and delete everything else above, for that item?
Thank you so much for any help!!!!!





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.525 / Virus Database: 322 - Release Date: 09/10/2003

"Ken Wright" wrote in message ...
The other way round getting rid of those item numbers on the blank rows would be to insert a new
column at the beginning of the data, which would become Col A. Now copy Col C to Col A, do Edit /
Go To / Special / Blanks, and then Edit / Delete / Shift cells Left. Then just delete Col A
again.

Depending on his ultimate need though, it may be better to actually delete all the blank rows
anyway, as the OP could then use either the Data / Subtotals option to get a better view of his
data, or indeed a Pivot table. If this was the route to go, then with the data as is, he could
simply select Col B, do Edit / Go To / Special / Blanks, and then do Edit / Delete / Entire Row.

Some nice Pivot table intros':-

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Chrissy" wrote in message ...
Do the following

- Select the Item # column from the first row with data
to the end of your data.
- Press F5
- Press the "Special" button
- Select "Blanks"
- Press the "Ok" button
- Type and equals sign then press the up arrow key
- Press Control+Enter
- Select the Item # column of your data.
- Press Control+C
- Using menus, select Edit -- Past Special
- Select "Values"
- Press the "Ok" button
- Using menus, select Insert -- Columns

Now enter this into the new column, assuming your original
Item # column is now col B

=IF(C:C= ""," ",B:B)

Copy it down to the length of your data and then select that
column.

- Using menus, select Edit -- Past Special
- Select "Values"
- Press the "Ok" button
- Press Control+H
- Enter a space (use the space bar) into the "Find What' box
- Enter nothing (or delete what is there) into the "Replace With" box
Both boxes will look like they are empty.
- Press the "Replace All" button

After the replacement is done on the selected area cancel the Replace dialog.

Delete your original Item # column.

When you get to do this a few times it will take all of about one minute.

Chrissy.



"Daniel" wrote in message
om...
If someone can help you will save my hand from carpal tunnel. I
imported some data from a warehouse program and it gives me item # and
all transactions associated.

blank row
Item # Invoice Date Remaing Items
BKD569 7685 5/5/98 500
6575 5/6/98 453
6756 5/7/98 412
5675 5/8/98 410
Blank row

I would like to be able to fill the item # in-front of all invoices
that correspond there is a blank line at the begining and end of the
invoices relating to a particular item.

If that can't be done, is there a way to move the item # to the last
invoice for the item and delete everything else above, for that item?
Thank you so much for any help!!!!!



THANK YOU SO MUCH FOR THE HELP BOTH WAYS WORKED PERFECT!!!!!!!!!!!!!

My pleasure - Glad it helped, and appreciate the feedback.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------


<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.525 / Virus Database: 322 - Release Date: 09/10/2003