Copy and fill automatically

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

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.
 
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
 
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/Excel/Pivots/pivottables.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/assistance/2002/articles/xlconPT101.aspx
 
Ken Wright said:
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/Excel/Pivots/pivottables.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/assistance/2002/articles/xlconPT101.aspx
Click to expand...
THANK YOU SO MUCH FOR THE HELP BOTH WAYS WORKED PERFECT!!!!!!!!!!!!!
 
Back
Top