I exported a report i'm working with and i'm trying to analyze the data within it, but i'm having problems because only the first entry for any one week has the date on it. so i'm having to manually add the week number to the data, is there any formula that can fill the blanks.

also could i suggest starting a thread that contains your most useful excel formulas? lots of smart people here.

original chart

transaction week# units shipped
1 week 23 x1
2 x 2
3 x3
4 x400

how i want it

1 week23 x1
2 week23 x2
3 week23 x3
4 week23 x400

 

better visual
ransaction week# units shipped
1.....................week 23......... x1
2 .......................................... x 2
3........................................... x3
4............................................x400
5.....................week24...........y1
6............................................y2
how i want it

1............week23......x1
2............week23......x2
3............week23......x3
4...........week24.........y1
5...........week24.........y2
etc

 

Not trying to be a smartass, but can't you just copy paste all the rows with the proper week?

 

you can do a find and replace maybe. Find "x" replace with "week 23.....x"

 

i know, that's how i'm currently doing it, but the spread sheets have over 1500 rows and cover a whole years so it's kinda tedious to find and replace or paste and copy 52 times.

 

find and replace all?

Single click, doesn't get much easier

 

I don't think i made myself clear, there are about 1500 transactions per week on the spread sheet, and there are 52 weeks in a year, however, the spreadsheet only has one date entry per week, and everything below it is blank until the next week, like this.

week 1
blank
blank
blank
etc
week 2
blank
blank
blank
etc
week 3
blank
blank
week 4
etc

I'm looking for a way to rpl the blanks with the week number corresponding to it. Right now i'm copying the week, selecting the blank space between weeks and copying, however i have to carry out this transaction 52 times per spreadsheet and i'm thinking someone here has got to have a better way of doing it.

 

Here's how I would do it:
1) create a new column
2) in that column, create a formula in each cell referring to the cell above it. For example, if column you created was Column D, and you were were in row 4, put "=D3" in that cell. This will copy the contents of the cell above it. Fill this formula all the way down your sheet for as many rows as you have.
3) right-click on the column header of the column that contains your weeks and copy everything.
4) right-click on the column that you created and select "Paste Special"
5) from the paste special menu, select "All" in the top section and place a checkmark next to "Skip Blanks" at the bottom.

Voila!

 

Good idea!

Also, if you want the contents of the new column to be the text (or number) instead of the formula (ie: the contents in the cell being "Week23" instead of "=D4", you can:

6) right-click on the column header of the new column with the formulas and click "Copy".
7) right-click on the column header again and click "Paste Special"
8) from the paste special menu, select "Values" and click "OK".

Voila!

 

sweet i'll give it a try.

 

Another alternative is to create a macro.
Sub Macro1()End Sub

Just click on the cell where you want to start (probably A1) and then run this macro.